LINQ to Entities Does Not Recognize Method Exception

Question

I have something 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); 

When executing the Linq statement, this excludes the statement LINQ to Entities does not recognize the "Boolean ContainsKey (System.String)" method, and this method cannot be translated into a storage expression.

Answer 1

To translate an expression into a database query, the database must somehow know the contents of your dictionary and have access to it from the query. There is no dictionary mechanism in SQL, but it doesn’t matter because you don’t need a dictionary, because you are just looking for keys whose value is a specific constant. You can include this key set in the list and see if this list contains what you are 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 in fact you do not have an empty string as a key in your dictionary, which means that you can get rid of the IsNullOrEmpty call and just have where groupsList.Contains(hp.GroupName) .

Answer 2

You are not allowed to use the dictionary in the WHERE clause to limit the result set, since LINQ To Entities will try to turn this into SQL and, unfortunately, it does not know how to process the Dictionary collection.

Check out this link: linq to entity framework: use dictionary in query