the logic of lazy loading with navigation properties in entity framework

Question

I am having trouble understanding how lazy loading works. For example, in the following example, I can access the Courses of Students within Where() clause:

context.Students
       .Where(st=>st.Courses
                    .Select(c=>c.CourseName).Contains('Math')
             ).ToList();

However, the following will not work and will throw null exception if I do not use Include() although I did not disable Lazy Loading:

context.Students.Single(s => s.StudentId == 1)
       .Courses.ToList()

Can someone explain me why it works this way?

Answer 1

In order for lazy loading to work, two things have to happen:

1. Lazy Loading must be enabled on the context
2. The properties to be lazy-loaded must be virtual.

In your case, you have explained that your property is not virtual, so it can't be lazy loaded. However, lazy loading is only needed if you want to access child entities or collections after loading the base object from the database. That means you are doing two very different things in your examples.

In your first example, you've written an EF query that includes a call to IQueryable.Where, followed by IQueryable.ToList. When this code runs, EF is going to try to translate that Where call into a call to the underlying SQL data store. Within that call, you access a child entity reference on the object being queried, so the EF expression parser sees this reference and knows to translate that into SQL as well. Essentially, you're only asking EF to make one database call, so it works.

(One caveat: even though your first query works, the child collection is not populated when the call to ToList completes; the SQL query still only returns the fields needed to populate the top level object. All that happens is that EF includes the child table in the WHERE clause to filter the result set. If you tried to access the Courses property on any of those returned Student objects, it would still fail.)

In your second example, you're making a call to IQueryable.Single to get a single student, followed by a call to the Courses property getter, followed by a call to IQueryable.ToList. Again, the EF expression parser sees whatever's inside the Single method call and turns that into a SQL query, but your attempt to access the child collection happens outside that call. Here, you're asking EF to do two "queries": one to get the student, and one to get the courses. Since lazy loading is not enabled, the second query never runs, and EF instead returns null immediately. This leads to an attempt to call ToList on a null object, which gives you the expected error.

If you had used Include in the second query, EF would have been forced to generate a different SQL query to satisfy your call to Single, one that included all the information needed to also populate the Courses child collection. In that case, when you attempted to access Courses in the next step, it wouldn't be null, it would already be populated, and the ToList call would work.

To really make sense of the difference, the easiest thing to do is just look at the SQL queries generated in each case; there's a number of ways to do this, an easy one being described here:

How do I view the SQL generated by the Entity Framework?