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.
Comments
Post a Comment