根据对象列表中的多个参数筛选查询
根据对象列表中的多个参数筛选查询
有一个类看起来像这样:
class CustomerPurchase { int CustomerId; int PurchaseId; int Cost; }
我使用了Code First方法和Entity Framework Core迁移来创建数据库。
还有一个过滤器看起来像这样:
class Filter { int CustomerId; int PurchaseId; }
现在,我正在尝试按照id对数据进行过滤。
这是我的代码:
var filters = new List(); ctx.CustomerPurchases .Where(p => filters.Any(f => f.CustomerId == p.CustomerId && f.PurchaseId == p.PurchaseId)) .ToList();
显然,这是不可能的。 filters
是一个对象列表,它不会被转换为SQL查询。
我得到了一个异常:The LINQ expression blah could not be translated.
那么,我该如何使其工作?这个特定的表包含了几百万条记录,所以我无法在客户端进行过滤。
我不局限于使用Entity Framework:如果有其他方法,请随意提出建议。
出于明显的原因,我不会生成原始查询,比如 WHERE (CustomerId = {1} AND PurchaseId = {2}) OR ...
。除此之外的任何方法都可以。
问题原因:根据给定的参数从对象列表中筛选查询的问题。
解决方法:有两种选择:
1. 根据id进行筛选:
var res1 = dbcontext.CustomerPurchases .Where(p => filters.Select(c => c.PurchaseId).Contains(p.PurchaseId)) .Where(p => filters.Select(c => c.CustomerId).Contains(p.CustomerId));
2. 使用contains方法:
var resq = await dbcontext.CustomerPurchases .Where(p => filters.Contains(new Filter { CustomerId = p.CustomerId, PurchaseId = p.PurchaseId })) .ToListAsync();
但是,如果运行第二种方法,除非实现IEquatable接口,否则将不会得到任何结果。因此,Filter类需要如下所示:
public class Filter : IEquatable{ public int CustomerId; public int PurchaseId; public bool Equals(Filter? other) { return this.PurchaseId == other.PurchaseId && this.CustomerId == other.CustomerId; } }
此外,还介绍了使用PredicateBuilder类的解决方案,该类可以根据给定的筛选器列表构建查询语句。完整代码如下所示:
var whereclause = PredicateBuilder.False(); foreach (var filterrow in filters) { whereclause = whereclause.Or(c => c.CustomerId == filterrow.CustomerId && c.PurchaseId == filterrow.PurchaseId); } var resqq = dbcontext.CustomerPurchases.Where(whereclause); var resq = await resqq.ToListAsync(); foreach (var item in resq) { Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}"); }
这将构建一个查询,由SQL翻译为以下语句:
DECLARE @__filterrow_CustomerId_0 int = 1; DECLARE @__filterrow_PurchaseId_1 int = 2; DECLARE @__filterrow_CustomerId_2 int = 2; DECLARE @__filterrow_PurchaseId_3 int = 2; SELECT [c].[PurchaseId], [c].[CustomerId] FROM [dbo].[CustomerPurchase] AS [c] WHERE (([c].[CustomerId] = @__filterrow_CustomerId_0) AND ([c].[PurchaseId] = @__filterrow_PurchaseId_1)) OR (([c].[CustomerId] = @__filterrow_CustomerId_2) AND ([c].[PurchaseId] = @__filterrow_PurchaseId_3))
此外,附上了PredicateBuilder类的完整代码:
public static class PredicateBuilder { public static Expression> True () { return f => true; } public static Expression > False () { return f => false; } public static Expression > Or (this Expression > expr1, Expression > expr2) { var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast ()); return Expression.Lambda > (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters); } public static Expression > And (this Expression > expr1, Expression > expr2) { var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast ()); return Expression.Lambda > (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters); } }
最后,更新了使用SQL服务器时的解决方案。使用PredicateBuilder类可以根据给定的筛选器列表构建where子句,并生成相应的SQL查询语句。