EF lambda where a collection property intersects an array of ids

Question

having an entity with a many to many Property, and an array of selected ids like this:

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

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

I needed to query Foos where Foo.Bar has all the ids

My attempts:

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

works without EF, with EF I get error:

The LINQ expression ... could not be translated. Either rewrite the query in a form that can be translated

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

also works without EF, but with EF I get error:

Unhandled exception. System.ArgumentNullException: Value cannot be null. (Parameter 'parameter') at System.Linq.Expressions.Expression.Lambda(Expression body, String name, Boolean tailCall, IEnumerable`1 parameters)

Answer 1

Try the following query:

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