我正在创建一个搜索数据库的应用程序，并允许用户动态添加任何条件（大约 50 个可能），类似于以下 SO 问题：使用创建动态查询
我正在创建一个搜索数据库的应用程序，并允许用户动态添加任何条件（大约 50 个），就像以下 SO 问题一样： 使用实体框架创建动态查询 。我目前正在进行一个检查每个条件的搜索，如果它不为空，它会将其添加到查询中。
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);
}
根据 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://.com/a/50414456/6859121
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();
享受！