如何在LINQ中使用多个where on键并满足特定条件

xam8gpfp  于 2022-12-06  发布在  其他
关注(0)|答案(3)|浏览(175)

我正在处理一个查询,其中我想检索包含多个int?键的数据。我的输出Dto:

public class FilterParamsDto {
    public int? StudentId { get; set; }
    public int? NationalityId { get; set; }
    public int? CountryId { get; set; }
    public int? SchoolCountryId { get; set; }
    public int? SchoolStateId { get; set; }
    public int? SchoolCityId { get; set; }

    .... More keys
}

我使用了以下查询

var value = from y in data
            where dto.CountryId == null
                ? y.CountryId != null
                : y.IntakeId == dto.CountryId && dto.StudentId == null
                    ? y.StudentId != null
                    : y.StudentId == dto.StudentId && dto.SchoolCityId == null
                        ? y.SchoolCityId != null
                        : y.SchoolCityId == dto.SchoolCityId
            select y;

我想实现的目标:
我想做一个方法,如果任何属性有一些值,我想根据该特定属性筛选数据,如果没有任何值,我想根据另一个有一些值的属性筛选数据。如果任何属性有0值,我想跳过筛选,因为如果任何属性有0值,所以数据不会匹配,我不会使用接收任何数据||数据未按所需条件进行筛选。
编辑1有三种可能性:所有属性都有一些值,一些属性关心值,所有属性关心值。所需的逻辑应该像如果第一个在哪里执行,那么另一个在哪里应该在更新的值上执行,等等。

j9per5c4

j9per5c41#

给予一个例子,这样做

.Where(y => (dto.CountryId == null || y.CountryId == dto.CountryId))

根据需要添加任意多个条件。

cbeh67ev

cbeh67ev2#

要按第一个可用的过滤器属性进行过滤,可以写(我用filter替换了dto,用d替换了y,使其更清晰):

var value = from d in data
   where
      filter.CountryId != null && (d.CountryId ?? d.IntakeId) == filter.CountryId ||
      filter.StudentId != null && d.StudentId == filter.StudentId ||
      filter.SchoolCityId != null && d.SchoolCityId == filter.SchoolCityId
   select d;

要按所有可用筛选器进行筛选:

var value = from d in data
   where
     (filter.CountryId == null || (d.CountryId ?? d.IntakeId) == filter.CountryId) &&
     (filter.StudentId == null || d.StudentId == filter.StudentId) &&
     (filter.SchoolCityId == null || d.SchoolCityId == filter.SchoolCityId)
   select d;

测试(d.CountryId ?? d.IntakeId) == filter.CountryId会比较d.CountryId是否不为空值,否则会比较d.IntakeIdfilter.CountryId
根据?? and ??= operators (C# reference)的文档:
如果左操作数不为空,则空合并运算符“”返回左操作数的值;否则,它计算右边的操作数并返回结果。

plicqrtu

plicqrtu3#

如果你有很多类似上面的属性,并且有多个版本的这种过滤操作,我建议你构建一个动态表达式来过滤你的集合。被查询的实体和DTO具有相同的属性名称和相似的类型。

public static void Main()
    {
        var dto = new FilterParamsDto { CountryId = 3, StudentId = 5 };
        var data = new List<Dummy> { new() { CountryId = 3, StudentId = 1 }, new() { CountryId = 4, StudentId = 5 }, new() { CountryId = 1, StudentId = 2 }, new() { CountryId = 3, StudentId = 5 } };
        var expAnd = GenerateFilterExpression<Dummy, FilterParamsDto>(dto);
        var expOr = GenerateFilterExpression<Dummy, FilterParamsDto>(dto, false);

        var filteredWithAnd = data.AsQueryable().Where(expAnd).ToArray();
        var filteredWithOr = data.AsQueryable().Where(expOr).ToArray();
    }

    public static PropertyInfo[] GetNonNullProperties<T>(T item) where T : class
    {
        var properties = typeof(T)
            .GetProperties(BindingFlags.Instance | BindingFlags.Public)
            .Where(r => r.GetValue(item) != null)
            .Select(r => r).ToArray();

        return properties;
    }

    public static Expression<Func<T, bool>> GenerateFilterExpression<T, R>(R dto, bool and = true) where T : class where R : class
    {
        var p = Expression.Parameter(typeof(T), "p");
        var nonnullProps = GetNonNullProperties(dto);
        var constExp = Expression.Constant(dto);

        // here we decide how to join conditions
        Func<Expression, Expression, BinaryExpression> operatorExp = and ? Expression.AndAlso : Expression.OrElse;
        Expression? exp = null;

        var sourceType = typeof(T);

        foreach (var item in nonnullProps)
        {
            var sourceProp = sourceType.GetProperty(item.Name);
            var prop = Expression.Property(p, sourceProp!);
            Expression dtoProp = Expression.Property(constExp, item);

            // we need this trick otherwise we will have runtime error that says you can not have an expression like : int? == int
            if (sourceProp!.PropertyType != item.PropertyType)
            {
                var underlyingType = Nullable.GetUnderlyingType(item.PropertyType);
                dtoProp = Expression.Convert(dtoProp, underlyingType!);
            }

            if (exp == null)
            {
                exp = Expression.Equal(prop, dtoProp);
            }
            else
            {
                exp = operatorExp(exp, Expression.Equal(prop, dtoProp));
            }
        }

        var result = Expression.Lambda<Func<T, bool>>(exp!, p);

        return result;
    }

Fiddle

相关问题