使用LINQ获取具有数据库字段作为参数的不同值。

Question

我试图使用Linq查询从MsSQL数据库选择不同的值，并将数据库字段作为条件传递，但是它给我错误。如果一个表具有以下数据：

Name | Age | Class | school
----------------------------
Anna,     23, grade 2, Havard
Kendricks,34, grade 2, Havard
Vikander, 27, grade 3, Covenant
Hathaway, 18, grade1,  Covenant
Gemma,    23, grade 4, Bowen
Jolie,    23, grade 5, Havard
Arteton,  24, grade 1, Bayero
Ana Armas 30, grade 2, Coventry

现在，我想通过传递“school”或“class”或更多字段来从上面的表中检索数据，然后根据这些字段返回不同的值。我该怎么做？

// filterParam - is the field(class, school)
// then how do I select the distinct values...

以下是我的代码：

  public IEnumerable<ScbDataInfo> GetScbOptionsByFilter(string filterParam) {
            using (SRSContext entityContext = new SRSContext()) {

                var query = (from e in entityContext.Set<ScbDataInfo>()

                             where e[filterParam] == searchParam   //i passed it here
                             orderby e.RefNo, e.datepmt                             
                             select e).Distinct();

                return query.ToArray();

            }
        }

- Ande Caleb

---

不支持该操作。您需要构建自己的谓词生成器，或者可以集成 NuGet 包，例如 DynamicQueryable。 - Igor

