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.

Answer 1

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).

Answer 2

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.

Answer 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