Entity Framework中带有OR条件的动态查询

Question

Entity Framework中带有OR条件的动态查询

c#sql-serverentity-frameworklinqlinq-to-entities

69

我正在创建一个应用程序，用于搜索数据库并允许用户动态添加任何条件（大约有50个可能条件），就像以下SO问题：Creating dynamic queries with entity framework。我目前已经实现了一个检查每个条件的搜索功能，如果条件不为空，则将其添加到查询中。

C#

var query = Db.Names.AsQueryable();
  if (!string.IsNullOrWhiteSpace(first))
      query = query.Where(q => q.first.Contains(first));
  if (!string.IsNullOrWhiteSpace(last))
      query = query.Where(q => q.last.Contains(last));
  //.. around 50 additional criteria
  return query.ToList();

这段代码在 SQL Server 中会生成类似于以下内容的结果（为了更易理解，我进行了简化）

SQL

SELECT
    [Id],
    [FirstName],
    [LastName],
    ...etc
FROM [dbo].[Names]
WHERE [FirstName] LIKE '%first%'
  AND [LastName] LIKE '%last%'

我现在正在尝试通过实体框架和C#生成以下SQL，并用OR代替AND，同时仍然保持动态添加条件的能力。

SQL

SELECT
    [Id],
    [FirstName],
    [LastName],
    ...etc
  FROM [dbo].[Names]
WHERE [FirstName] LIKE '%first%'
  OR [LastName] LIKE '%last%' <-- NOTICE THE "OR"

通常一个查询的标准不会超过两三个，但将它们合并成一个巨大的查询不是一个选择。我尝试过使用concat、union和intersect，但它们只是复制了查询并用UNION连接它们。

有没有一种简单清晰的方法，可以使用实体框架为动态生成的查询添加“OR”条件？

编辑我的解决方案-2015年9月29日

自从发布这篇文章以来，我注意到它引起了一些关注，所以我决定发布我的解决方案

// Make sure to add required nuget
// PM> Install-Package LinqKit

var searchCriteria = new 
{
    FirstName = "sha",
    LastName = "hill",
    Address = string.Empty,
    Dob = (DateTime?)new DateTime(1970, 1, 1),
    MaritalStatus = "S",
    HireDate = (DateTime?)null,
    LoginId = string.Empty,
};

var predicate = PredicateBuilder.False<Person>();
if (!string.IsNullOrWhiteSpace(searchCriteria.FirstName))
{
    predicate = predicate.Or(p => p.FirstName.Contains(searchCriteria.FirstName));
}

if (!string.IsNullOrWhiteSpace(searchCriteria.LastName))
{
    predicate = predicate.Or(p => p.LastName.Contains(searchCriteria.LastName));
}

// Quite a few more conditions...

foreach(var person in this.Persons.Where(predicate.Compile()))
{
    Console.WriteLine("First: {0} Last: {1}", person.FirstName, person.LastName);
}

- Ben Anderson

4

你可能想了解一些类似于Predicate Builder的东西，它可以使进行and和or操作更容易。 - Steven V

Predicate Builder确实是解决方案，但我只是好奇...为什么组合它们“不是一个选项”？您说它不会超过两到三个项目。SQL Server可能能够优化您的大型组合查询，以在具有相同条件的单个查询的速度下运行。您是否测试过并发现连接查询是性能瓶颈？ - Ocelot20

研究谓词构建器，我相信它是答案。感谢Steven V，如果您想提交答案，我会将其标记为已回答。将它们合并成一个大查询不是一个选项，因为我需要检查每个条件是否为空，然后才能进行实际过滤，而这超过了50个条件。这将使查询变慢且难以管理。 - Ben Anderson

Predicate Builder的替代方案是在此处接受的答案中的代码：https://dev59.com/23DXa4cB1Zd3GeqP9zvt - GilShalit

4个回答

25

虽然LINQKit及其PredicateBuilder非常灵活，但是通过一些简单的实用工具（每个工具都可以作为其他表达式操作的基础）直接完成也是可能的：

首先是一个通用的表达式替换器：

