Search code examples
entity-framework-core

ThenInclude with conditions based on Parent entity on EF Core 8

Let's see these entities (tables in SQL Server)

  • Project (has many)
  • Milestone (has many)
  • Task (has many)
  • SubTask

Project entity has a Type attribute. Type can be A, B.

Task entity has a Gender attribute. Gender can be X, Y, One, Two.

Type attribute in Project should allow only these combinations:

  • Projects marked with Type A only allows Tasks with Gender X or Y.
  • Projects marked with Type B only allows Tasks with Gender One or Two.

Well, our solution allowed different all kind of combinations until now. So I am working on EF Core 8 to apply that kind of filtering and ignore the old values.

I am using this query to get all projects with milestones, tasks and subtasks.

query
    .Include(p => p.Milestones)
        .ThenInclude(pc => pc.Tasks)
        .ThenInclude(pa => pa.SubTasks)
    .AsSplitQuery();

but how can I do to get all projects A with only task with Gender X and Y and projects B with only One and Two?

I have tried to use If from this post : EF Core linq and conditional include and theninclude problem

Also, I tried using EF Plus with IncludeIf.

Solution

  • As you can read here, under "The filter expression", the expression in (Then)Include must be "stand-alone", i.e. it can't use range variables that are used before the includes.

    In your case it should be something like this:

    query
    .Include(p => p.Milestones)
        .ThenInclude(pc => pc.Tasks
            .Where(t => 
                (t.Milestone.Project.Type == "A"
                    && (t.Gender == "X" || t.Gender == "Y"))
             || (t.Milestone.Project.Type == "B"
                    && (t.Gender == "One" || t.Gender == "Two"))))
        .ThenInclude(pa => pa.SubTasks)
    .AsSplitQuery();

    This requires back-references from Task to Milestone to Project. It's not elegant, but necessary because you can't use p.Type in the predicate.