Left Join in Entity Framework

If you need a left join in Entity Framework, you have a couple options. First, if you’re using a real foreign key that just happens to be nullable, then you can use the regular navigation properties. But if you’re doing a left join manually, or with other factors, then you need to do things just a little differently:

Suppose we have the following database:

create table dbo.Foods (
  FoodID int not null identity primary key
 ,FoodName varchar(100) not null
);
go
insert dbo.Foods (FoodName) values ('Pizza'), ('Chicken'), ('Potatoes'), ('Broccoli');
go
create table dbo.People (
 PersonID int not null identity primary key
 ,FirstName varchar(100) not null
 ,FavoriteFoodID int null
 ,constraint FK_Person_FavoriteFoodID
  foreign key (FavoriteFoodID) references dbo.Foods (FoodID)
);
go
insert dbo.People (FirstName, FavoriteFoodID)
values ('John', 1), ('Mary', 2), ('Pat', null);
go

We can build our Entity Framework tables as follows:

[Table("Foods")]
public class Food {
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int FoodID { get; set; }
    public string FoodName { get; set; } = "";
}

[Table("People")]
public class Person {
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int PersonID { get; set; }
    public string FirstName { get; set; } = "";
    public int? FavoriteFoodID { get; set; }

    [ForeignKey(nameof(FavoriteFoodID))]
    public Food? FavoriteFood { get; set; }
}

public class MyContext : DbContext {
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
        optionsBuilder.UseSqlServer(@"server=(localdb)\MSSQLLocalDB;database=sandbox20200409;integrated security=true;");
    }

    public DbSet<Food>? Foods { get; set; }
    public DbSet<Person>? People { get; set; }
}

The navigation property FavoriteFood gives us the ability to harness Entity Framework’s intelligence to build a query:

using var context = new MyContext();
var firstQuery = (from p in context.People
                  select new
                  {
                      p.PersonID,
                      p.FirstName,
                      p.FavoriteFood!.FoodID,
                      p.FavoriteFood.FoodName
                  }).ToArray();

The generated SQL looks as we’d expect:

SELECT [p].[PersonID], [p].[FirstName], [f].[FoodID], [f].[FoodName]
FROM [People] AS [p]
LEFT JOIN [Foods] AS [f] ON [p].[FavoriteFoodID] = [f].[FoodID]

┬áBut if that navigation property wasn’t there, then we have an alternative way of doing a left join:

var secondQuery = (from p in context.People
                   from f in context.Foods.Where(f => f.FoodID == p.FavoriteFoodID).DefaultIfEmpty()
                   select new
                   {
                       p.PersonID,
                       p.FirstName,
                       f.FoodID,
                       f.FoodName
                   }).ToArray();

This generates identical SQL to the first one. Note the DefaultIfEmpty call.

There are other ways, but I find this to be very easy to read and understand.

Lazy Loading in Entity Framework Core

Here’s all you need to do to enable lazy loading of navigation properties in Entity Framework Core – doing this allows you to call one of the navigation properties after the original query, and EF will figure out what SQL it needs to run to pull that data.

If you know you’re going to need the data, it’s generally better to pull it upfront, using Include or including the data in the LINQ select clause. But sometimes the option is nice, so I recommend allowing it, and deciding at dev time whether or not to use it.

Install-Package Microsoft.EntityFrameworkCore.Proxies
 -- or --
dotnet add package Microsoft.EntityFrameworkCore.Proxies

In the OnConfiguring method, just tack on UseLazyLoadingProxies, like:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseLazyLoadingProxies().UseSqlServer(_connectionString);

Make sure your navigation properties are all marked virtual (thankfully, you’ll get a friendly runtime error right away if you forget this step):

[Table("Customers")]
public class Customer {
    [Key]
    public int CustomerID { get; set; }
    public string Name { get; set; } = "";

    [InverseProperty(nameof(Customer))]
    public virtual List<Car>? Cars { get; set; }
}

[Table("Cars")]
public class Car {
    [Key]
    public int CarID { get; set; }
    public string Make { get; set; } = "";
    public int CustomerID { get; set; }

    [ForeignKey(nameof(CustomerID))]
    public virtual Customer? Customer { get; set; }
}

// Use lazy loading: easiest, but requires two database calls
async static Task Main() {
    using var context = new MyContext();

    /*
        SELECT TOP(2) [c].[CustomerID], [c].[Name]
        FROM [Customers] AS [c]
        WHERE [c].[CustomerID] = 4
    */
    var cust = await context.Customers.SingleAsync(x => x.CustomerID == 4).ConfigureAwait(false);
    Console.WriteLine(cust.Name);

    /*
        exec sp_executesql N'SELECT [c].[CarID], [c].[CustomerID], [c].[Make]
        FROM [Cars] AS [c]
        WHERE [c].[CustomerID] = @__p_0',N'@__p_0 int',@__p_0=4
    */
    foreach (var car in cust.Cars!) {
        Console.WriteLine($"{car.CarID}: {car.Make}");
    }
}

// Get the data upfront using Include - single database call, but more complex query
async static Task Main() {
    using var context = new MyContext();

    /*
        SELECT [t].[CustomerID], [t].[Name], [c0].[CarID], [c0].[CustomerID], [c0].[Make]
        FROM (
            SELECT TOP(2) [c].[CustomerID], [c].[Name]
            FROM [Customers] AS [c]
            WHERE [c].[CustomerID] = 4
        ) AS [t]
        LEFT JOIN [Cars] AS [c0] ON [t].[CustomerID] = [c0].[CustomerID]
        ORDER BY [t].[CustomerID], [c0].[CarID]
    */
    var cust = await context.Customers.Include(c => c.Cars)
        .SingleAsync(x => x.CustomerID == 4).ConfigureAwait(false);
    Console.WriteLine(cust.Name);

    foreach (var car in cust.Cars!) {
        Console.WriteLine($"{car.CarID}: {car.Make}");
    }
}