public class ExpressionReplacer : ExpressionVisitor
{
    private readonly Func<Expression, Expression> replacer;

    public ExpressionReplacer(Func<Expression, Expression> replacer)
    {
        this.replacer = replacer;
    }

    public override Expression Visit(Expression node)
    {
        return base.Visit(replacer(node));
    }
}

接下来，我们提供一个简单的实用方法，用于在给定表达式中将一个参数的使用替换为另一个参数：

public static T ReplaceParameter<T>(T expr, ParameterExpression toReplace, ParameterExpression replacement)
    where T : Expression
{
    var replacer = new ExpressionReplacer(e => e == toReplace ? replacement : e);
    return (T)replacer.Visit(expr);
}

这是必要的，因为两个不同表达式中的lambda参数实际上是不同的参数，即使它们具有相同的名称。例如，如果您想要得到q => q.first.Contains(first) || q.last.Contains(last)，那么在q.last.Contains(last)中的q必须是lambda表达式开头提供的q完全相同的q。

接下来，我们需要一个通用的Join方法，它能够将Func<T, TReturn>风格的Lambda表达式与给定的二元表达式生成器连接起来。

public static Expression<Func<T, TReturn>> Join<T, TReturn>(Func<Expression, Expression, BinaryExpression> joiner, IReadOnlyCollection<Expression<Func<T, TReturn>>> expressions)
{
    if (!expressions.Any())
    {
        throw new ArgumentException("No expressions were provided");
    }
    var firstExpression = expressions.First();
    var otherExpressions = expressions.Skip(1);
    var firstParameter = firstExpression.Parameters.Single();
    var otherExpressionsWithParameterReplaced = otherExpressions.Select(e => ReplaceParameter(e.Body, e.Parameters.Single(), firstParameter));
    var bodies = new[] { firstExpression.Body }.Concat(otherExpressionsWithParameterReplaced);
    var joinedBodies = bodies.Aggregate(joiner);
    return Expression.Lambda<Func<T, TReturn>>(joinedBodies, firstParameter);
}

我们将使用这个方法与Expression.Or一起使用，但您也可以使用相同的方法来实现各种目的，例如使用Expression.Add来组合数字表达式。

最后，将所有内容放在一起，您可能会得到类似于以下的东西：

var searchCriteria = new List<Expression<Func<Name, bool>>();

  if (!string.IsNullOrWhiteSpace(first))
      searchCriteria.Add(q => q.first.Contains(first));
  if (!string.IsNullOrWhiteSpace(last))
      searchCriteria.Add(q => q.last.Contains(last));
  //.. around 50 additional criteria
var query = Db.Names.AsQueryable();
if(searchCriteria.Any())
{
    var joinedSearchCriteria = Join(Expression.Or, searchCriteria);
    query = query.Where(joinedSearchCriteria);
}
  return query.ToList();

- StriplingWarrior

2

太棒了！我在GitHub上创建了一个工作示例的Gist（至少在.NET 5.0中编译）https://gist.github.com/princefishthrower/6620fcded6b2600bbd10f4100c55401c - fullStackChris

我的回答基于你的回答：https://dev59.com/ZGIj5IYBdhLWcg3wn2do#69156702。谢谢。 - Mr. Squirrel.Downy

我该如何使用类似的查询语句.Where(entity => entity.Collection.Any(joinedSearchCriteria))？我该如何构建where查询的表达式？我已经尝试了几个小时，但是没有找到获取Any方法信息的方法。 - Snaketec

@Snaketec：只要您的标准泛型类型与 entity.Collection 匹配，它应该可以正常工作。看起来您应该编写一个单独的问题，详细说明您尝试过什么以及出现了什么行为/错误。 - StriplingWarrior

14

有没有一种简单而干净的方法，可以使用Entity Framework添加"OR"条件到动态生成的查询中？

是的，您可以通过仅依赖包含单个布尔表达式的单个where子句来实现这一点，该表达式的OR部分在运行时动态地“禁用”或“启用”，从而避免了安装LINQKit或编写自定义谓词构建器。

