How to apply thenInclude condition on above relations on .net core ef?

Question

I have some category tables with one-many relations. Lets call the classes A,B, and C, and assume that A can contain many B, B can be related to many C.

and imagine this is the result of

select A.id, B.id, C.id, C.type 
from A 
left join B on B.a_id=A.id 
left join C on B.id=C.id

A.id	B.id	C.id	C.type
1	1	1	f
1	1	2	f
1	1	3	g
1	2	4	g
1	2	5	g
2	3	6	h

On ef core, If I execute this Linq Lambda

 var items = context.As.AsNoTracking()    // As, Bs, Cs, are the DbContext names and the ICollection's name on the Model definition
               .Include(i => i.Bs)
                 .ThenInclude(j => j.C.Where(k=>k.type=="f"));
return await Task.FromResult(items);

I get B{id=2} with empty ICollection Cs.

I was expecting those not to appear, as would happen on an inner join.

is there a way to achieve this?

Thank you in advance.

A visual representation would be the following. I'm getting

As (result set)
|-{ }
|  |-A
|  | |-id=1
|  | |-Bs={ }
|  | |     |-B
|  | |     | |-id=1
|  | |     | |-Cs={ }
|  | |     | |     |-C
|  | |     | |     | |-id=1
|  | |     | |     | |-type="f"
|  | |     | |     |
|  | |     | |     |-C
|  | |     | |     | |-id=2
|  | |     | |     | |-type="f"
|  | |     | |
|  | |     |-B
|  | |     | |-id=2
|  | |     | |-Cs={ }
|  |-A
|  | |-id=2
|  | |-Bs={ }
|  | |     |-B
|  | |     | |-id=3
|  | |     | |-Cs={ }

when I just want

As (result set)
|-{ }
|  |-A
|  | |-id=1
|  | |-Bs={ }
|  | |     |-B
|  | |     | |-id=1
|  | |     | |-Cs={ }
|  | |     | |     |-C
|  | |     | |     | |-id=1
|  | |     | |     | |-type="f"
|  | |     | |     |
|  | |     | |     |-C
|  | |     | |     | |-id=2
|  | |     | |     | |-type="f"

You need to add corresponding filter in `.Include(i => i.Bs)`. — Guru Stron, Aug 03 '21 at 19:40
Yo could also use a join, if you are familiar with sql syntax: var result=(from a in As join b in Bs on a.id equals b.a_id join c in Cs on b.id equals c.b_id where c.type=="f" select new { a_id=a.id b_id=b.id, c_id=c.id }) — hfpt, Aug 03 '21 at 19:40
@GuruStron but how? Bs doesn't have access to C properties, it has only access to Cs, wich is an ICollection — Pablo Fuenzalida, Aug 03 '21 at 19:50
@PabloFuenzalida what about `A` with `id == 2`? Should it be present in the result? — Guru Stron, Aug 03 '21 at 20:05

score 2 · Accepted Answer · answered Aug 03 '21 at 20:40

A filtered Include only works on the collection itself. A subsequent ThenInclude doesn't filter the preceding Include. This is what I mean:

context.As
    .Include(a => a.Bs) // Fully populates A.Bs
    .ThenInclude(b => b.Cs.Where(c => c.type == "f")); // Partly populates B.Cs

You expected the filter on Cs to filter Bs as well, but Bs are only affected by a filtered Include on Bs, which in your case would amount to:

context.As
    .Include(a => a.Bs.Where(b => b.Cs.Any(c => c.type == "f"))
    .ThenInclude(b => b.Cs.Where(c => c.type == "f"))

More details on scope and effect of filtered `Include`s [here](https://stackoverflow.com/a/61147681/861716). — Gert Arnold, Aug 03 '21 at 20:42

How to apply thenInclude condition on above relations on .net core ef?

1 Answers1