我有一個通過 EF Core 映射的關系資料庫，其中包含一個自定義的多對多表，該表在映射旁邊保存一個排序順序。
剝離示例類：
class A
{
int Id;
IEnumerable<AB> Bs;
DateTime Created;
}
class B
{
int Id;
IEnumerable<AB> As
}
class AB
{
int AId;
A A;
int BId;
B B;
int Ordering;
}
我想獲得一個 As 串列（通過一些為簡潔而省略的標準進行過濾），然后按創建時間或連接表中指定的順序進行排序。當通過指定的 Ordering 屬性進行排序時，結果將被過濾為僅鏈接到一個特定 B 的 As （盡管我認為這不會有任何區別）。
所以我有類似的東西
_dbContext.As.Include(a => a.Bs)
.ThenInclude(ab => ab.B)
.Where( a => a.Bs.Any(b => b.Id == 1))
.OrderBy(a => a.Created)
.ToList()
它回傳所有鏈接到 B 且 ID 為 1 的 As，然后按其創建日期排序。
現在我想對鏈接到 B1 的所有 As 運行相同的查詢，但按鏈接表中的 Ordering 排序。
在 SQL 中這很簡單，因為我知道我已經加入了鏈接表并且可以按該列排序，但是我找不到將其指定給物體框架的方法。
I can pass the filtered B Id to the clause, but that constructs a SQL subquery for every single row, which I want to avoid for performance reasons
.OrderBy(a => a.Bs.First(ab => ab.BId == 1).Ordering)
I've tried passing an anonymous type to the OrderBy clause in the hope that EF would interpret Ordering as the joined Ordering column, but it didn't.
.OrderBy(a => new {Ordering = 0}.Ordering)
I've tried starting from the join table and working outwards instead, which then causes issues with duplication when one A is linked to multiple Bs, and using Distinct then removes the previously applied ordering.
_dbContext.ABs
.Include(ab => ab.A)
.Include(ab => ab.B)
.OrderBy(ab => ab.Ordering)
.Select(ab => ab.A)
.Distinct()
I've tried joining the A and AB tables into an anonymous type so that I have access to the ordering property from the join table, but that didn't work either, same issue with deduplication.
_dbContext.As.Join( _dbContext.ABs, a => a.Id, ab => ab.AId, (a, ab) => new {a, ab})
.Where( x => x.ab.BId = 1)
.OrderBy( x => x.ab.Ordering)
.Select(x => x.A)
.Distinct()
I think the only other option might be to materialise the filtered data into memory and sort on the client. But I want to avoid that when I should just be able to apply ORDER BY directly in the generated SQL.
Is there any way I can tell Entity Framework to order by the Ordering column in the join table without creating subqueries?
uj5u.com熱心網友回復：
試試下面的查詢，希望能理解你的問題。
var query =
from a in _dbContext.As
from ab in _dbContext.ABs
.Where(ab => ab.AId == ab.AId)
.Where(ab => ab.BId == 1)
.Orderby(ab => ab.Ordering)
.Take(1)
orderby ab.Ordering
select a;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/456803.html
標籤：c# entity-framework entity-framework-core .net-6.0
上一篇：在SQLServer的WebAPICore3.1中使用EFCore更新新表
下一篇：VisualStudio2022C#ASP.NETWebforms，在服務器上找不到服務參考文本檔案（HTTP錯誤404.0）