How can I dynamically combine a set of LINQ queries at runtime?

Question

EDIT: Trying to make my problem a bit more clear.

I'm trying to build a LINQ expression that is the intersection of several LINQ statements. Right now, manually I can intersect two statements together and I'm getting the desired result.

        var results = 
                context.UserBooleanAttributes.Where(x => x.UserAttributeID == 1 && x.Value).Select(a => a.User)
            .Intersect(
                context.UserBooleanAttributes.Where(y => y.UserAttributeID == 2 && y.Value).Select(b => b.User)
            );

         Assert.Equal(100,results.Count());

My goal is to build this dynamically, for example using a for-loop. Since I am using LINQ, I want to defer execution until I actually call something results that forces execution.

Grant Winney · Answer 1 · 2015-07-22T02:59:29.207

1

Looks like you just want a list of User:

var list = new List<User>();

And then you can add each set of results, as you get them:

list.AddRange(someUsers);

If User is a class that you can do an Intersect on, and you only want distinct values (which seems to be the case in your code), consider doing it one go, and calling Distinct() at the end:

var attrIDs = Enumerable.Range(1, xTimes);

list.AddRange(context.UserBooleanAttributes
                     .Where(x => attrIDs.Contains(x.UserAttributeID) && x.Value)
                     .Select(a => a.User)
                     .Distinct());

edited Jul 22 '15 at 02:59

answered Jul 22 '15 at 02:46

Grant Winney

61,140
9
100
152

The problem with AddRange is that it appears to cause the expression to be evaluated. What I didn't mention in my OP is that I'm using this with EntityFramework, and that after I build my list using Intersect, I want to be able to examine list.ToString() to see the underlying SQL code. – WhiskerBiscuit Jul 22 '15 at 03:35
Updated my question a bit. The problem with the AddRange is that also is forcing execution of the statement immediately. – WhiskerBiscuit Jul 22 '15 at 04:06
The loop is eliminated in the new query, so the `AddRange()` is redundant. You can just `var list = context.User......Select(a => a.User)` – Ken Hung Jul 22 '15 at 05:20
Ken, I think I solved it, but would love to see an example – WhiskerBiscuit Jul 22 '15 at 05:22

score 1 · Accepted Answer · edited May 23 '17 at 11:43

Your code can be refactored, to get the multiple lists of users:

IEnumerable<IEnumerable<User>> GetListOfListsOfUsers(int xTimes)
{
    for (int i = 1; i <= xTimes; i++)
    {
        var someUsers = context.UserBooleanAttributes
                               .Where(x => x.UserAttributeID == i && x.Value == true)
                               .Select(a => a.User);
        yield return someUsers;
    }
}

var listOfListsOfUsers = GetListOfListsOfUsers(xTimes);

The problem now becomes intersection of multiple lists:

var intersection = listOfListsOfUsers.Aggregate((previousList, nextList) => previousList.Intersect(nextList));

score 0 · Answer 3 · edited May 23 '17 at 11:51

A couple of problems going on here. The first was I needed a way to declare my anonymous variable for use outside of the loop. I did this by using IQueryable allBooleansTrue. I assigned it a non null value to get Resharper to stop nagging me. The second problem was I was running into was the famous closures issue. I solved this using int copy = i

        IQueryable<User> allBooleansTrue = new List<User>().AsQueryable();
        for (int i = 1; i <= numBools; i++)
        {
            int copy = i; //workaround to prevent closures using reference
            var q = context.UserBooleanAttributes.Where(y => y.UserAttributeID == copy && y.Value)
                .Select(a => a.User);
            if (i == 1)
                allBooleansTrue = q;
            else
                allBooleansTrue = allBooleansTrue.Intersect(q);

The first iteration replaces the contents of my outer variable (allBooleansTrue) with the LINQ statement. Ever other iteration performs an intersection. A little hacky here, but this appears to work (so far). I suppose I should be using expression trees, but that's for another day.

Sorry I changed my code around a bit for the original post, but I'm dead tired and copied this from my test class.

How can I dynamically combine a set of LINQ queries at runtime?

3 Answers3