Simplify Entity Framework Core query

Question

I do not understand how to simplify this request

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();

It will transform it into a request like this

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

Сan I somehow optimize it? I don’t want to use a SQL query because Linq seems more convenient to me.

Answer 1

You have created query which is translated directly to the same SQL. You can omit repeating queries by additional Select. Also AsNoTracking is not needed - EF Core do not tack custom entities.

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();

But quality of query above depends on quality of current EF Core LINQ translator. So, added another variant:

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();

Answer 2

As far as the LINQ query is concerned, you can simplify it by using query syntax and 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
}

But that doesn't optimize the SQL query. EF generates the same subquery over and over again for each field from the Messages table. In SQL Server, the query plan doesn't optimize these subqueries away into one branch.

If you really want to optimize the SQL query you have to do something like this:

(
    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
})

By adding AsEnumerable, which forces client-side evaluation of the last part of the query, EF generates a query with one subquery that uses the ROW_NUMBER() OVER function to get the last message only once. But of course it's quite a hassle to do this. But it may be necessary if the first query suffer considerable performance hits.