Linq multiple where queries

Question

I have an issue building a fairly hefty linq query. Basically I have a situation whereby I need to execute a subquery in a loop to filter down the number of matches that are returned from the database. Example code is in this loop below:

        foreach (Guid parent in parentAttributes)
        {
            var subQuery = from sc in db.tSearchIndexes
                           join a in db.tAttributes on sc.AttributeGUID equals a.GUID
                           join pc in db.tPeopleIndexes on a.GUID equals pc.AttributeGUID
                           where a.RelatedGUID == parent && userId == pc.CPSGUID                             
                           select sc.CPSGUID;

            query = query.Where(x => subQuery.Contains(x.Id));
         }

When I subsequently call the ToList() on the query variable it appears that only a single one of the subqueries has been performed and I'm left with a bucketful of data I don't require. However this approach works:

       IList<Guid> temp = query.Select(x => x.Id).ToList();

        foreach (Guid parent in parentAttributes)
        {
            var subQuery = from sc in db.tSearchIndexes
                           join a in db.tAttributes on sc.AttributeGUID equals a.GUID
                           join pc in db.tPeopleIndexes on a.GUID equals pc.AttributeGUID
                           where a.RelatedGUID == parent && userId == pc.CPSGUID                             
                           select sc.CPSGUID;

            temp = temp.Intersect(subQuery).ToList();
        }

        query = query.Where(x => temp.Contains(x.Id));

Unfortunately this approach is nasty as it results in multiple queries to the remote database whereby the initial approach if I could get it working would only result in a single hit. Any ideas?

driis · Accepted Answer · 2012-03-12T21:25:31.987

I think you are hitting a special case of capturing the loop variable in the lambda expression used to filter. Also known as an access to modified closure error.

Try this:

   foreach (Guid parentLoop in parentAttributes)
    {
        var parent = parentLoop;
        var subQuery = from sc in db.tSearchIndexes
                       join a in db.tAttributes on sc.AttributeGUID equals a.GUID
                       join pc in db.tPeopleIndexes on a.GUID equals pc.AttributeGUID
                       where a.RelatedGUID == parent && userId == pc.CPSGUID                             
                       select sc.CPSGUID;

        query = query.Where(x => subQuery.Contains(x.Id));
     }

The problem is capturing the parent variable in the closure (that the LINQ syntax is converted to), which causes all the subQueryes to be run with the same parent id.

What happens is the compiler generating a class to hold the delegate and the local variables the delegate accesses. The compiler re-uses the same instance of that class for each loop; and therefore, once the query executes, all of the Wheres executes with the same parent Guid, namely the last to execute.

Declaring the parent inside the loop scope causes the compiler to essentially make a copy of the variable, with the correct value, to be captured.

This can be a bit hard to grasp at first, so if this is the first time it has hit you; I'd recommend these two articles for background and a thorough explanation:

Eric Lippert: Closing over the loop variable considered harmful and part two.
Jon Skeet: Closures

+1 If you want to understand this concept more, look at Skeet's answer here and his referenced article. http://stackoverflow.com/questions/271440/c-sharp-captured-variable-in-loop — Daniel Moses, Mar 12 '12 at 20:00
@DMoses thanks, I added some links, fully acknowledging that I am not able to explain this as elegant and precise as the gentlemen Lippert and Skeet :-) — driis, Mar 12 '12 at 21:29
Thank you driis. Worked like a charm. You're a saint and a scholar sir. — kh25, Mar 13 '12 at 09:48
@DMoses I'm curious to see how many times subQuery gets materialized I think every time contains is called it rematerializes the list I'd be much happier to see subquery as a List and not and IEnumerable — dbarnes, Nov 21 '13 at 19:07

score 0 · Answer 2 · answered Mar 12 '12 at 19:53

0

Maybe this way?

var subQuery = from sc in db.tSearchIndexes
               join a in db.tAttributes on sc.AttributeGUID equals a.GUID
               join pc in db.tPeopleIndexes on a.GUID equals pc.AttributeGUID
               where parentAttributes.Contains(a.RelatedGUID) && userId == pc.CPSGUID                             
               select sc.CPSGUID;

answered Mar 12 '12 at 19:53

k06a

17,755
10
70
110

Thanks for the response k06a but the query above is trying (but not succeeding) in doing something slightly different than the query you presented. Essentially I'm filtering by all of the parentAttributes whilst you are filtering by any so this will return a larger set of results. – kh25 Mar 13 '12 at 00:24

Linq multiple where queries

2 Answers2