在实体框架中动态添加 where 子句

Question

我有这个sql语句

SELECT userID from users WHERE
(name='name1' AND username='username1') OR
(name='name2' AND username='username2') OR
(name='name3' AND username='username3') OR
..........
(name='nameN' AND username='usernameN')

如何使用LINQ通过实体框架实现这个语句？

Answer 1

您可以使用一个名为 PredicateBuilder 的漂亮东西。像这样使用它

var pr = PredicateBuilder.False<User>();
foreach (var name in names)
{
    pr = pr.Or(x => x.Name == name && x.Username == name);
}
return query.AsExpandable().Where(pr);

Answer 2

 Expression<Func<User, bool>> whereExpression = null;
 foreach (var name in names)
 {
     Expression<Func<User, bool>> e1 = u => u.Name == name;
     Expression<Func<User, bool>> andExpression = e1.And(u => u.Username == name);
     whereExpression = whereExpression == null 
         ? andExpression 
         : whereExpression.Or(andExpression);
 }
 return query.Where(whereExpression);

这个助手可能会帮助你。

public static class ExpressionExtensions
{
    public static Expression<Func<T, bool>> And<T>(
        this Expression<Func<T, bool>> leftExpression, 
        Expression<Func<T, bool>> rightExpression)
    {
        if (leftExpression == null) return rightExpression;
        if (rightExpression == null) return leftExpression;
        var paramExpr = Expression.Parameter(typeof(T));
        var exprBody = Expression.And(leftExpression.Body, rightExpression.Body);
        exprBody = (BinaryExpression)new ParameterReplacer(paramExpr)
            .Visit(exprBody);

        return Expression.Lambda<Func<T, bool>>(exprBody, paramExpr);
    }

    public static Expression<Func<T, bool>> Or<T>(
        this Expression<Func<T, bool>> leftExpression, 
        Expression<Func<T, bool>> rightExpression)
    {
        if (leftExpression == null) return rightExpression;
        if (rightExpression == null) return leftExpression;
        var paramExpr = Expression.Parameter(typeof(T));
        var exprBody = Expression.Or(leftExpression.Body, rightExpression.Body);
        exprBody = (BinaryExpression)new ParameterReplacer(paramExpr)
            .Visit(exprBody);

        return Expression.Lambda<Func<T, bool>>(exprBody, paramExpr);
    }
}

和：

class ParameterReplacer : ExpressionVisitor
{
    private readonly ParameterExpression _parameter;

    protected override Expression VisitParameter(ParameterExpression node)
    {
        return base.VisitParameter(_parameter);
    }

    internal ParameterReplacer(ParameterExpression parameter)
    {
        _parameter = parameter;
    }
}

Answer 3

注意：这是根据我现有的内容进行修改的，因此它可能无法开箱即用。但这将是一个很好的起点。

public static IQueryable<TEntity> Where<TEntity>(
    this IQueryable<TEntity> source,
    IEnumerable<WhereSpecifier> orClauses) where TEntity : class
{
    if (!orClauses.Any()) return source.Where(t => false);
    Type type = typeof (TEntity);
    ParameterExpression parameter = null;
    Expression predicate = Expression.Constant(false, typeof (bool));
    ParameterExpression whereEnt = Expression.Parameter(type, "WhereEnt");
    foreach (WhereSpecifier orClause in orClauses)
    {
        Expression selector;
        if (orClause.Selector != null) {
            selector = orClause.Selector;
            parameter = orClause.Parameter;
        }
        else
        {
            parameter = whereEnt;
            Type selectorResultType;
            selector = GenerateSelector<TEntity>(parameter, orClause.Column, 
                out selectorResultType);
        }
        Expression clause = selector.CallMethod(orClause.Method, 
            MakeConstant(selector.Type, orClause.Value), orClause.Modifiers);
        predicate = Expression.Or(predicate, clause);
    }

    var lambda = Expression.Lambda(predicate, whereEnt);
    var resultExp = Expression.Call(typeof (Queryable), "Where", new[] {type},
        source.Expression, Expression.Quote(lambda));
    return source.Provider.CreateQuery<TEntity>(resultExp);
}

生成选择器：

