Azure Cosmos DB中EF Core LINQ查询无法翻译问题求助

问题现象 #

在EF Core内存数据库中正常运行的LINQ查询，切换到Azure Cosmos DB时抛出System.InvalidOperationException，错误提示：

The LINQ expression 'DbSet()' could not be translated. 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'.

报错堆栈信息：

fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
  An unhandled exception has occurred while executing the request.
  System.InvalidOperationException: The LINQ expression 'DbSet<Media>()' could not be translated. 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.
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.VisitExtension(Expression extensionExpression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.Visit(Expression expression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.Visit(Expression expression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.Visit(Expression expression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.Visit(Expression expression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
     at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.Visit(Expression expression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
     at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
     at Microsoft.EntityFrameworkCore.Cosmos.Query.Internal.CosmosQueryableMethodTranslatingExpressionVisitor.Visit(Expression expression)
     at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
     at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
     at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
     at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
     at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
     at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
     at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
     at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

涉及代码 #

查询代码 #

var messages =await _context.Messages
    .Where(x => x.RoomChatId == roomId)
    .OrderByDescending(x => x.CreatedDate)
    .Select(message =>
        new MessageResponse
        {
            id = message.id,
            CreatedDate = message.CreatedDate,
            MediaList = message.MediaList!.Select(x => new MediaResponse
            {
                Id = x.Id,
                Type = (MediaTypeResponse)x.Type,
                BlurHash = x.BlurHash,
                Path = x.Path
            }).ToList(),

            Text = message.Text,
            GroupInviteToken = message.GroupInvitationToken,
            ModifiedDate = message.ModifiedDate,
            ReplyMessageId = message.ReplyMessageId,
            UserSender = _context.Users.Where(x => x.id == message.UserSenderId).Select(x => new PreviewUserResponse()
            {
                Id = x.id,
                Name = x.FullName,
                ProfileImage = FileStorageHelper.GetUrl(x.ProfileImage)
            }).Single()
        }).ToListAsync();

Message实体定义（Media为内嵌实体） #

public class Message : BaseEntity
{
    public Guid RoomChatId { get; set; }

    public string? Text { get; set; }

    public Guid UserSenderId { get; set; }

    public string? GroupInvitationToken { get; set; }

    public Guid? ReplyMessageId { get; set; }

    public ICollection<Media>? MediaList { get; set; }
}

问题原因 #

Azure Cosmos DB作为非关系型数据库，其EF Core Provider的查询翻译能力和内存数据库/关系型数据库有显著差异，导致报错的核心原因包括：

1. 跨容器查询不支持嵌套投影：在Select中直接使用_context.Users.Where(...)属于跨容器的嵌套查询，EF Core Cosmos无法将其翻译为有效的Cosmos SQL语句。
2. 客户端方法无法被翻译：FileStorageHelper.GetUrl是自定义客户端方法，无法转换为Cosmos SQL的内置函数，会导致查询翻译失败。
3. 内嵌集合的ToList()操作：在投影中对MediaList.Select(...).ToList()调用ToList()，EF Core Cosmos无法将该操作翻译为Cosmos SQL的数组处理逻辑。

解决办法 #

1. 拆分查询，客户端处理跨容器关联 #

Cosmos DB不支持类似关系型数据库的JOIN操作，需先查询主数据，再批量查询关联数据，最后在客户端组装结果：

// 第一步：查询消息及关联的用户ID
var messageDtos = await _context.Messages
    .Where(x => x.RoomChatId == roomId)
    .OrderByDescending(x => x.CreatedDate)
    .Select(m => new 
    {
        Message = m,
        UserSenderId = m.UserSenderId
    })
    .ToListAsync();

// 第二步：批量查询所有涉及的用户
var userIds = messageDtos.Select(d => d.UserSenderId).Distinct().ToList();
var users = await _context.Users
    .Where(u => userIds.Contains(u.id))
    .Select(u => new 
    {
        u.id,
        u.FullName,
        u.ProfileImage
    })
    .ToListAsync();
// 转换为字典方便快速查找
var userLookup = users.ToDictionary(u => u.id);

// 第三步：客户端组装最终响应
var messages = messageDtos.Select(d => new MessageResponse
{
    id = d.Message.id,
    CreatedDate = d.Message.CreatedDate,
    // 移除投影中的ToList()，在客户端转换为列表
    MediaList = d.Message.MediaList?.Select(x => new MediaResponse
    {
        Id = x.Id,
        Type = (MediaTypeResponse)x.Type,
        BlurHash = x.BlurHash,
        Path = x.Path
    }).ToList(),
    Text = d.Message.Text,
    GroupInviteToken = d.Message.GroupInvitationToken,
    ModifiedDate = d.Message.ModifiedDate,
    ReplyMessageId = d.Message.ReplyMessageId,
    // 客户端调用自定义方法处理URL
    UserSender = userLookup.TryGetValue(d.UserSenderId, out var user) 
        ? new PreviewUserResponse
        {
            Id = user.id,
            Name = user.FullName,
            ProfileImage = FileStorageHelper.GetUrl(user.ProfileImage)
        } 
        : null
}).ToList();

2. Cosmos DB EF Core使用注意事项 #

• 避免跨容器嵌套查询：所有跨容器关联逻辑需移到客户端处理，可采用批量查询+字典映射的方式提升性能。
• 客户端方法延迟执行：自定义方法、复杂类型转换等操作必须放在客户端（即查询执行后）进行，不能包含在EF Core的查询表达式中。
• 简化内嵌集合操作：对内嵌集合的投影尽量只使用Select，避免在查询阶段调用ToList()、Count()等无法翻译的方法。
• 优先使用Cosmos原生特性：利用内嵌文档减少跨容器查询，设计数据模型时遵循Cosmos DB的非关系型建模原则。

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