EF's lazy-loading might break your reality when mixed with an improper DB setup

Referencing nested objects in your LINQ to SQL query does some pretty neat magic “behind the scenes”. But, there are occasions when its magic might leave you with unexplainable exceptions. By shear happenstance (that’s for you, Scott), your database might not have foreign-keys added. Or, they’re added, but not enabled. Your LINQ looks right and compiles, but runtime is just not your friend. This post outlines part of that magic and even provides a way to safeguard your query. After reading, see if you can tell where I now reside on the whole lazy-loading debate 😉.

The setup

Here’s a breakdown of the various models and tech. stack used within the post:

• Entity Framework 6
• LocalDB 13
• Lazy-loading enabled

public class Business
{
    public int BusinessId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Member> Members { get; set; } 
}

public class Member
{
    public int MemberId { get; set; }
    public string Name { get; set; }
}

public class BusinessContext
{
    public virtual DbSet<Business> Businesses { get; set; }       
}

DB setup

==========
Businesses
----------
BusinessId
Name
==========

=====================
BusinessMembers (1:*)
---------------------
BusinessId
MemberId
=====================

=======
Members
-------
MemberId
Name
=======

Which of these snippets has the potential to throw a NRE?

Without .Include

var business = context.Businesses
    .Where(x => x.Name == "Bill's Business")
    .SingleOrDefault();

if (business != null)
{
    var members = business
        .Where(x => x.Members.Name == "Bill");
}

With .Include

var business = context.Businesses
    .Where(x => x.Name == "Bill's Business")
    .SingleOrDefault();

if (business != null)
{
    var members = business
        .Include(x => x.Members)
        .Where(x => x.Members.Name == "Bill");
}

It’s the first-one. And here’s why:

1. Improper or non-functioning foreign-keys means the potential for invalid data!

2. Lazy-loading means extra database calls!

With the first snippet, using Members.Name in the LINQ-statement results in an additional database-call to get info. from the Members table. But, the BusinessMembers table might have MemberIds that don’t exist in the Members table. Accessing a property on a null reference means 🤯. Here’s a shortened (and clarified)-version of the SQL EF generates:

select
    *
    from (select top 1 b.*, join1.*
        from Businesses b  
        left outer join BusinessMembers on Businesses.BusinessId = BusinessMembers.BusinessId as join1
        where Businesses.Name = @p__linq__0
    )  AS ...
    order by ...

Remember, this query is just to get the Business and BusinessMembers info. An additional query will be executed to get Members once the second-query in our csharp example is executed.

The second snippet results in an inner join between BusinessMembers and Members. This effectively filters-out those invalid records:

select
    *
    from (select top 1 b.*, join1.*, join2.*
        from Businesses b
        left outer join BusinessMembers on Businesses.BusinessId = BusinessMembers.BusinessId as join1
        inner join Members on BusinessMembers.MemberId = Members.MemberId as join2
        where Businesses.Name = @p__linq__0
    )  AS ...
    order by ...

Summary

Of course, with lazy-loading disabled, using the .Include would be required anyway, so maybe an alternate title for this post should be ”Lazy-loading... (╯°□°）╯︵ ┻━┻”. 😜