我有這個查詢，它使用狀態(tài)為 Completed 的 Childs 填充 CompletedWords 和 CompletedRows ，問題是它也是段表的 2 次而不是 1 次：var query = _context.Submodules.Where(t => t.Id == id) .Select(e => new Submodules{ Id = e.Id, Name = e.Name, Status = e.Status, Token = e.Token, ModuleId = e.ModuleId, Gender = e.Gender, TotalRows = e.TotalRows, TotalWords = e.TotalWords, CompletedWords = e.Segments.Where(a => a.Status == Abr.Recorded).Sum(y=> y.Wordcount), CompletedRows = e.Segments.Where(a => a.Status == Abr.Recorded).Count() }).ToList();轉化為：SELECT t.ID, t.name, t.status, t.token, t.moduleID,t.gender, t.total_rows AS TotalRows, t.total_words AS TotalWords, ( SELECT SUM(a.wordcount) FROM segments AS a WHERE (a.status = 1) AND (t.ID = a.submoduleID)) AS CompletedWords, ( SELECT COUNT(*) FROM segments AS a0 WHERE (a0.status = 1) AND (t.ID = a0.submoduleID)) AS CompletedRowsFROM submodules AS tWHERE t.ID = @__id_0正如您看到的那樣填充 CompletedWords 和 Rows ，它運行 2 個選擇，其中 Status==1 ，只有一個是 Sum ，另一個是 Count() ，我如何將它們合并到 .1 select 中。請指教
1 回答
素胚勾勒不出你
TA貢獻1827條經(jīng)驗 獲得超9個贊
除非您明確看到性能問題，否則我不會太擔心 EF 生成的 SQL。數(shù)據(jù)庫引擎非常擅長自我優(yōu)化。
不過，要回答您的問題，這應該可行：
var query = _context.Submodules.Where(t => t.Id == id)
.Select(e => new {
Id = e.Id,
Name = e.Name,
Status = e.Status,
Token = e.Token,
ModuleId = e.ModuleId,
Gender = e.Gender,
TotalRows = e.TotalRows,
TotalWords = e.TotalWords,
ComletedSegments = e.Segments
.Where(a => a.Status == Abr.Recorded)
.Select(y => new { y.Wordcount })
.ToList()
}).ToList()
.Select(e => new Submodules{
Id = e.Id,
Name = e.Name,
Status = e.Status,
Token = e.Token,
ModuleId = e.ModuleId,
Gender = e.Gender,
TotalRows = e.TotalRows,
TotalWords = e.TotalWords,
CompletedWords = e.Sum(y=> y.Wordcount),
CompletedRows = e.Count()
}).ToList();
第一個查詢選擇匿名類型以從適用的已完成片段中選擇字數(shù)。實現(xiàn)這ToList()一點并執(zhí)行查詢。對于您可以省略的段，.Select()雖然這會將所選數(shù)據(jù)減少到我們關心的列。
第二個.Select()通過對段求和和計數(shù)來填充視圖模型。
- 1 回答
- 0 關注
- 96 瀏覽
添加回答
舉報
0/150
提交
取消