我如何建立一个LINQ谓词/动态.当网格属性不具有实体属性时,基于网格过滤的LINQ查询
本文关键字:属性 网格 LINQ 实体 查询 过滤 于网格 建立 何建立 动态 谓词 | 更新日期: 2023-09-27 18:11:50
我有一个网格传递给我过滤器。比如我有一个对象,比如
var filter = new Filter(){
Member = "Titles",
Operator = Filter.Operators.IsEqualTo,
Value = "Developer"
};
然后我需要扩展一个IQueryable,为此,我使用dynamic。LINQ和有一个方法来应用这些过滤器:
private IQueryable<TReportClass> ApplyFilter(ReportFilter filter, IQueryable<TReportClass> baseQuery)
{
switch (filter.Operator)
{
case ReportFilter.Operators.Contains:
baseQuery = baseQuery.Where(string.Format("{0}.Contains(@0)", filter.Member), filter.Value);
break;
case ReportFilter.Operators.DoesNotContain:
baseQuery = baseQuery.Where(string.Format("!{0}.Contains(@0)", filter.Member), filter.Value);
break;
case ReportFilter.Operators.IsEqualTo:
baseQuery = baseQuery.Where(string.Format("{0} = @0", filter.Member), filter.Value);
break;
case ReportFilter.Operators.IsNotEqualTo:
baseQuery = baseQuery.Where(string.Format("{0} != @0", filter.Member), filter.Value);
break;
case ReportFilter.Operators.StartsWith:
baseQuery = baseQuery.Where(string.Format("{0}.StartsWith(@0)", filter.Member), filter.Value);
break;
case ReportFilter.Operators.EndsWith:
baseQuery = baseQuery.Where(string.Format("{0}.EndsWith(@0)", filter.Member), filter.Value);
break;
case ReportFilter.Operators.IsNull:
baseQuery = baseQuery.Where(string.Format("{0} = NULL", filter.Member));
break;
case ReportFilter.Operators.IsNotNull:
baseQuery = baseQuery.Where(string.Format("{0} != NULL", filter.Member));
break;
case ReportFilter.Operators.IsEmpty:
baseQuery = baseQuery.Where(string.Format("string.IsNullOrEmpty({0})", filter.Member));
break;
case ReportFilter.Operators.IsNotEmpty:
baseQuery = baseQuery.Where(string.Format("!string.IsNullOrEmpty({0})", filter.Member));
break;
}
return baseQuery;
}
然而,这只适用于非集合。我如何让它与集合一起工作?如果我有这个模型:
public class UserReport : Entity
{
public string Name { get; set; }
public string Email { get; set; }
public List<string> Titles { get; set; }
}
和这个查询作为基础:
IQueryable<UserReport> baseQuery = MyDbContext.DbSet<User>.Select(user => new UserReport
{
Id = user.Id,
Name = user.FirstName + " " + user.LastName,
Email = user.Email,
Titles = user.Positions.Select(apptment => apptment.Title).ToList()
})
我可以这样调用:
IQueryable<UserReport> filteredQuery = ApplyFilters(filters, baseQuery);
我如何将上面的过滤器转换为LINQ,如:
baseQuery.Where(userReport => userReport.Titles.Any(title => title == "Developer")
动态LINQ可以做到吗?还是需要构建自己的谓词?如果有,我该怎么做?
对于System.Linq.Dynamic
和System.Linq.Expressions
都是可能的。
这是System.Linq.Dynamic
的解决方案,以保持它接近你当前的代码。您只需要确定成员是否为集合,并使用以下模式作为动态标准:
Collection: {PropertyName}.Any(it{condition})
对象: {PropertyName}{condition}
实现可以是这样的(基本上用Func<string, string, string>
替换string.Format
):
private IQueryable<TReportClass> ApplyFilter(ReportFilter filter, IQueryable<TReportClass> baseQuery)
{
var property = typeof(TReportClass).GetProperty(filterMember);
bool isCollection = property.Type != typeof(string) &&
&& typeof(IEnumerable).IsAssignableFrom(property.Type);
Func<string, string, string> condtion;
if (isCollection)
condition = (format, member) => string.Format("{0}.Any({1})", member, string.Format(format, "it"));
else
condition = (format, member) => string.Format(format, member);
switch (filter.Operator)
{
case ReportFilter.Operators.Contains:
baseQuery = baseQuery.Where(condition("{0}.Contains(@0)", filter.Member), filter.Value);
break;
case ReportFilter.Operators.DoesNotContain:
baseQuery = baseQuery.Where(condition("!{0}.Contains(@0)", filter.Member), filter.Value);
break;
case ReportFilter.Operators.IsEqualTo:
baseQuery = baseQuery.Where(condition("{0} = @0", filter.Member), filter.Value);
break;
case ReportFilter.Operators.IsNotEqualTo:
baseQuery = baseQuery.Where(condition("{0} != @0", filter.Member), filter.Value);
break;
case ReportFilter.Operators.StartsWith:
baseQuery = baseQuery.Where(condition("{0}.StartsWith(@0)", filter.Member), filter.Value);
break;
case ReportFilter.Operators.EndsWith:
baseQuery = baseQuery.Where(condition("{0}.EndsWith(@0)", filter.Member), filter.Value);
break;
case ReportFilter.Operators.IsNull:
baseQuery = baseQuery.Where(condition("{0} = NULL", filter.Member));
break;
case ReportFilter.Operators.IsNotNull:
baseQuery = baseQuery.Where(condition("{0} != NULL", filter.Member));
break;
case ReportFilter.Operators.IsEmpty:
baseQuery = baseQuery.Where(condition("string.IsNullOrEmpty({0})", filter.Member));
break;
case ReportFilter.Operators.IsNotEmpty:
baseQuery = baseQuery.Where(condition("!string.IsNullOrEmpty({0})", filter.Member));
break;
}
return baseQuery;
}