Entity Framework Core: LINQ query with OrderByDescending and navigation properties fails to translate to SQL

Question

I am encountering the following error while trying to execute a LINQ query. I need to retrieve all chats for the current user, along with the last message (orderded by CreatedAt) in each chat, in a single query.

public async Task<Response<IEnumerable<ChatResponse>>> GetChats()
{
    return await (
        from uc in context.UserChats
        where uc.UserId == httpContextProvider.CurrentUserId
        join x in context.Chats on uc.ChatId equals x.Id
        from lastMessage in x.Messages
            .OrderByDescending(m => m.CreatedAt)
            .Take(1)
            .DefaultIfEmpty()
        select new ChatResponse(
            x.Id,
            x.Name,
            x.Description,
            x.ChatType,
            x.UserChats.Count(),
            true,
            lastMessage.Content,
            lastMessage.CreatedAt,
            lastMessage.User.UserName
        )).ToListAsync();
}

error:

Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1] An unhandled exception has occurred while executing the request. System.InvalidOperationException: The LINQ expression 'DbSet() .Where(m => EF.Property<Guid?>(StructuralTypeShaperExpression: SnapTalk.Domain.Entities.ChatEntity ValueBufferExpression: ProjectionBindingExpression: Inner IsNullable: False , "Id") != null && object.Equals( objA: (object)EF.Property<Guid?>(StructuralTypeShaperExpression: SnapTalk.Domain.Entities.ChatEntity ValueBufferExpression: ProjectionBindingExpression: Inner IsNullable: False , "Id"), objB: (object)EF.Property<Guid?>(m, "ChatId"))) .OrderByDescending(m => m.CreatedAt)' could not be translated. Additional information: Translation of member 'CreatedAt' on entity type 'MessageEntity' failed. This commonly occurs when the specified member is unmapped. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

edit:

public class MessageEntity
{
    public Guid Id { get; set; }
    public required string Content { get; set; }
    public DateTime CreatedAt { get; } = DateTime.Now;
    public DateTime? UpdatedAt { get; set; }
    public string? AttachmentFileName { get; set; }
    public Guid UserId { get; set; }
    public Guid ChatId { get; set; }
    public Guid? ReplyToMessageId { get; set; }
    public MessageEntity? ReplyToMessage { get; set; }
    public ICollection<MessageEntity> Replies { get; set; } = new List<MessageEntity>();
    public UserEntity User { get; set; }
    public ChatEntity Chat { get; set; }
}

Answer 1

Nothing fundamentally wrong here. Here's a sample to work from:

using Microsoft.EntityFrameworkCore;

using (var db = new Db())
{
    db.Database.EnsureDeleted();
    db.Database.EnsureCreated();

    var q =
        from uc in db.UserChats
        where uc.UserId == 1
        join x in db.Chats on uc.ChatId equals x.Id
        from lastMessage in x.Messages
            .OrderByDescending(m => m.CreatedAt)
            .Take(1)
            .DefaultIfEmpty()
        select new
        {
            x.Id,
            lastMessage.CreatedAt,
            lastMessage.User.UserName
        };

    var col = q.ToList();
}

class Db : DbContext
{
    public DbSet<UserChat> UserChats { get; set; }
    public DbSet<User> Users{ get; set; }
    public DbSet<Chat> Chats { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=.;Database=EfTest;Trusted_Connection=True;TrustServerCertificate=true")
                      .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information);
    }

}

public class Message
{
    public int Id { get; set; }
    public DateTime CreatedAt { get; set; }
    public  User User{ get; set; }
    public Chat Chat { get; set; }

}

public class UserChat
{
    public int Id { get; set; }
    public Chat  Chat { get; set; }
    public User User{ get; set; }

    public int ChatId { get; set; }
    public int UserId { get; set; }
}

public class Chat
{
    public int Id { get; set; }
    public List<Message> Messages { get; set; }
}

public class User
{
    public int Id { get; set; }
    public string UserName { get; set; }
}