我有一個包含物件串列的 SQL 表，我正在嘗試使用許多不同的條件回傳一個串列。
這是我的物件（與 SQL 表相同）：
class Photo{
public int Id { get; set; }
public string FileName { get; set; }
public DateTime Uploaded { get; set; }
public int? ProjectId { get; set; }
public int GalleryOrder { get; set; }
}
在 SQL 表中，隨著時間的推移上傳了很多照片，這些照片可能帶有ProjectIds標記，也可能不帶有s標記。每張都有許多照片標簽ProjectId。它們是分批上傳的，因此可能會有許多相同的照片，DateTime Uploaded然后用GalleryOrder.
給定一個ProjectIds串列，我試圖根據以下引數為每個專案回傳一張代表性照片：
- 每個 ProjectId 一張照片
- 最近上傳日期
- 最低畫廊訂單
我有一些代碼似乎可以與我的測驗資料庫（大約 20 個條目）一起使用，但它會提取多余的資料并且種類繁多。我不確定如何簡化和優化它。
這是我當前的查詢：
public async Task<List<Photo>> GetOneImageFilesPerProject(List<int> projectIds)
{
using var context = _contextFactory.CreateDbContext();
var results = await context.Photos.Where(x => x.ProjectId != null
&& projectIds.Contains((int)x.ProjectId))
.ToListAsync();
results = results.OrderBy(x => x.ProjectId)
.ThenByDescending(x => x.Uploaded)
.ThenBy(x => x.GalleryOrder)
.GroupBy(x => x.ProjectId)
.Select(x => x.First())
.ToList();
return results;
}
程式編譯與OrderBy，GroupBy和Select呼叫后加入.Where通話，但它似乎在這一點掛，再也不回來了最終名單。這就是為什么它分為兩個處理步驟。
我的另一個想法是從 Db 中獲取串列，然后通過 foreach 回圈構建最終串列。不確定這是否比使用.GroupByand更快.Select。無論哪種方式似乎有點不雅和蠻力。如果有一個直接的 SQL 查詢會是一個更好的解決方案，我愿意接受！
是否有一些更直接的方法可以根據物件串列中的其他標準回傳具有一個唯一專案的串列？
就規模而言，這不會是一個龐大的應用程式，但它會一次尋找約 10-20 個專案 ID，每個專案可能有約 40-50 張照片標記。許多案例會小一點（2-5 張照片），但有些更大（100-200 張照片）。用戶會非常頻繁地運行此功能，例如一天幾次，并且照片串列會頻繁更改。
編輯：使用 .NET 5 和 EF Core 5
uj5u.com熱心網友回復：
試試這個查詢，它應該適用于 EF Core 5
public async Task<List<Photo>> GetOneImageFilesPerProject(List<int> projectIds)
{
using var context = _contextFactory.CreateDbContext();
var photos = context.Photos.Where(x => x.ProjectId != null
&& projectIds.Contains((int)x.ProjectId));
var query =
from dp in photos.Select(x => new { x.ProjectId }).Distinct()
from p in photos.Where(p => p.ProjectId == dp.ProjectId)
.OrderByDescending(p => p.Uploaded)
.ThenBy(p => p.GalleryOrder)
.Take(1)
select p;
var results = await query.ToListAsync();
return results;
}
uj5u.com熱心網友回復：
只要您在 Projects 中有一個包含照片串列的導航屬性，EFC5 就會對此進行翻譯：
var some = new [] {1,2,3}.ToList();
var v = await context.Projects
.Where(p => some.Contains(p.ProjectId))
.Select(p => p.Photos
.OrderByDescending(ph => ph.Uploaded)
.ThenBy(ph => ph.Gallery)
.First()
).ToListAsync();
它轉化為類似的東西：
SELECT y.*
FROM
Projects p
LEFT JOIN
(
SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY ProjectId ORDER BY Uploaded DESC, Gallery) rn
FROM Photos
) x
WHERE rn = 1
) y
WHERE p.ProjectId IN (1,2,3)
這應該是相當高性能的。您還可以將謂詞移到 Select 內部：
var some = new [] {1,2,3}.ToList();
var v = await context.Projects
.Select(p => p.Photos
.Where(p => some.Contains(p.ProjectId))
.OrderByDescending(ph => ph.Uploaded)
.ThenBy(ph => ph.Gallery)
.First()
)
.ToListAsync();
這將生成相同的查詢，但將 IN 移動到內部查詢：
SELECT y.*
FROM
Projects p
LEFT JOIN
(
SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY ProjectId ORDER BY Uploaded DESC, Gallery) rn
FROM Photos
WHERE p.ProjectId IN (1,2,3)
) x
WHERE rn = 1
) y
這并不重要；SQLS 應該同樣執行這些
那么“從專案開始對我們有什么作用？” 你可能會問..
..好吧，它本質上為您提供了“每個專案”的標準：它導致 EF 寫入PARTITION BY ProjectId，這對于獲取每個專案的影像至關重要。如果您直接從context.OrdersEF嘗試，將無法理解“每個專案”部分
您也可以始終 ExecuteRaw 代替：
var ids = new[]{1,2,3};
var idsStr = string.Join(",", ids);
//yes, raw is intended here because of the IN, not interpolated, even though it's an interpstring
context.Photos.FromSqlRaw($@"SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ProjectId ORDER BY Uploaded DESC, Gallery) rn
FROM Photos
WHERE ProjectId IN ({idsStr})
)
WHERE rn = 1").ToList();
或者，如果將值連接到 raw 讓您感到緊張（它應該，盡管使用 int 陣列進行 SQL 注入的空間不大），您可以在 raw 之上撰寫：
var ids = new[]{1,2,3}.ToList();
context.Photos.FromSqlRaw($@"SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ProjectId ORDER BY Uploaded DESC, Gallery) rn
FROM Photos
)
WHERE rn = 1").Where(p => ids.Contains(p.ProjectId));
EF 會將原始資料捆綁為子查詢，但 SQLS 應該能夠將 projectid 謂詞下推到最里面的查詢中，并且基本上與另一個相同地執行這個原始資料
uj5u.com熱心網友回復：
不幸的是，EF Core 還沒有趕上 EF6，它可以設法GroupBy從要從分組結果中提取特定行的運算式構建交叉應用。自 EF Core 3.1 或什至更早的版本以來，已經記錄了對這種支持的請求，而 AFAIK 仍然沒有合并它們。（https://github.com/dotnet/efcore/issues/12088）
典型的解決方法是使用GroupBy運算式來獲取值的唯一表示以重新加入表中。這需要一些假設，因為您有一個日期和一個序列號 (GalleryOrder)，因為我們需要假設 GalleryOrder 總是從 1 開始，并且這些專案不會被洗掉。否則，您可以使用查詢來接近以查找每個適用訂單和日期的所有照片，但最終需要從記憶體中對最低圖庫進行最終選擇：
List<int> projectIds = new[] { 1, 2 }.ToList(); // Just for testing...
var photos = context.Photos
.Where(x => x.ProjectId.HasValue && projectIds.Contains(x.ProjectId.Value))
.GroupBy(x => x.ProjectId)
.Select(g => new
{
ProjectId = g.Key,
Uploaded = g.Max(x => x.Uploaded)
}).Join(context.Photos.Where(x => x.GalleryOrder == 1),
x => x,
x => new { x.ProjectId, x.Uploaded }
, (_, r) => r).ToList();
如果可以在我們無法保證 GalleryOrder 為 1 的情況下洗掉照片，或者我們想切換到最高畫廊順序之類的東西，那么最終選擇將需要在記憶體中完成。（編輯：洗掉第二個選項，直到我有機會測驗更新的分組，因為它沒有解決最新的日期。）
Edit2：好的，我玩了第二個查詢背后的想法。可以通過 Linq 在一次操作中獲取所需的資料，但是根據我們正在討論的資料量和選擇的專案的數量，加載所選專案的所有照片并進行分組/使用可以進行組內選擇的 Linq2Object 進行最小化。（直到 EF Core 6 可能重新啟用此功能）
var photos = context.Photos
.Where(x => x.ProjectId.HasValue && projectIds.Contains(x.ProjectId.Value))
.GroupBy(x => new { x.ProjectId, x.Uploaded })
.Select(g => new
{
ProjectId = g.Key.ProjectId,
Uploaded = g.Key.Uploaded,
GalleryOrder = g.Min(x => x.GalleryOrer)
}).Join(context.Photos, x => x, x => new { x.ProjectId, x.Uploaded, x.GalleryOrder }
, (_, r) => r)
.GroupBy(x => new { x.ProjectId, x.GalleryOrder })
.Select(g => new
{
ProjectId = g.Key.ProjectId,
Uploaded = g.Max(x => x.Uploaded),
GalleryOrder = g.Key.GalleryOrder
}).Join(context.Photos, x => x, x => new { x.ProjectId, x.Uploaded, x.GalleryOrder }
, (_, r) => r)
.ToList();
如果要檢索大量的專案 ID 和/或每個專案的大量照片，則需要考慮一些事情。或者為每個適用的專案加載照片，如果需要，對專案 ID 進行批處理，并使用 Linq2Object 有序分組獲取適當的照片。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/407992.html
標籤：
上一篇：如何將json檔案包含到天藍色