Express Query With Multiple Or And Filter On Joined Table With Entity Framework Core

Here is my model class Parent { int Id; string Name; List Childs; } // name is unique class Child { int Id; int ParentId; string Name;

Solution 1:

As per your comment, the requirement now is: give me all parents, specified by name, and only one specific child per parent, if present. That is: parents having other children will appear in the result, but without children.

That sounds rather trivial, but it isn't. The gotcha is that it requires two filters, one on parents and one on children, in which the child filter is even parent-specific. A SQL query would look like this:

SELECT*FROM parents p1
LEFTJOIN 
(
    SELECT ch.*FROM children ch
    JOIN parents p2 ON ch.parentid = p2.id
    WHERE (p2.name ='parent1'AND ch.name ='child1')
       OR (p2.name ='parent2'AND ch.name ='child2')
       OR (p2.name ='parent3'AND ch.name ='child3')
       OR (p2.name ='parent4'AND ch.name ='child4') -- filter 2
) fc ON fc.parentid = p1.id
WHERE p1.name IN ('parent1','parent2','parent3','parent4') -- filter 1

For an EF LINQ query the parent predicate can be a simple Contains, but you'd want to build the predicate using a predicate builder. Here, for reason following later, I use LINQkit.core.

To be able to build the predicates from one source I use a temporary structure (but I guess you already have something similar):

var filters = new[]
{
    new { ParentName = "parent1", ChildName = "child1" },
    new { ParentName = "parent2", ChildName = "child2" },
    new { ParentName = "parent3", ChildName = "child3" },
    new { ParentName = "parent4", ChildName = "child5" },
};

And prepare the predicates:

using LinqKit;
...
var parentNames = filters.Select(f => f.ParentName).ToList();
var childPredicateStarter = PredicateBuilder.New<Child>();
foreach (var filter in filters)
{
    childPredicateStarter = childPredicateStarter
        .Or(c => c.Parent.Name == filter.ParentName && c.Name == filter.ChildName);
}

Now, ideally, the LINQ query would look like this (db is a context), working around the lack of filtering in Include:

var p = db.Parents
    .Where(p => parentNames.Contains(p.Name))
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.Where(childPredicateStarter)
    })
    .AsEnumerable()
    .Select(p => p.Parent);

But that doesn't run because p.Children is IEnumerable, so childPredicateStarter implicitly converts to a Func instead of the required Expression<Func>>. See here for an in-depth explanation.

The actual working version is:

// Convert to expression:
Expression<Func<Child, bool>> childPredicate = childPredicateStarter;

var p = db.Parents.AsExpandable() // <-- LINQKit's AsExpandable()
    .Where(p => parentNames.Contains(p.Name))
    .Select(p => new
    {
        Parent = p,
        Children = p.Children.Where(c => childPredicate.Invoke(c))
    })
    .AsEnumerable()
    .Select(p => p.Parent);

The AsExpandable call converts the Invoke back into a proper expression tree that EF can translate into SQL.

Solution 2:

Parent.Childs contains all childs of the parent instead of only the wanted one

Filtered include is coming, but not implemented yet. I am a little at a loss on why you think it would actually filter at all, given that your code CLEARLY states to incldue ALL children...

.Include(p => p.Childs)

Means include the children (btw., childs is bad english - the plural is Children). There is no filter there.

Regarding filtered include:

Filtering on Include in EF Core

Quoting the relevant part for here:

"Finally, this feature has been implemented starting with EF Core preview version 5.0.0-preview.3.20181.2 and will be GA in EF Core version 5.0.0"

But even then, you will have to filter (i.e. ptut a where into the include, not just tell it to get all of them).