EF Core查询子列表属性时LINQ表达式无法转换问题求助

问题场景 #

使用EF Core搭配CosmosDB，尝试通过嵌套集合Players的Id字段匹配查询GameReadModel数据时，出现LINQ表达式无法翻译的错误。非嵌套属性查询正常，但客户端评估（比如先拉取所有数据再过滤）会导致大量额外数据加载，影响性能。

相关代码 #

查询逻辑（Handler）

public async Task<IReadOnlyCollection<GameResponse>> Handle(GetMyUpcomingGamesQuery request, CancellationToken cancellationToken)
{
    var test = await _repository.GetAsync(p => p.Players.Any(z=>z.Id == request.ProfileId));

    // ...
}

仓储层代码

public async Task<IReadOnlyCollection<GameReadModel>> GetAsync(Expression<Func<GameReadModel, bool>> predicate)
{
    return await _context.GameReadModels.AsNoTracking().Where(predicate).ToListAsync();
}

GameReadModel 定义

namespace Common.Domain.ReadModels
{
    public class GameReadModel 
    {
        public string Id { get; set; }
        public VenueReadModel Venue { get; set; } 
        public string Notes { get; set; }
        public DateTimeOffset Begins { get; set; }
        public DateTimeOffset Ends { get; set; }
        public int NumberOfPlayers { get; set; }
        public decimal MinimumRating { get; set; }
        public decimal MaximumRating { get; set; }
        public bool OpenToLobby { get; set; }
        public string GameType { get; set; }
        public ProfileReadModel Host { get; set; } 
        public IReadOnlyCollection<ProfileReadModel> Players { get; set; } = new List<ProfileReadModel>();  
        public IReadOnlyCollection<ListItemReadModel> Lists { get; set; } = new List<ListItemReadModel>();
        public DateTimeOffset Created { get; set; }
        public DateTimeOffset Modified { get; set; }
    }
}

报错信息

System.InvalidOperationException: The LINQ expression 'DbSet()
.Where(g => EF.Property(g, "Players")
.AsQueryable()
.Any(o => o.Id == __request_ProfileId_0))' 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'.

解决方法 #

1. 改用CosmosDB原生SQL查询（推荐）

直接使用CosmosDB的ARRAY_CONTAINS函数编写原生查询，绕过EF的LINQ翻译限制，确保查询在服务端执行：

// 仓储层新增或修改方法
public async Task<IReadOnlyCollection<GameReadModel>> GetGamesByPlayerId(string profileId)
{
    // ARRAY_CONTAINS第三个参数true表示部分匹配，只检查Id字段
    var cosmosSql = "SELECT * FROM c WHERE ARRAY_CONTAINS(c.Players, {\"Id\": @profileId}, true)";
    return await _context.GameReadModels
        .FromSqlRaw(cosmosSql, new SqlParameter("@profileId", profileId))
        .AsNoTracking()
        .ToListAsync();
}

2. 调整实体集合类型并修改查询

将GameReadModel中的Players类型从IReadOnlyCollection<ProfileReadModel>改为List<ProfileReadModel>，EF Core Cosmos提供器对List<T>的嵌套集合翻译支持更完善：

// 修改GameReadModel的Players属性
public List<ProfileReadModel> Players { get; set; } = new List<ProfileReadModel>();

修改后尝试原Any查询，或者改用Contains写法：

// Handler中调整查询表达式
var test = await _repository.GetAsync(p => p.Players.Select(z => z.Id).Contains(request.ProfileId));

核心原因 #

CosmosDB是文档型数据库，EF Core的LINQ翻译器对IReadOnlyCollection<T>类型的嵌套集合Any操作支持有限，无法正确转换为CosmosDB原生的数组查询语法。改用原生SQL或调整集合类型，能让查询在服务端高效执行，避免客户端加载大量冗余数据。

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