EF Core Intersect between List and Property of child element returns "ArgumentNullException"

user_2078959 0
Viewed 521

I am currently trying to intersect a list of IDs with a child property of my Database Entity.

The intent of doing so is to find all "Form" records that that have all of the list Ids matching at least one entity in the child entity "Location". I believe the SQL equivalent would be doing an IN on a joined table "Locations".

The structure is as such for the element in question: (I have intentionally left out all other properties).

public class Form
{

    [Column("FormId"), DatabaseGenerated(DatabaseGeneratedOption.Identity), Key]
    public Guid Id { get; set; }
    public virtual ICollection<Location> Locations { get; set; }
}

public class Location
{
    [Column("LocationId"), DatabaseGenerated(DatabaseGeneratedOption.Identity), Key]
    public Guid Id { get; set; }
}

I am attempting to do the following:

var locationIdsL = locationIds.Split(',').Select(Guid.Parse).ToList();

return baseQuery.Include(x => x.Locations).Where(x => x.Locations.Select(y => y.Id).Intersect(locationIdsL).Count() == locationIdsL.Count);

* Assume that the locationIdsL variable is a valid list of Guids.

The code above returns the following exception:

System.ArgumentNullException: Value cannot be null. (Parameter 'parameter') at System.Linq.Expressions.Expression.Lambda(Expression body, String name, Boolean tailCall, IEnumerable`1 parameters) at System.Linq.Expressions.Expression.Lambda(Expression body, ParameterExpression[] parameters) at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ReducingExpressionVisitor.Visit(Expression expression) at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes) at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)

However, if I were to manually consume the collection like so:

List<Form> forms = new List<Form>();
foreach (var form in baseQuery.Include(x => x.Locations))
{
    var locations = form.Locations;
    if (locations.Select(x => x.Id).Intersect(locationIdsL).Count() == locationIdsL.Count)
    {
        forms.Add(form);
    }
}

The forms collection gets the item that I expect to see, most notably, with no exception.

For obvious reasons the latter is not an acceptable solution as it would require the loading of the entire dataset.

I am not sure what I am missing here as EFCore should be able to translate the first query.

In all honesty, my approach may be wrong entirely and my I might be "anti-patterning" my query entirely, I am not sure; any suggestions are welcome.

entity-framework-core set-intersection entity-framework lambda c#
1 Answers

I am not sure what I am missing here as EFCore should be able to translate the first query

It probably would, if locationIdsL variable was holding a database query (non in-memory IQueryable<T> type). The problem is that it holds in-memory collection, and the only supported operation on in-memory collections inside LINQ to Entities query so far is the Contains on "primitive" value collection, which translates directly to SQL IN (val1, val2, ..., valN) construct.

Luckily, this is enough for achieving the query in question. Because intersection of setA and setB is nothing more than elements in setA contained in a setB. So Contains based Where operator can be used in place of Intersect, and then simply counting the result (or using the predicate version of Count as shortcut for Where(...).Count()), e.g. replace

x.Locations.Select(y => y.Id).Intersect(locationIdsL).Count() == locationIdsL.Count

with

x.Locations.Count(y => locationIdsL.Contains(y.Id)) == locationIdsL.Count
user_52025630
Related