EF中查询包含全部指定ID关联实体的Foo的实现问题

场景与需求 #

实体定义如下，Foo与Bar为多对多关联：

public class Foo
{
    public ICollection<Bar> Bars { get; set; }
}

现有一组选中的Bar ID数组：

var ids = new[] { 1, 2, 3 }; // selected Bar ids

需要查询所有关联的Bars包含全部指定ID的Foo记录。

尝试的写法及问题 #

1. 使用All嵌套Select.Contains

var res1 = cx.Foos.Where(f => ids.All(id => f.Bars.Select(b => b.Id).Contains(id)));

非EF环境下有效，但EF无法翻译该LINQ表达式，报错：The LINQ expression ... could not be translated. Either rewrite the query in a form that can be translated

2. 使用Intersect匹配计数

var count = ids.Count();    
var res = cx.Foos
         .Where(f => f.Bars.Select(b => b.Id).Intersect(ids).Count() == count)
         .ToArray();

非EF环境下有效，但EF抛出空参数异常：Unhandled exception. System.ArgumentNullException: Value cannot be null. (Parameter 'parameter')

解决方法 #

方法1：用All结合Any（推荐） #

利用EF支持的导航属性Any操作，替换嵌套的Select.Contains，EF可正常翻译为SQL的EXISTS子查询：

var res = cx.Foos
    .Where(f => ids.All(id => f.Bars.Any(b => b.Id == id)))
    .ToArray();

若需处理ids为空的场景（比如返回所有Foo），可先做判断：

var res = ids.Length == 0 
    ? cx.Foos.ToArray()
    : cx.Foos.Where(f => ids.All(id => f.Bars.Any(b => b.Id == id))).ToArray();

方法2：统计匹配的Bar ID数量 #

通过统计Foo关联的Bars中包含目标ID的数量，与目标ID的去重计数对比，EF可正常翻译：

var targetCount = ids.Distinct().Count();
if (targetCount == 0)
{
    return cx.Foos.ToArray();
}

var res = cx.Foos
    .Where(f => f.Bars.Count(b => ids.Contains(b.Id)) == targetCount)
    .ToArray();

对ids做去重处理，避免重复ID导致计数判断错误。

失效原因说明 #

• 第一种写法中，f.Bars.Select(b => b.Id).Contains(id)属于嵌套集合投影操作，EF无法将其转换为对应SQL逻辑；
• 第二种写法的Intersect在EF部分版本存在翻译bug，且未处理ids为空的情况，触发空参数异常。

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