Linq Query to get DataContext Entities based on ILookup

Question

If I have an IEnumerable<int> Values, I can write a Linq to Entities query, like so:

DataContext.Answers.Where(a => a.Organization == CurrentUser.Organization ||
    Values.Contains(a.QuestionId))

The (Values.Contains(a.QuestionId)) part is what my question concerns.

If Values were implemented instead as: ILookup<string, IEnumerable<int>>Values, how could I rewrite the query to get Answers where Values contains the key(a.Organization) and the IEnumerable values for that key contains a.QuestionId?

I've put in an answer, but as a clarification, are you sure you want a `||` instead of an `&&` in that logic? — Steve Mitcham, Feb 13 '15 at 15:56
Yes, the basic premise is that the `ILookup` is going to be a collection of values that another `User` is sharing with `CurrentUser`. In my case, I want to get all Answers that either belong to the `CurrentUser`, or are being shared with the `CurrentUser`. I'm using the `ILookup` because the answers that a `User` shares a specific to their `Organization` and `Company` pairing. — Gunner Barnes, Feb 13 '15 at 16:03

score 0 · Answer 1 · answered Feb 13 '15 at 16:27

First you'll need to flatten the ILookup<string, IEnumerable<int>> into an IEnumerable of some item that has both the organization and the question Id. You'll need to get all of the groups in the lookup, get all of the collections of ids from the group, and then get all of the ids in that collection, and transform each of them into an object holding both that ID and the group's key. You can then use Contains on that collection to see if the answer's organization and question ID are in that collection of pairings. By doing this you allow the collection to be translated into an IN clause in SQL. Of course, if the lookup is particularly large, then that will be a problem; if it's small, it won't be.

You would do that like so:

var flattenedValues = (from grouping in Values
                        from ids in grouping
                        from id in ids
                        select new
                        {
                            Organization = grouping.Key,
                            QuestionId = id,
                        })
                        .ToList();
DataContext.Answers.Where(a => a.Organization == CurrentUser.Organization ||
    flattenedValues.Contains(new
                        {
                            Organization = a.Organization,
                            QuestionId = a.QuestionId,
                        }));

If the lookup is particularly big, you may have no other choice but to pull all of the data from the table into memory and filter it by looking through the lookup on the application's side, or upload the data in that lookup into a temporary table in the list.

This gives me the error, "Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context." — Gunner Barnes, Feb 13 '15 at 17:50
@GunnerBarnes Then you probably need to either upload the data into a temporary table or pull the data from the DB down into memory, as mentioned in the answer. — Servy, Feb 13 '15 at 18:39

Linq Query to get DataContext Entities based on ILookup

1 Answers1