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

i'm new ef, , has been bugging me couple of days now:

i have user entity. has parent workspace, has collection of users. each user has collection of children schedule, in user.schedules property.

i'm navigating through objects this:

var query = myuser.workspace.users.selectmany(u => u.schedules);  

when enumerating results of query (myuser instance of user has been loaded using .find(userid)), noticed ef makes 1 query db each user in workspace.users

how come ef doesn't results in 1 single query, starting primary key of myuser, , joining on tables involved?

if else directly context such this, works fine though:

context.users.where(u => u.id = userid).selectmany(u => u.workspace.users.selectmany(u => u.schedules)) 

is there i'm doing wrong?

let take first query:

var query = myuser.workspace.users.selectmany(u => u.schedules); 

if @ type of query variable, you'll see ienumerable<schedule>, means regular linq objects query. why? because starts materialized object, accession object/collection etc. combined ef lazy loading feature leading multiple database query behavior.

if same second query:

var query = context.users.where(u => u.id = userid)     .selectmany(u => u.workspace.users.selectmany(u => u.schedules)) 

you'll notice type of query iqueryable<schedule>, means have linq entities query. because neither context.users nor other object/collections used inside query real objects - metadata used build, execute , materialize query.

to recap, not doing wrong. lazy loading works way. if don't care called n+1 query issue, can use first approach. if care, use second.