LINQ to Entities does not recognize the method exception

Question

I have some thing like this

SecuritySearcher sc = new SecuritySearcher();
Dictionary<string, bool> groupsMap = 
    sc.GetUserGroupMappings(domainName, currentUser, distGroups.ToList());

IQueryable<HotelTravel> groupq = 
    (from hotel in qHs
    join hp in qHps on hotel.HotelTravelId equals hp.HotelTravelId
    where !string.IsNullOrEmpty(hp.GroupName)
       && groupsMap.ContainsKey(hp.GroupName) 
       && groupsMap[hp.GroupName] == true
    select hotel);

While executing Linq statement it is throwing exception saying LINQ to Entities does not recognize the method 'Boolean ContainsKey(System.String)' method, and this method cannot be translated into a store expression.

possible duplicate of [linq to entity framework: use dictionary in query](http://stackoverflow.com/questions/7638859/linq-to-entity-framework-use-dictionary-in-query) — jrummell, Dec 14 '11 at 14:23
@jrummell: This isn't a duplicate of the question you link to because it uses dictionaries in a different way. — Gabe, Dec 14 '11 at 15:18

Gabe · Accepted Answer · 2011-12-14T14:19:01.653

In order to translate your expression into a database query, the database would somehow have to know the contents of your dictionary and have a way to access it from the query. There is no dictionary mechanism in SQL, but that doesn't matter because you don't need a dictionary because you're just looking for keys whose value is a certain constant. You can turn that set of keys into a list and see if that list contains what you're looking for:

var groupsList = (from kvp in groupsMap     // find all keys in groupsMap
                  where kvp.Value == true   // where the value is set to True
                  select kvp.Key).ToList();

IQueryable<HotelTravel> groupq =
    from hotel in qHs
    join hp in qHps on hotel.HotelTravelId equals hp.HotelTravelId
    where !string.IsNullOrEmpty(hp.GroupName)
          && groupsList.Contains(hp.GroupName)
    select hotel;

I suspect that you don't actually have the empty string as a key in your dictionary, though, which means you can get rid of the IsNullOrEmpty call and just have where groupsList.Contains(hp.GroupName).

Hi thanks for quick replay but I am getting this exception now LINQ to Entities does not recognize the method 'System.Collections.Generic.List`1[System.String] ToList[String](System.Collections.Generic.IEnumerable`1[System.String])' method, and this method cannot be translated into a store expression. — Pavan Josyula, Dec 14 '11 at 14:11
Sorry, but I should have put the list-making into a separate statement. You can see from my edited answer how to do that now. — Gabe, Dec 14 '11 at 14:19

score 6 · Answer 2 · answered Sep 26 '18 at 14:35

I had the same issue. The easiest solution is to replace the method

where groupsMap.ContainsKey(hp.GroupName)

with the method with the same functionality that is recognized by LINQ to Entities:

where groupsMap.Keys.Contains(hp.GroupName)

As the answer here says, these two functions do exactly the same thing.

score 3 · Answer 3 · edited May 23 '17 at 10:24

3

You are not allowed to use your dictionary in the WHERE clause to limit your result set because LINQ To Entities will try to turn this into SQL and unfortunately, it doesn't know how to handle the Dictionary collection.

See this link: linq to entity framework: use dictionary in query

edited May 23 '17 at 10:24

Community

1
1

answered Dec 14 '11 at 13:52

Josh

2,955
1
19
28

LINQ to Entities does not recognize the method exception

3 Answers3