Solved

Linq - Join in memory query with fresh query

Posted on 2008-06-16
4
379 Views
Last Modified: 2013-11-26
I would like to join and query an in memory query and a fresh linq query. I will try and explain.

I have a query in memory (CurrentItem.ItemClauses). I need to then join this to a fresh data base query (dbClause.LinksSpecificationClauses) so i can do a contains statement (on clauseid).  Here is my code at the moment but it is very slow.

Thanks


For Each TempClause In CurrentItem.ItemClauses
            Dim SpecID As Int32 = TempClause.SpecificationClauseID
            Dim lsc = (From s In dbClause.LinksSpecificationClauses _
                       Where s.SpecificationClauseID = SpecID).SingleOrDefault
            If lsc.ClauseID = ClauseID Then
                Return True
            End If
        Next

Open in new window

0
Comment
Question by:suroma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 21

Expert Comment

by:naspinski
ID: 21799284
are you asking how to make it faster?

Would you be able to make this in to one query?  Or do you have to have it split?  Because right now you have your initials query, which finds x objects, and then you are running x queries.

If you have to keep them split, you have to think of any ways you can programmatically eliminate options that are in your CurrentItem.ItemClauses query before you call the query inside foreach.  Every item you can eliminate before you make the DB call will speed up your program.  

Another option, since it looks like you are running through it for matches is to simply use a .First() to return (if you only need one to return?  it's hard to tell)  That way, you can simply use a query to return the first result that satisfies, eliminating all remaining checks?  Does that make sense?

Can you possibly explain a little better?  Not sure if I am hitting this or not?

0
 

Author Comment

by:suroma
ID: 21799421
I would like to make it into one query but i don't know how to. This is how I would do it if i was just calling it from the database.

Dim result = from ic in dbclause.itemclauses join _
lsc In dbClause.LinksSpecificationClauses on lsc.SpecificationClauseID = ic.SpecificationClauseID where lsc.clauseid = clauseid

However the itemclauses is in memory (CurrentItem.ItemClauses) while i build up the clauses needed before submitting it to the database. I just wondered if there was a way of linking in memory object with the database object?
0
 
LVL 21

Accepted Solution

by:
naspinski earned 500 total points
ID: 21812565
You can't JOIN them or anything since one is SQL and one isn't, but you can manipulate other objects in the query.

Instead of running through each one with it's own query,  you could get a collection with a single query.  If I am reading this right, you want to know which CurrentItem.ItemClauses (assuming this is an int) matches a SpecificationID (int as well)

The following will return a collection of all the SpecificationIDs that match every TempClause in CurrentItem.ItemClauses

Then you can take that list and do what you want with it.  Much more efficient as it is just one trip:
Dim TrueCases = From s In dbClause.LinkSpecificationClauses Where CurrentItem.ItemClauses.Contains(s.SpecificationClauseID) Select s.SpecificationClauseID
 
'hope this is what you are looking for'
'this will spit them out so you can see if it worked'
Response.Write("<h3>True Cases</h2>")
For Each _case In TrueCases
   Response.Write(_case.ToString() + "<br />")
Next

Open in new window

0
 

Author Closing Comment

by:suroma
ID: 31467681
Thanks for your help and pointing me in the right direction. Problem now solved.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question