Entity Framework Core中带OrderByDescending的LINQ导航属性查询无法转SQL

需求 #

在单个查询中获取当前用户的所有聊天，同时返回每个聊天中按CreatedAt降序排列的最后一条消息。

现有代码 #

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

错误信息 #

Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
An unhandled exception has occurred while executing the request.
System.InvalidOperationException: 无法翻译LINQ表达式'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)'。附加信息：无法翻译实体类型'MessageEntity'的成员'CreatedAt'。这种情况通常发生在指定的成员未映射时。请将查询重写为可翻译的形式，或者通过插入对'AsEnumerable'、'AsAsyncEnumerable'、'ToList'或'ToListAsync'的调用显式切换到客户端计算。

实体定义 #

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; }
}

问题原因 #

MessageEntity中的CreatedAt是只读属性（仅包含get访问器），EF Core默认不会将这种属性映射到数据库列，因此LINQ查询在尝试翻译OrderByDescending(m => m.CreatedAt)时失败，因为该属性未与数据库字段关联。

解决方案 #

方案1：给CreatedAt添加私有setter #

修改MessageEntity的CreatedAt属性，添加私有setter，让EF Core能够识别并映射该属性：

public DateTime CreatedAt { get; private set; } = DateTime.Now;

这样EF Core会自动将该属性映射到数据库对应的列，查询就能正常翻译。

方案2：使用Fluent API显式配置映射 #

如果不想修改实体属性的访问器，可以在DbContext的OnModelCreating方法中，通过Fluent API强制配置CreatedAt的映射：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<MessageEntity>()
        .Property(m => m.CreatedAt)
        .HasColumnName("CreatedAt")
        .IsRequired();
}

额外优化：优化查询性能 #

原查询中x.UserChats.Count()会触发额外子查询，建议调整为更高效的写法，比如通过分组统计用户数：

var chats = await (
    from uc in context.UserChats
    where uc.UserId == httpContextProvider.CurrentUserId
    join chat in context.Chats on uc.ChatId equals chat.Id
    join message in context.Messages on chat.Id equals message.ChatId into messagesGroup
    let userCount = context.UserChats.Count(u => u.ChatId == chat.Id)
    from lastMessage in messagesGroup
        .OrderByDescending(m => m.CreatedAt)
        .Take(1)
        .DefaultIfEmpty()
    select new ChatResponse(
        chat.Id,
        chat.Name,
        chat.Description,
        chat.ChatType,
        userCount,
        true,
        lastMessage?.Content,
        lastMessage?.CreatedAt,
        lastMessage?.User.UserName
    )).ToListAsync();

内容的提问来源于stack exchange，提问作者Illusion