EF Core did join on his own?

Question

I've just witnessed something weird

Let's consider this example

publi class Order
{
    (...)
    public Status Status { get; set; }
    public Owner Owner { get; set; }
}

public class Status
{
    (...)
    public string Name { get; set; }
}

context
.Orders
.Include(x => x.Owner)
.Where(x => x.Owner.Id = 123)
.Where(x => x.Status.Name == "asdf")
.ToList();

I was shocked when this code worked properly - it found only those orders with status name==asdf for Owner with an Id=123 (he had orders of other type also) and I even found that Status is being inner joined

but why? there's no Include

Is it possible or I'll have to try to find bug somewhere else?

Answer 1

Include is an instruction to eager-load related data in the same LINQ statement. It's not a "declaration" of navigation properties you're going to use later for filtering or selecting. I often see this confusion. Let's summarize it:

Include is not necessary for

• Filtering data

  context.Orders
         .Where(x => x.Owner.Id = 123)
         .Where(x => x.Status.Name == "In progress")
  

  ...generates SQL with two JOINs and returns filtered Orders, no Owner or Status included.

  context.Orders
         .Include(o => o.Owner)
         .Where(x => x.Owner.Id = 123)
         .Where(x => x.Status.Name == "In progress")
  

  ...returns filtered Orders with only Owner included.

• Selecting data

  context.Orders
         .Select(x => new
         { 
             x.Number,
             Owner = x.Owner.Name, 
             Status = x.Status.Name
         }
  

  ...again generates SQL with two joins and returns anonymous type objects, no Owner, no Status.

  context.Orders
         .Include(o => o.Owner)
         .Select(x => new
         { 
             x.Number,
             Owner = x.Owner.Name, 
             Status = x.Status.Name
         }
  

  ...returns exactly the same data because the Include is ignored. If there's nothing in the end result that can contain the Included data the Include will be ignored.

Note that Include does have effect in a query like

context.Orders
       .Include(o => o.Owner)
       .Select(x => new
       { 
           Order = x,
           Owner = x.Owner.Name, 
           Status = x.Status.Name
       }

Even though an anonymous type is returned, the Order in it is the container of Owner and Owner is included. This was different in EF 6 (and I guess still in EF 6.3 on .NET Core 3.0), there the latter Include was also ignored.