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?

Answer 1

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.