asked on

Linq - Join in memory query with fresh query

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

naspinski

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?

suroma

ASKER

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?

ASKER CERTIFIED SOLUTION

naspinski

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

suroma

ASKER

Thanks for your help and pointing me in the right direction. Problem now solved.