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)
CodePudding user response:
Try the following query:
var count = ids.Count();
var res = cx.Foos
.Where(f => f.Bars.Count(b => ids.Contains(b.Id)) == count)
.ToArray();