public static Expression GenerateSelector<TEntity>(
    ParameterExpression parameter, string propertyName,
    out Type resultType) where TEntity : class
{
    //  create the selector part, but support child properties
    PropertyInfo property;
    Expression propertyAccess;
    if (propertyName.Contains('.'))
    {
        // support to be sorted on child fields.
        String[] childProperties = propertyName.Split('.');
        property = typeof (TEntity).GetProperty(childProperties[0]);
        propertyAccess = Expression.MakeMemberAccess(parameter, property);
        for (int i = 1; i < childProperties.Length; i++)
        {
            property = property.PropertyType.GetProperty(childProperties[i]);
            propertyAccess = Expression
                .MakeMemberAccess(propertyAccess, property);
        }
    }
    else
    {
        property = typeof (TEntity).GetProperty(propertyName);
        propertyAccess = Expression.MakeMemberAccess(parameter, property);
    }
    resultType = property.PropertyType;
    return propertyAccess;
}

WHereSpecifier：

public class WhereSpecifier
{
    public WhereSpecifier(string column, CheckMethod method, string value, 
        CheckMethodModifiers modifiers)
    {
        Modifiers = modifiers;
        Value = value;
        Column = column;
        Method = method;
    }

    public WhereSpecifier(string column, CheckMethod method, string value)
        : this(column, method, value, CheckMethodModifiers.None)
    {
    }
    public Expression Selector { get; set; }
    public ParameterExpression Parameter { get; set; }
    public string Column { get; set; }
    public CheckMethod Method { get; set; }
    public CheckMethodModifiers Modifiers { get; set; }
    public string Value { get; set; }
}

用法：

var column = typeof(TEntity).Name + "ID";
var where = from id in SelectedIds 
            select new WhereSpecifier(column, CheckMethod.Equal, id.ToString());
return GetTable().Where(where);

Answer 4

我尝试了 @Egor Pavlikhin 解决方案，但我得到“LINQ to Entities 不支持 LINQ 表达式节点类型“Invoke”。”。

根据这个 您可以使用 谓词扩展：

var predicate = PredicateExtensions.Begin<User>();
foreach (var name in names)
{
    pr = pr.Or(x => x.Name == name);
}
return _context.Users.Where(predicate);

Answer 5

不要忘记实体框架也理解实体sql，所以你可以在字符串中执行这部分查询。当您需要做动态的事情时，构建字符串非常方便。

Answer 6

我必须根据用户界面选择动态构建“Where”子句的谓词。 'System.Dynamic.Linq' 允许使用字符串进行谓词。

foreach (var name in names)
{
    query = query.Where("Name=@0 And UserName=@1", name, name);
}
return query;

“System.Dynamic.Linq”可作为 nuget 包使用。查看 Scott Guthrie 对该主题的介绍

Answer 7

我发现这种方法太简单了：

    var query = context.InvoiceHeader.Where( i =>  i.DateInvoice >= model.datedu && i.DateInvoice <= model.dateau).AsQueryable();
        if(model.name != null)
        {
            query = query.Where(i =>  i.InvoiceNum.Equals(model.name));
        }
        if (model.status != 0 )
        {
            query = query.Where(i => i.REF_InvoiceStatusRecId == model.status);
        }

        if (model.paiements != 0)
        {
            query = query.Where(i => i.REF_PaymentTermRecId  == model.paiements);
        }
        query = query.AsQueryable().OrderByDescending(x => x.RecId);

Answer 8

对于 System.Dynamic.Linq

这是动态 sql：

//using System.Linq.Dynamic.Core;
//using Microsoft.EntityFrameworkCore.Metadata;

var listName = Enumerable.Range(1, 6).Select(i=>new
{
    name=$"name{i}",
    username=$"username{i}"
}).ToList();

var listOr = new List<string>();
var entityType = _context.XXX.EntityType;
var schema = entityType.GetSchema();
var tableName = entityType.GetTableName();
var storeObjectIdentifier = StoreObjectIdentifier.Table(tableName, schema);
var name = entityType.FindProperty(nameof(XXX.Name)).GetColumnName(storeObjectIdentifier);
var userName = entityType.FindProperty(nameof(XXX.UserName)).GetColumnName(storeObjectIdentifier);
foreach (var tParm in listName)
{
    listOr.Add($"({name}={tParm.name} AND {userName}={tParm.username})");
}
var result = _context.XXX.Where(string.Join(" OR ", listOr));

var sql = query.ToQueryString();