Use dictionary inside linq query

Question

I want to use some dictionary inside my linq query, however, since LINQ for entities cannot translate the use of the dictionary, it throws an exception. Actually the same problem was described in the following question: linq to entity framework: use dictionary in query

I was not satisfied with the solution that was described there. I am sure that there is another solution to solving this problem. I do not want to use the ToList / ToArray method, which will bring all the data to memory.

What is the best way to solve this problem without pulling db data into memory?

Answer 1

Your example looks like you don't need dictionary functionality, you just want to have WHERE IN functionality.
To do this, you can use the following:

 var countries = Countries.WhereIn(x => x.CountryId, dict.Keys); 

WhereIn not a built-in query operator, you need to write it yourself - or copy:

 /// <summary> /// Holds extension methods that simplify querying. /// </summary> public static class QueryExtensions { /// <summary> /// Return the element that the specified property value is contained in the specified values. /// </summary> /// <typeparam name="TElement"> The type of the element. </typeparam> /// <typeparam name="TValue"> The type of the values. </typeparam> /// <param name="source"> The source. </param> /// <param name="propertySelector"> The property to be tested. </param> /// <param name="values"> The accepted values of the property. </param> /// <returns> The accepted elements. </returns> public static IQueryable<TElement> WhereIn<TElement, TValue>( this IQueryable<TElement> source, Expression<Func<TElement, TValue>> propertySelector, params TValue[] values) { return source.Where(GetWhereInExpression(propertySelector, values)); } /// <summary> /// Return the element that the specified property value is contained in the specified values. /// </summary> /// <typeparam name="TElement"> The type of the element. </typeparam> /// <typeparam name="TValue"> The type of the values. </typeparam> /// <param name="source"> The source. </param> /// <param name="propertySelector"> The property to be tested. </param> /// <param name="values"> The accepted values of the property. </param> /// <returns> The accepted elements. </returns> public static IQueryable<TElement> WhereIn<TElement, TValue>( this IQueryable<TElement> source, Expression<Func<TElement, TValue>> propertySelector, IEnumerable<TValue> values) { return source.Where(GetWhereInExpression(propertySelector, values.ToList())); } /// <summary> /// Gets the expression for a "where in" condition. /// </summary> /// <typeparam name="TElement"> The type of the element. </typeparam> /// <typeparam name="TValue"> The type of the value. </typeparam> /// <param name="propertySelector"> The property selector. </param> /// <param name="values"> The values. </param> /// <returns> The expression. </returns> private static Expression<Func<TElement, bool>> GetWhereInExpression<TElement, TValue>( Expression<Func<TElement, TValue>> propertySelector, ICollection<TValue> values) { var p = propertySelector.Parameters.Single(); if (!values.Any()) return e => false; var equals = values.Select( value => (Expression)Expression.Equal(propertySelector.Body, Expression.Constant(value, typeof(TValue)))); var body = equals.Aggregate(Expression.OrElse); return Expression.Lambda<Func<TElement, bool>>(body, p); } }