How does Linq Where(p => p.Parent == null) work in self-referencing table?

Question

Using EF 2.0 Core, code first, I have the following entity which defines a self-referencing table:

class EntityX
{
    public int EntityXId { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
    //navigation properties
    public EntityX Parent { get; set; }
    public ICollection<EntityX> Children  { get; set; }
}

I want to retrieve all EntityX objects and their children in the form of a 'tree'

I can do that using:

var entities = context.EntityX
    .Include(p => p.Parent)
    .Include(p => p.Children)
    .Where(p => p.Parent == null);

When I call entities.ToList() this gets me what I want: a list of parent entities with their children edit only 'first' generation children. When I omit the Where() clause, I get all entities and their children.

I do not understand why the Where() clause works. Objects that are part of the Children collection have a Parent. Why are they not omitted?

Edit: my question was answered but please be aware that I was wrong in my perception of how Include() works.

The `Where` clause only affects the root element. As for the children being loaded recursively, that's probably not the case and is instead lazy loading everything after the initially included children. — DavidG, Apr 18 '18 at 01:12

score 3 · Accepted Answer · answered Apr 18 '18 at 01:16

LINQ applies Where condition only to the objects in the collection being queried. Whatever else you choose to load with the Include method is not subject to the same condition. In fact, EF provides no direct way of restricting the associated entities (see this Q&A).

That is how the top-level query brings you what you expected. EF retrieves the children recursively via a separate RDBMS query using the parent ID to get all its children. The restriction from the Where method does not make it to the child query.

How does Linq Where(p => p.Parent == null) work in self-referencing table?

1 Answers1