Eager-loaded related objects which are null are being lazy-loaded

Question

I'm eager-loading (by using .Include()) related entities in order to minimize the number of SQL queries generated for a particularly "massive" datagrid. In the POCOs, the related entities are public virtual navigation properties, meaning that they are otherwise lazy-loaded. I want to keep the default behavior of lazy-loading these related entities, as it suits me best for other parts of the project.

Now, what happens is that after eager-loading the related entities, I can see that there are still a fair amount of queries generated. And this is queries solely for related entities being null (null simply because the sql joins "found" no related data).

This led me to believe that the EF tries to lazy-load those entities (thinking they are not yet loaded) as soon as I access them later on in the presentation layer.

Have I missed something fundamental, or is there any way to work around this problem?

Sample code
Using a repository pattern, here are some simplified methods (omitting paging, sorting, filtering etc).

Get-method in generic repository

public IEnumerable<T> Get(
    Expression<Func<T, object>>[] includes = null)
{
    IQueryable<T> query = set;

    ...

    // Include properies for eager loading
    if (includes != null)
    {
       query = includes.Aggregate(query, 
          (current, include) => current.Include(include));
    }

    ...

    return query.ToList();
}

Above is called from a service class, something like this

...
context.Licenses.Get(
    includes: new Expression<Func<License, object>>[] { l => l.Comment }
);
...

License POCO

public class License
{
    public License()
    {
    }

    // Primitive properties
    public string ID { get; set; }
    public string Name { get; set; }
    ...
    // Navigation properties
    public virtual LicenseComment Comment { get; set; }
    ...
}

LicenseComment POCO

public class LicenseComment
{
    public LicenseComment()
    {
    }

    // Primitive properties
    public string LicenseID { get; set; }
    public string Comment { get; set; }
}

Accessing the property in a MVC Razor view (or in a Model for that sake)

<span>@license.Comment</span>

Extra SQL queries are generated whenever I try access a License.Comment that is null (looked it up via SQL Server Profiler), which seems to me that Entity Framework lazy-loading kicks in, even though I eagerly included this property.

Slauma Slauma · Accepted Answer · 2012-07-27T12:32:32

I agree with @Ladislav that it should not happen and tested that the "normal" behaviour is the following:

If you load the license including comment...

var license = context.Licenses.Include(l => l.Comment).Single(l => l.ID == 1);

...EF marks the navigation property as loaded:

var isCommentLoaded = context.Entry(license).Reference(l => l.Comment).IsLoaded;

No matter if the comment is null or not isCommentLoaded will be true and no lazy loading query is issued when you access the Comment property.

Even if you don't use eager loading...

var license = context.Licenses.Single(l => l.ID == 1);

...isCommentLoaded will be true if the foreign key from license to comment in the database is NULL (but it will be false if the foreign key is not NULL). The reason is that EF always loads the foreign key in the SELECT statement into the context and if this key is NULL EF knows that there is no related comment in the database and marks the navigation property as already loaded to avoid unnecessary lazy loading.

Eager-loaded related objects which are null are being lazy-loaded

3 Answers