关于您的示例：

var isFirstValid = !string.IsNullOrWhiteSpace(first);
var isLastValid = !string.IsNullOrWhiteSpace(last);

var query = db.Names
  .AsQueryable()
  .Where(name =>
    (isFirstValid && name.first.Contains(first)) ||
    (isLastValid && name.last.Contains(last))
  )
  .ToList();

正如您在上面的示例中所看到的，我们根据先前评估的前提条件（例如isFirstValid）动态切换where过滤器表达式的“或”部分的“开”或“关”状态。

例如，如果isFirstValid不是true，那么name.first.Contains(first)将被短路，既不会执行也不会影响结果集。此外，EF Core的DefaultQuerySqlGenerator还会在执行之前进一步优化和简化 where内的布尔表达式（例如false && x || true && y || false && z可能通过简单的静态分析简化为y）。

请注意：如果没有一个前提条件是true，那么结果集将为空——我假设这是您想要的行为。然而，如果出于某种原因，您更喜欢从IQueryable源选择所有元素，则可以将一个最终变量添加到表达式中，该变量计算结果为true（例如.Where( ... || shouldReturnAll)，其中var shouldReturnAll = !(isFirstValid || isLastValid)或类似的东西）。

最后一句话：这种技术的缺点是它强制您构建一个“集中式”布尔表达式，该表达式位于查询所在的同一方法体中（更精确地说是查询的where部分）。如果您出于某种原因想要分散谓词的构建过程并将它们作为参数注入或通过查询生成器链接它们，则最好坚持像其他答案中建议的谓词生成器。否则，请享受这种简单的技术 :)

- Felix K.

1

我喜欢这个用于大多数简单的动态查询。谢谢！ - Canolyb1

是的，动态的意思是你有两个可选的OR语句，但并不真正动态，因为你仍然需要在Where()函数中编写任何你需要的内容... - fullStackChris

1

@fullStackChris，是的，这就是我在答案结尾处提到的缺点。但是，通常这种可选的OR语句非常方便，并且足够“动态”，可以解决手头的问题。然而，当然对于更复杂的查询谓词链，人们会采用其他技术。 - Felix K.

2

基于StriplingWarrior的回答，我编写了我的linq扩展以使用linq方式完成这项工作：

https://github.com/Flithor/ReusableCodes/blob/main/EFCore/OrPredicate.cs

