我不明白如何簡化這個請求
var dialogs = await dbContext.UsersDialogs
.AsNoTracking()
.Where(x => x.UserId == userId)
.Select(x => new DialogModel
{
Id = x.DialogId,
Login = x.Dialog.Name,
Image = x.User.FacialImage,
IsConfirm = x.Dialog.Messages.OrderBy(x => x.DateCreate).LastOrDefault().IsRead,
DateTime = x.Dialog.Messages.OrderBy(x => x.DateCreate).LastOrDefault().DateCreate,
LastMessage = x.Dialog.Messages.OrderBy(x => x.DateCreate).LastOrDefault().Content,
LastUserId = x.Dialog.Messages.OrderBy(x => x.DateCreate).LastOrDefault().UserId
})
.ToListAsync();
它會將其轉換為這樣的請求
SELECT [u].[DialogId] AS [Id], [d].[Name] AS [Login], [u0].[FacialImage] AS [Image], (
SELECT TOP(1) [m].[IsRead]
FROM [Messages] AS [m]
WHERE [d].[Id] = [m].[DialogId]
ORDER BY [m].[DateCreate] DESC) AS [IsConfirm], (
SELECT TOP(1) [m0].[DateCreate]
FROM [Messages] AS [m0]
WHERE [d].[Id] = [m0].[DialogId]
ORDER BY [m0].[DateCreate] DESC) AS [DateTime], (
SELECT TOP(1) [m1].[Content]
FROM [Messages] AS [m1]
WHERE [d].[Id] = [m1].[DialogId]
ORDER BY [m1].[DateCreate] DESC) AS [LastMessage], (
SELECT TOP(1) [m2].[UserId]
FROM [Messages] AS [m2]
WHERE [d].[Id] = [m2].[DialogId]
ORDER BY [m2].[DateCreate] DESC) AS [LastUserId]
FROM [UsersDialogs] AS [u]
INNER JOIN [Dialogs] AS [d] ON [u].[DialogId] = [d].[Id]
INNER JOIN [Users] AS [u0] ON [u].[UserId] = [u0].[Id]
WHERE [u].[UserId] = @__userId_0
我以某種方式優化它？我不想使用 SQL 查詢，因為 Linq 對我來說似乎更方便。
uj5u.com熱心網友回復：
您已經創建了直接轉換為相同 SQL 的查詢。您可以通過附加省略重復查詢Select。也AsNoTracking沒有必要- EF核心不粘性自定義物體。
var dialogs = await dbContext.UsersDialogs
.Where(x => x.UserId == userId)
.Select(x => new
{
UserDialog = x,
LastMessage = x.Dialog.Messages.OrderByDescending(x => x.DateCreate).FirstOrDefault()
})
.Select(x => new DialogModel
{
Id = x.UserDialog.DialogId,
Login = x.UserDialog.Dialog.Name,
Image = x.UserDialog.User.FacialImage,
IsConfirm = x.LastMessage.IsRead,
DateTime = x.LastMessage.DateCreate,
LastMessage = x.LastMessage.Content,
LastUserId = x.LastMessage.UserId
})
.ToListAsync();
但是上述查詢的質量取決于當前 EF Core LINQ 轉換器的質量。因此，添加了另一個變體：
var query =
from ud in dbContext.UsersDialogs
from lm in ud.Dialog.Messages.OrderByDescending(x => x.DateCreate)
.Take(1).DefaultIfEmpty()
select new DialogModel
{
Id = ud.DialogId,
Login = ud.Dialog.Name,
Image = ud.User.FacialImage,
IsConfirm = lm.IsRead,
DateTime = lm.DateCreate,
LastMessage = lm.Content,
LastUserId = lm.UserId
};
var dialogs = await query.ToListAsync();
uj5u.com熱心網友回復：
就 LINQ 查詢而言，您可以使用查詢語法和let：
from d in dbContext.UsersDialogs
where d.UserId == userId
let lastMessage = d.Dialog.Messages.OrderBy(d => d.DateCreate).LastOrDefault()
select new DialogModel
{
Id = d.DialogId,
Login = d.Dialog.Name,
Image = d.User.FacialImage,
IsConfirm = lastMessage.IsRead,
DateTime = lastMessage.DateCreate,
LastMessage = lastMessage.Content,
LastUserId = lastMessage.UserId
}
但這并沒有優化 SQL 查詢。EF 為 Messages 表中的每個欄位一遍又一遍地生成相同的子查詢。在 SQL Server 中，查詢計劃不會將這些子查詢優化到一個分支中。
如果你真的想優化 SQL 查詢，你必須做這樣的事情：
(
from d in dbContext.UsersDialogs
where d.UserId == userId
select new
{
Id = d.DialogId,
Login = d.Dialog.Name,
Image = d.User.FacialImage,
LastMessage = (from d.Dialog.Messages
orderby d.DateCreate
select new
{
IsConfirm = d.IsRead
d.DateCreate,
d.Content,
d.UserId
}).LastOrDefault()
}
).AsEnumerable()
.Select(x => new DialogModel
{
Id = x.DialogId,
Login = x.Dialog.Name,
Image = x.User.FacialImage,
LastMessage.IsConfirm,
LastMessage.DateCreate,
LastMessage.Content,
LastMessage.UserId
})
通過添加AsEnumerable，強制對查詢的最后一部分進行客戶端評估，EF 生成一個帶有一個子查詢的查詢，該子查詢使用該ROW_NUMBER() OVER函式僅獲取最后一條訊息一次。但當然，這樣做很麻煩。但如果第一個查詢遭受相當大的性能損失，則可能有必要。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/393768.html