How to get an EF query to compile the most optimised SQL?

Question

I'm fairly new to EF, and this is something that has been bugging me for a couple of days now:

I have a User entity. It has a parent WorkSpace, which has a collection of Users. Each User also has a collection of children Schedule, in a User.Schedules property.

I'm navigating through the objects like this:

var query = myUser.WorkSpace.Users.SelectMany(u => u.Schedules); 

When enumerating the results of query (myUser is an instance of User that has been loaded previously using .Find(userid)), I noticed that EF makes one query to the db for each User in WorkSpace.Users

How come EF doesn't get the results in one single query, starting from the primary key of myUser, and joining on this with all the tables involved?

If I do something else directly from the context such as this, it works fine though:

context.Users.Where(u => u.ID = userid).SelectMany(u => u.WorkSpace.Users.SelectMany(u => u.Schedules))

Is there something I'm doing wrong?

Answer 1

Let take the first query:

var query = myUser.WorkSpace.Users.SelectMany(u => u.Schedules);

If you look at the type of the query variable, you'll see that it is IEnumerable<Schedule>, which means this is a regular LINQ to Objects query. Why? Because it starts from materialized object, then accession another object/collection etc. This combined with the EF lazy loading feature is leading to the multiple database query behavior.

If you do the same for the second query:

var query = context.Users.Where(u => u.ID = userid)
    .SelectMany(u => u.WorkSpace.Users.SelectMany(u => u.Schedules))

you'll notice that the type of the query is now IQueryable<Schedule>, which means now you have a LINQ to Entities query. This is because neither context.Users nor other object/collections used inside the query are real objects - they are just metadata used to build, execute and materialize the query.

To recap, you are not doing something wrong. The lazy loading works this way. If you don't care about so called N+1 query issue, you can use the first approach. If you do care, then use the second.