Left join with Entity Framework

#entityframework #dotnet #csharp #sql

In Entity Framework, you can write simple queries and complex queries using LINQ, and the SQL will be generated for you, in addition to the object mapping.

This can be pretty awesome, but there are some things that you just can't do with LINQ but you can with SQL. Or there are things, such as LEFT JOIN, which you can do in LINQ if you know how.

There are a few ways to accomplish it, but here is the most straightforward to me if you don't want to use navigation properties. Instead of a join, add a second "from", with a "Where" clause attached, adding "DefaultIfEmpty":

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