8wDlpd.png
8wDFp9.png
8wDEOx.png
8wDMfH.png
8wDKte.png

实体框架中使用 OR 条件的动态查询

zest16 2月前

85 0

我正在创建一个搜索数据库的应用程序,并允许用户动态添加任何条件(大约 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);
}
帖子版权声明 1、本帖标题:实体框架中使用 OR 条件的动态查询
    本站网址:http://xjnalaquan.com/
2、本网站的资源部分来源于网络,如有侵权,请联系站长进行删除处理。
3、会员发帖仅代表会员个人观点,并不代表本站赞同其观点和对其真实性负责。
4、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报
5、站长邮箱:yeweds@126.com 除非注明,本帖由zest16在本站《asp.net-mvc》版块原创发布, 转载请注明出处!
最新回复 (0)
  • 根据 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();
    

    享受!

返回
作者最近主题: