有一個類看起來像這樣：
class CustomerPurchase {
int CustomerId;
int PurchaseId;
int Cost;
}
我使用 Code First 方法和 Entity Framework Core 遷移來創建資料庫。
還有一個看起來像這樣的過濾器：
class Filter {
int CustomerId;
int PurchaseId;
}
現在，我正在嘗試按 id 對過濾資料。
這是我的代碼：
var filters = new List<Filter>();
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.
那么，我該如何讓它作業呢？這個特定的表包含幾百萬條記錄，所以我無法在客戶端過濾它。
我不受物體框架的束縛：如果有的話，請隨意提出不同的方法。出于顯而易見的原因，我不會生成原始查詢，例如WHERE (CustomerId = {1} AND PurchaseId = {2}) OR .... 除此之外的任何事情都是受歡迎的。
uj5u.com熱心網友回復：
我之前遇到過這個問題，在這里我是如何解決的 在這種情況下你有兩個選擇
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- 使用包含
var resq = await dbcontext.CustomerPurchases
.Where(p=> filters.Contains(new Filter { CustomerId = p.CustomerId,PurchaseId = p.PurchaseId }))
.ToListAsync();
但是如果你運行這個，除非你實施，否則你不會得到任何結果IEquatable
所以你的Filter課看起來像這樣
public class Filter : IEquatable<Filter>
{
public int CustomerId;
public int PurchaseId;
public bool Equals(Filter? other)
{
return this.PurchaseId == other.PurchaseId &&
this.CustomerId == other.CustomerId;
}
}
這是兩種方式的完整代碼
var options = new DbContextOptionsBuilder<ApplicationDBContext>()
.UseInMemoryDatabase("test")
.Options;
var dbcontext = new ApplicationDBContext(options);
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1,PurchaseId = 1,Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1, PurchaseId = 2, Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1, PurchaseId = 3, Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 2, PurchaseId = 2, Cost = 10 });
await dbcontext.SaveChangesAsync();
var filters = new List<Filter>();
filters.Add(new Filter { CustomerId = 1, PurchaseId = 2 });
filters.Add(new Filter { CustomerId = 2, PurchaseId = 2 });
var resq = await dbcontext.CustomerPurchases
.Where(p=> filters.Contains(new Filter { CustomerId = p.CustomerId,PurchaseId = p.PurchaseId }))
.ToListAsync();
foreach (var item in resq)
{
Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}
var res1 = dbcontext.CustomerPurchases
.Where(p => filters.Select(c=>c.PurchaseId).Contains(p.PurchaseId))
.Where(p => filters.Select(c => c.CustomerId).Contains(p.CustomerId));
var res = await res1.ToListAsync();
Console.WriteLine("===========================================================");
foreach (var item in res)
{
Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}
和運行代碼
更新 更改為 SQL 服務器后，我仍然收到錯誤，因此在 SQL 服務器上運行時選項 2 不是一個選項
但是我找到了另一個解決方案，我可以根據我找到的這個PredicateBuilder的過濾器串列構建 where 子句 ，這里是使用謂詞構建器的代碼
var whereclause = PredicateBuilder.False<CustomerPurchase>();
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))
Here is the full class of PredicateBuyilder
public static class PredicateBuilder
{
public static Expression<Func<T, bool>> True<T>() { return f => true; }
public static Expression<Func<T, bool>> False<T>() { return f => false; }
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
}
}
I hope that answer your question !!
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/450030.html
上一篇：編譯器流水線如何對應增量源生成？