Left Join in Entity Framework

Posted by Joe Enos on June 01, 2021 · 13 mins read

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.