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?
When we need to transform 2 columns of data table to a dictionary, we can use LINQ. Dictionary is a Key Value Pair collection and Key should be unique. You can create the Dictionary<TKey, TValue> object by passing the type of keys and values it can store.
Entity Framework allows you to execute raw SQL queries for the underlying relational database. The following methods can be used to execute raw SQL queries to the database using Entity Framework 6. x: DbSet.
Dictionary data type mapping. For Entity Framework Core 3 and 5, dotConnect for PostgreSQL now supports mapping the dictionary .
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With