可能是[动态列名在where子句中。Entity Framework]的重复问题(https://stackoverflow.com/questions/12421916/dynamic-column-name-in-where-clause-entity-framework)。 - Jota.Toledo

Answer 1

这是我从DataTables.Queryable(由Alexander Krutov开发，遵循MIT协议)中整理出来的一些代码：

它通过表达式工作，调用ToArray之前不需要将数据实体化：

/// <summary>
/// Creates predicate expression like 
/// <code>(T t) => t.SomeProperty.Contains("Constant")</code> 
/// where "SomeProperty" name is defined by <paramref name="stringConstant"/> parameter, and "Constant" is the <paramref name="stringConstant"/>.
/// If property has non-string type, it is converted to string with <see cref="object.ToString()"/> method.
/// </summary>
/// <typeparam name="T">Data type</typeparam>
/// <param name="propertyName">Property name</param>
/// <param name="stringConstant">String constant to construnt the <see cref="string.Contains(string)"/> expression.</param>
/// <param name="caseInsensitive">Case insenstive Contains Predicate?</param>
/// <returns>Predicate instance</returns>
public static Expression<Func<T, bool>> BuildStringContainsPredicate<T>(string propertyName, string stringConstant, bool caseInsensitive)
{
    var type = typeof(T);
    var parameterExp = Expression.Parameter(type, "e");
    var propertyExp = BuildPropertyExpression(parameterExp, propertyName);

    Expression exp = propertyExp;

    // if the property value type is not string, it needs to be casted at first
    if (propertyExp.Type != typeof(string))
    {
        // If we have an Enum, the underlying Entity Framework Provider can not translate the Enum to SQL.
        // Therefore we converting it first to the underlying primitive type (byte, int16, int32, int64 etc)
        //Todo: Sideeffects beobachten
        //Todo: Evtl möglichkeit finden Display Attribute zu implementieren um eine String Suche zu ermöglichen?
        //Todo: Notwendigkeit in NET Core 2.1 überprüfen
        if (propertyExp.Type.IsEnum)
        {
            exp = Expression.Convert(exp, Enum.GetUnderlyingType(propertyExp.Type));
        }

        exp = Expression.Call(exp, ObjectToString);
    }

    // call ToLower if case insensitive search
    if (caseInsensitive)
    {
        exp = Expression.Call(exp, StringToLower);
        stringConstant = stringConstant.ToLower();
    }
    var someValue = Expression.Constant(stringConstant, typeof(string));
    var containsMethodExp = Expression.Call(exp, StringContains, someValue);
    return Expression.Lambda<Func<T, bool>>(containsMethodExp, parameterExp);
}

/// <summary>
/// Builds the property expression from the full property name.
/// </summary>
/// <param name="param">Parameter expression, like <code>e =></code></param>
/// <param name="propertyName">Name of the property</param>
/// <returns>MemberExpression instance</returns>
private static MemberExpression BuildPropertyExpression(ParameterExpression param, string propertyName)
{
    var parts = propertyName.Split('.');
    Expression body = param;
    foreach (var member in parts)
    {
        body = Expression.Property(body, member);
    }
    return (MemberExpression)body;
}

/// <summary>
/// <see cref="object.ToString()"/> method info. 
/// Used for building search predicates when the searchable property has non-string type.
/// </summary>
private static readonly MethodInfo ObjectToString = typeof(object).GetMethod(nameof(ToString));


/// <summary>
/// <see cref="string.ToLower()"/> method info. 
/// Used for conversion of string values to lower case.
/// </summary>
private static readonly MethodInfo StringToLower = typeof(string).GetMethod(nameof(string.ToLower), new Type[] { });

/// <summary>
/// <see cref="string.Contains(string)"/> method info. 
/// Used for building default search predicates.
/// </summary>
private static readonly MethodInfo StringContains = typeof(string).GetMethod(nameof(string.Contains), new[] { typeof(string) });

这将创建一个过滤表达式，其中propertyName是列名，stringConstant是搜索值，bool值用于指定搜索是否区分大小写。 T是您的IQueryable<T>类型。

使用PredicateBuilder，您可以执行以下操作：

public static IQueryable<T> FilterColumns(this IQueryable<T> query, IEnumerable<string> columns, string searchValue)
{
    Expression<Func<T, bool>> predicate = null;
    foreach (var column in columns)
    {
        var expr = BuildStringContainsPredicate<T>(column,
                       searchValue, false);
        predicate = predicate == null ? PredicateBuilder.Create(expr) : predicate.Or(expr);
    }
    return query.Where(predicate);
}

这提供了一个扩展方法，因此包含的类必须是静态的。

现在你可以执行以下操作：

entityContext.ScbDataInfos
    .FilterColumns(columnNames, searchValue)
    .OrderBy(e => e.RefNo)
    .ThenBy(e => e.datepm)
    .Distinct()
    .ToArray();

- Christian Gollhardt

---

我希望使用Expressions来实现它。 - Sebastian Oscar Lopez

Answer 2

在这里，我展示了一个可能的解决方案，并通过对代码的注释来解释它是如何工作的。

    /// <summary>
    /// Filter ScbDataInfo with de field and value indicated
    /// </summary>
    /// <param name="filterParam">Field name</param>
    /// <param name="searchParam">Value used in filter</param>
    /// <returns></returns>
    public IEnumerable<ScbDataInfo> GetScbOptionsByFilter(string filterParam, string searchParam)
    {
        // Here get property using reflection 
        var typeScbDataInfo = typeof(ScbDataInfo);
        var property = typeScbDataInfo.GetProperty(filterParam);

        //var filterExpression =
        using (var context = new SRSContext())
        {
            var query = context.ScbDataInfo
                .ToArray() // It force linq to sql to obtain all records from database. A poor implementation
                .Where(
                    m => property.GetValue(m) // Get entity with reflection
                            .ToString() // Convert to string because searchParam is string. It could be changed for the correct type or using dynamic type
                            .Equals(searchParam) // Simple equals for filter
                );
            return query.ToArray(); // Return array. Poor implementation
        }
    }

示例如何进行测试。

    static void Main(string[] args)
    {
        Console.WriteLine("Filter NAME:");
        var filterName = Console.ReadLine();
        Console.WriteLine("Filter VALUE:");
        var filterValue = Console.ReadLine();

        var program = new Program();
        var results = program.GetScbOptionsByFilter(filterName, filterValue);

        Console.WriteLine($"Total results: {results.Count()}");
        Console.ReadKey();
    }

PS：由于使用了ToArray()，因此它的实现效果较差，所以它获取了所有记录，然后才执行Where操作。

我认为可能可以使用表达式树来实现更好的实现。

无论如何，在普通PC上处理几千条记录是可以正常工作的。

- Sebastian Oscar Lopez