代码（可能不是最新的）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace Flithors_ReusableCodes
{
    /// <summary>
    /// Make <see cref="IQueryable{T}"/> support or predicate in linq way
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public interface IQueryOr<T>
    {
        IQueryOr<T> WhereOr(Expression<Func<T, bool>> predicate);
        IQueryable<T> AsQueryable();
    }
    /// <summary>
    /// The extension methods about or predicate
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public static class OrPredicate
    {
        /// <summary>
        /// Private or predicate builder
        /// </summary>
        /// <typeparam name="T"></typeparam>
        private class OrPredicateBuilder<T> : IQueryOr<T>
        {
            List<Expression<Func<T, bool>>> predicates = new List<Expression<Func<T, bool>>>();
            IQueryable<T> sourceQueryable;

            #region private methods
            internal OrPredicateBuilder(IQueryable<T> sourceQueryable) => this.sourceQueryable = sourceQueryable;
            private OrPredicate(IQueryable<T> sourceQueryable, IEnumerable<Expression<Func<T, bool>>> predicates)
            {
                this.sourceQueryable = sourceQueryable;
                this.predicates.AddRange(predicates);
            }

            //===============================================
            // Code From: https://dev59.com/ZGIj5IYBdhLWcg3wn2do#50414456
            private class ExpressionReplacer : ExpressionVisitor
            {
                private readonly Func<Expression, Expression> replacer;

                public ExpressionReplacer(Func<Expression, Expression> replacer)
                {
                    this.replacer = replacer;
                }

                public override Expression Visit(Expression node)
                {
                    return base.Visit(replacer(node));
                }
            }
            private static TExpression ReplaceParameter<TExpression>(TExpression expr, ParameterExpression toReplace, ParameterExpression replacement) where TExpression : Expression
            {
                var replacer = new ExpressionReplacer(e => e == toReplace ? replacement : e);
                return (TExpression)replacer.Visit(expr);
            }
            private static Expression<Func<TEntity, TReturn>> Join<TEntity, TReturn>(Func<Expression, Expression, BinaryExpression> joiner, IReadOnlyCollection<Expression<Func<TEntity, TReturn>>> expressions)
            {
                if (!expressions.Any())
                {
                    throw new ArgumentException("No expressions were provided");
                }
                var firstExpression = expressions.First();
                if (expressions.Count == 1)
                {
                    return firstExpression;
                }
                var otherExpressions = expressions.Skip(1);
                var firstParameter = firstExpression.Parameters.Single();
                var otherExpressionsWithParameterReplaced = otherExpressions.Select(e => ReplaceParameter(e.Body, e.Parameters.Single(), firstParameter));
                var bodies = new[] { firstExpression.Body }.Concat(otherExpressionsWithParameterReplaced);
                var joinedBodies = bodies.Aggregate(joiner);
                return Expression.Lambda<Func<TEntity, TReturn>>(joinedBodies, firstParameter);
            }
            //================================================
            private Expression<Func<T, bool>> GetExpression() => Join(Expression.Or, predicates);
            #endregion

            #region public methods
            public IQueryOr<T> WhereOr(Expression<Func<T, bool>> predicate)
            {
                return new OrPredicate<T>(sourceQueryable, predicates.Append(predicate));
            }
            public IQueryable<T> AsQueryable()
            {
                if (predicates.Count > 0)
                    return sourceQueryable.Where(GetExpression());
                else // If not any predicates exists, returns orignal query
                    return sourceQueryable;
            }
            #endregion
        }

        /// <summary>
        /// Convert <see cref="IQueryable{T}"/> to <see cref="IQueryOr{T}"/> to make next condition append as or predicate.
        /// Call <see cref="IQueryOr{T}.AsQueryable"/> back to <see cref="IQueryable{T}"/> linq.
        /// </summary>
        /// <typeparam name="TSource"></typeparam>
        /// <param name="source"></param>
        /// <returns></returns>
        public static IQueryOr<TSource> AsWhereOr<TSource>(this IQueryable<TSource> source)
        {
            return new OrPredicateBuilder<TSource>(source);
        }
    }
}

如何使用它：

// IQueryable<ClassA> myQuery = ....;
  
var queryOr = myQuery.AsWhereOr();
// for a condition list ...
// queryOr = queryOr.WhereOr(a => /*some condition*/)

myQuery = queryOr.AsQueryable();

享受！

- Mr. Squirrel.Downy

1

有趣的方法。让 WhereOr 改变状态并返回相同的对象是一种反模式，特别是在 LINQ 语法中。考虑遵循 OrderBy().ThenBy() 使用的模式，其中返回的接口扩展 IQueryable，并且每个返回的对象都是不可变的查询。 - StriplingWarrior

1

同时，请仔细考虑当 WhereOr() 从未被调用时，用户会期望什么。他们应该得到一个异常吗？还是原始查询而没有任何过滤器应用？ - StriplingWarrior

@StriplingWarrior 我修复了一些问题：现在返回新对象而不是相同的对象；当用户从未调用 WhereOr 时抛出异常，现在将返回原始查询。 - Mr. Squirrel.Downy

1

你仍然在更改原始对象的 predicates。因此，即使您不进行赋值（queryOr = ...），调用 queryOr.WhereOr（...） 也会更改 queryOr 对象。考虑使用不可变的谓词集合而不是列表？ - StriplingWarrior

@StriplingWarrior 哎呀，我的错 - Mr. Squirrel.Downy

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Steven V · Accepted Answer

您可能需要类似Predicate Builder这样的工具，它可以更轻松地控制where语句中的AND和OR关系。

还有Dynamic Linq，它允许您像SQL字符串一样提交WHERE子句，并将其解析为适当的WHERE谓词。