How to make this EF Linq method syntax query more readable

Question

Can I somehow make this query more readable? I don't like ternary operators inside the select.

var chats = await context.Chats
        .Where(x => x.Name.Contains(query))
        .Include(x => x.UserChats)
        .Include(x => x.Messages)
            .ThenInclude(x => x.User)
        .Select(x => new ChatResponse(
            x.Id,
            x.Name,
            x.Description,
            x.ChatType,
            x.UserChats.Count,
            x.UserChats.Any(uc => uc.UserId == httpContextProvider.CurrentUserId),
            x.Messages.LastOrDefault() == null ? String.Empty : x.Messages.LastOrDefault()!.Content,
            x.Messages.LastOrDefault() == null ? null : x.Messages.LastOrDefault()!.CreatedAt,
            x.Messages.LastOrDefault() == null ? String.Empty : x.Messages.LastOrDefault()!.User.UserName!
        )).ToListAsync();

Answer 1

I would suggest a double-projection. With projection you do not need eager loading, so no need for Include. Double-projection first uses a pure Linq-to-entity query to get the relevant data then a second projection to flatten to the desired view model.

var chats = await context.Chats
    .Where(x => x.Name.Contains(query))
    .Select(x => new 
    {
        x.Id,
        x.Name,
        x.Description,
        x.ChatType,
        ChatCount = x.UserChats.Count,
        CurrentUserInChat = x.UserChats.Any(uc => uc.UserId == httpContextProvider.CurrentUserId),
        LastMessage = x.Messages
            .OrderByDescending(m => m.CreatedAt)
            .Select(m => new 
            {   
                m.CreatedAt,
                m.Content,
                m.User.UserName
            }).FirstOrDefault()
    }).ToListAsync() // First projection to SQL
    .Select(x => new ChatResponse(
            x.Id,
            x.Name,
            x.Description,
            x.ChatType,
            x.ChatCount,
            x.CurrentUserInChat,
            x.LastMessage?.Content ?? string.Empty,
            x.LastMessage?.CreatedAt,
            x.LastMessage?.UserName ?? string.Empty)
     .ToList(); // Second projection in memory to model.

While it is possible to build a single query for complex queries that flatten information, these can sometimes end up being harder to read and can lead to less efficient queries where tables are joined multiple times for things like multiple FirstOrDefault() sub-queries. The double projection allows it to produce a single structured query (using OrderByDescending and FirstOrDefault rather than LastOrDefault) then a simple second pass to build the desired flattened projection. Some null coalesce operations like ?. don't work in Linq-to-Entity when building SQL, so we leave that for the second projection done in memory.