Linq to entity framework: use dictionary in query

I have:

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

this leads to an error

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

while on lists it works:

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

So, is this linq restriction on EF or am I missing something?

+6
source share
1 answer

This is the nature of the Entity Framework - when you nest expressions in your query, it does everything possible to translate them into SQL so that work can be performed on the server.

In the first example, he tries to translate both a query and a dictionary search query in SQL, and cannot, because he does not know how to do this.

In the second example, you simply pass the list to the request. He knows how to translate this into SQL.

There are several such errors, so you just need to keep that in mind before defining your EF request.

EDIT

Just noticed that your first query uses the query results when reading from a dictionary. Therefore, since you cannot pass the dictionary into the SQL query, you probably need to first extract all the records from the database, and then use LINQ-to-objects to perform the validation, for example:

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

The ToArray() method ToArray() entire result set into memory (there are other ways to do this, but I usually do it), and the next Where clause is executed in LINQ-to-objects instead of LINQ-to-Entities, which means your dictionary will be work fine.

+10
source

Source: https://habr.com/ru/post/898540/


All Articles