linq to entity framework: use dictionary in query

Question

I have:

Dictionary<int, List<int>> dict = new ...
var a = SomeEntity.Where(f => dict[f.key].Contains(f.someValue)) 

this produces error

 LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[System.Int32] get_Item(Int32)' method

while with lists it works:

List<int> l = new ...
var a = SomeEntity.Where(f => l.Contains(f.someValue))

So is this limitation of linq to EF or am I missing something?

Answer 1

It's the nature of Entity Framework - when you put expressions into your query, it does its best to translate them into SQL, so that the work can be done on the server.

In your first example, it tries to translate both the query and the dictionary retrieval call into SQL, and is unable, since it doesn't know how to.

In your second example, you're just passing a list into the query. It does know how to translate that into SQL.

There are a few gotchas like that, so you just have to be mindful about it before defining your EF query.

EDIT

Just noticed that your first query makes use of the query results when reading from the dictionary. So since you can't actually pass your dictionary into the SQL query, you'll probably need to retrieve all the records from the DB first, then use LINQ-to-objects to perform your check, like:

Dictionary<int, List<int>> dict = new ...
var a = SomeEntity
    .ToArray()
    .Where(f => dict[f.key].Contains(f.someValue));

The ToArray() method pulls the entire result set into memory (there are other ways of doing it, but this is how I usually do it), and the following Where clause runs in LINQ-to-objects instead of LINQ-to-Entities, meaning your dictionary code will run fine.