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; } = default!;
}
[Table("People")]
public class Person
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int PersonID { get; set; }
    public string FirstName { get; set; } = default!;
    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=sandbox;integrated security=true;");

    public DbSet<Food> Foods { get; set; } = default!;
    public DbSet<Person> People { get; set; } = default!;
}

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

var firstQuery = (from p in context.People
                  select new
                  {
                      p.PersonID,
                      p.FirstName,
                      FoodID = (int?)p.FavoriteFood!.FoodID,
                      FoodName = (string?)p.FavoriteFood!.FoodName
                  });

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.

View code on GitHub

Comments

Popular posts from this blog

C# Record Serialization

Add timestamp to photo using ImageMagick

DotNet CLI