EF Core 7中使用.All()匹配List<string>时LINQ表达式无法翻译求重写方案

问题描述 #

我尝试在数据库端执行以下LINQ查询时失败：

public async Task<List<MatchEntity>> GetMatchesAsync(List<string> teammatesChampionNames, List<string> enemiesChampionNames)
{
    return await _context.Matches.Include(x => x.Teams).ThenInclude(x => x.Participants).AsSplitQuery()
        .Where(x =>
            teammatesChampionNames.All(y => x.Teams.Any(z => z.Participants.Any(i => i.ChampionName == y)))
            && enemiesChampionNames.All(y => x.Teams.Any(z => z.Participants.Any(i => i.ChampionName == y)))).ToListAsync();
}

我当前使用EF Core 7和SQL Server，请问是否可以重写该查询？

重写方案 #

原查询的问题在于多层嵌套的All+Any逻辑，EF Core 7对这种复杂转换支持有限，导致无法生成有效SQL或执行报错。以下是几种可行的重写方式：

方案一：子查询统计匹配英雄数量

将“匹配所有目标英雄”的逻辑转换为统计比赛中包含的目标英雄唯一数量等于输入列表数量，EF Core能很好地将其转换为SQL：

public async Task<List<MatchEntity>> GetMatchesAsync(List<string> teammatesChampionNames, List<string> enemiesChampionNames)
{
    var teammateUniqueCount = teammatesChampionNames.Distinct().Count();
    var enemyUniqueCount = enemiesChampionNames.Distinct().Count();

    return await _context.Matches
        .Include(x => x.Teams).ThenInclude(x => x.Participants)
        .AsSplitQuery()
        .Where(m => 
            // 验证所有队友英雄都在比赛中出现
            _context.Participants
                .Where(p => p.Team.MatchId == m.Id && teammatesChampionNames.Contains(p.ChampionName))
                .Select(p => p.ChampionName)
                .Distinct()
                .Count() == teammateUniqueCount
            &&
            // 验证所有敌人英雄都在比赛中出现
            _context.Participants
                .Where(p => p.Team.MatchId == m.Id && enemiesChampionNames.Contains(p.ChampionName))
                .Select(p => p.ChampionName)
                .Distinct()
                .Count() == enemyUniqueCount
        )
        .ToListAsync();
}

方案二：分组关联统计

通过分组关联参与者表，直接统计每个比赛的目标英雄匹配情况：

public async Task<List<MatchEntity>> GetMatchesAsync(List<string> teammatesChampionNames, List<string> enemiesChampionNames)
{
    var teammateSet = new HashSet<string>(teammatesChampionNames);
    var enemySet = new HashSet<string>(enemiesChampionNames);

    var query = from match in _context.Matches
                join team in _context.Teams on match.Id equals team.MatchId
                join participant in _context.Participants on team.Id equals participant.TeamId
                group participant by match into matchGroup
                let matchedTeammateChamps = matchGroup.Select(p => p.ChampionName).Where(c => teammateSet.Contains(c)).Distinct()
                let matchedEnemyChamps = matchGroup.Select(p => p.ChampionName).Where(c => enemySet.Contains(c)).Distinct()
                where matchedTeammateChamps.Count() == teammateSet.Count 
                      && matchedEnemyChamps.Count() == enemySet.Count
                select matchGroup.Key;

    return await query
        .Include(m => m.Teams).ThenInclude(t => t.Participants)
        .AsSplitQuery()
        .Distinct()
        .ToListAsync();
}

方案三：结合过滤式Include（同时过滤关联数据）

如果需要同时过滤关联的Teams和Participants只保留目标英雄，可以用Filtered Include配合主查询过滤：

public async Task<List<MatchEntity>> GetMatchesAsync(List<string> teammatesChampionNames, List<string> enemiesChampionNames)
{
    var teammateUniqueCount = teammatesChampionNames.Distinct().Count();
    var enemyUniqueCount = enemiesChampionNames.Distinct().Count();

    return await _context.Matches
        .Include(m => m.Teams.Where(t => t.Participants.Any(p => teammatesChampionNames.Contains(p.ChampionName) || enemiesChampionNames.Contains(p.ChampionName))))
        .ThenInclude(t => t.Participants.Where(p => teammatesChampionNames.Contains(p.ChampionName) || enemiesChampionNames.Contains(p.ChampionName)))
        .AsSplitQuery()
        .Where(m => 
            m.Teams.SelectMany(t => t.Participants).Select(p => p.ChampionName).Where(c => teammatesChampionNames.Contains(c)).Distinct().Count() == teammateUniqueCount
            &&
            m.Teams.SelectMany(t => t.Participants).Select(p => p.ChampionName).Where(c => enemiesChampionNames.Contains(c)).Distinct().Count() == enemyUniqueCount
        )
        .ToListAsync();
}

说明 #

• 所有方案都先对输入的英雄名称列表去重，避免重复统计影响结果准确性。
• 优先选择方案一，其生成的SQL在SQL Server上执行效率更优，逻辑也更直观。

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