Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 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?

like image 652
ren Avatar asked Oct 03 '11 18:10

ren


People also ask

Can we use LINQ with dictionary in C #?

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.

Can we use SQL query in Entity Framework?

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.

Does Entity Framework support dictionary?

Dictionary data type mapping. For Entity Framework Core 3 and 5, dotConnect for PostgreSQL now supports mapping the dictionary .


1 Answers

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.

like image 50
Joe Enos Avatar answered Sep 22 '22 15:09

Joe Enos