在linq中使用字典查询 [英] Use dictionary inside linq query

问题描述

我想在我的LINQ查询中使用一些字典，但是由于LINQ对实体不能翻译使用字典，它会引发异常。
实际上在以下问题中描述了相同的问题：
linq到实体框架：在查询中使用字典

我对这里所描述的解决方案不满意。我确定这个问题还有一些其他的解决方案。我不想使用ToList / ToArray方法 - 这将把所有数据都带到内存中。

解决这个问题的最好方法是什么，而不需要拉数据库数据到内存？

解决方案

您的示例看起来像您实际上不需要字典的功能，您只需要 WHERE IN 功能。

您可以使用以下操作来实现：

  var countries = Countries.WhereIn（x => x.CountryId，dict.Keys）; 
  

WhereIn 不是内置的查询运算符你需要自己写 - 或复制：

  ///< summary> 
 ///拥有简化查询的扩展方法。 
 ///< / summary> 
 public static class QueryExtensions 
 {
 ///< summary> 
 ///返回指定属性值包含在指定值中的元素。 
 ///< / summary> 
 ///< typeparam name =TElement>元素的类型。 < / typeparam> 
 ///< typeparam name =TValue>值的类型。 < / typeparam> 
 ///< param name =source>来源。 < / PARAM> 
 ///< param name =propertySelector>要测试的财产。 < / PARAM> 
 ///< param name =values>接受的财产价值。 < / PARAM> 
 ///< returns>被接受的元素。 < /回报> 
 public static IQueryable< TElement>其中In< TElement，TValue>（
 this IQueryable< TElement> source，
 Expression&FunC< TElement，TValue>> propertySelector，
 params TValue [] values）
 {
 return source.Where（GetWhereInExpression（propertySelector，values））; 
} 
 
 ///< summary> 
 ///返回指定属性值包含在指定值中的元素。 
 ///< / summary> 
 ///< typeparam name =TElement>元素的类型。 < / typeparam> 
 ///< typeparam name =TValue>值的类型。 < / typeparam> 
 ///< param name =source>来源。 < / PARAM> 
 ///< param name =propertySelector>要测试的财产。 < / PARAM> 
 ///< param name =values>接受的财产价值。 < / PARAM> 
 ///< returns>被接受的元素。 < /回报> 
 public static IQueryable< TElement>其中In< TElement，TValue>（
 this IQueryable< TElement>来源，
表达式< Func< TElement，TValue>> propertySelector，
 IEnumerable< TValue>值）
 { b $ b return source.Where（GetWhereInExpression（propertySelector，values.ToList（）））; 
} 
 
 ///< summary> 
 ///获取where in条件的表达式。 
 ///< / summary> 
 ///< typeparam name =TElement>元素的类型。 < / typeparam> 
 ///< typeparam name =TValue>值的类型。 < / typeparam> 
 ///< param name =propertySelector>属性选择器。 < / PARAM> 
 ///< param name =values>价值。 < / PARAM> 
 ///< returns>表达方式。 < /回报> 
 private static Expression< Func< TElement，bool>> GetWhereInExpression< TElement，TValue>（
 Expression< Func< TElement，TValue>> propertySelector，ICollection< TValue>值）
 {
 var p = propertySelector.Parameters.Single（）; 
 if（！values.Any（））
 return e =>假; 
 
 var equals = 
 values.Select（
 value => 
（Expression）Expression.Equal（propertySelector.Body，Expression.Constant（value，typeof TValue））））; 
 var body = equals.Aggregate（Expression.OrElse）; 
 
 return Expression.Lambda } 
} 
  

I want to use some dictionary inside my linq query, however since LINQ to entities can't 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 wasn't satisfied of the solution which was described there. I'm sure there is some other solution for this problem. I don't want to use the ToList/ToArray method - which will bring all data to memory.

What is the best way to solve this issue, without pulling the db data to memory?

解决方案

Your example looks like you actually don't need the functionality of a dictionary, you simply want to have WHERE IN functionality.
You could use the following to achieve this:

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

WhereIn is 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's 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's 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);
    }
}

这篇关于在linq中使用字典查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！