Left Joins with Entity Framework

Yavor Ivanov has been in Melon for 9 years now. Currently he is a Project Manager. From his long software development experience, he shares how to make left joins with Entity Framework. Here it is.  

In the examples bellow I use a small database.

I am a fan of Microsoft SQL Management studio and I like to design my database with it first, after that I generate Entity Framework models. For the generation I use “Code first from database”. If you are used to the old fashioned way of generating classes from database tables, this option is for you.

When you finish with the generation you have the following classes:

Make sure that you have included the following namespaces in your code samples:

using System.Data.Entity;

using System.Linq;

Create DbContext object:

LibraryContext dbContext = new LibraryContext();

Enable logging so you can see the SQL queries sent to the database in your output window.

dbContext.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

In many cases when you make queries and you load objects using entity framework’s navigation properties generated queries will use left join automatically.

For example we load all authors and include all books and their series: 

LibraryContext dbContext = new LibraryContext();

dbContext.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

var resList = dbContext.Set<Author>().AsQueryable()

.Include(a => a.Book.Select(b=>b.BookSeries)).ToList();

In the resulting query tables are joined with left join:

Let’s now make a real left join query by using GroupJoin and DefaultIfEmpty().

First example is a query to return all authors and their book.

LibraryContext dbContext = new LibraryContext();

dbContext.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

var authurs = dbContext.Set<Author>().AsQueryable();

var books = dbContext.Set<Book>().AsQueryable();           

var resList = authurs.GroupJoin(books, authur => authur.Id, book => book.AuthorId, (a, b) => new { Author = a, Book = b.DefaultIfEmpty() }).SelectMany(obj => obj.Book.Select(b => new { Author = obj.Author.Name, Book = b.Name })).ToList();     

Resulting SQL query is:

Let’s now try left join with three tables.

LibraryContext dbContext = new LibraryContext();

dbContext.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

var authurs = dbContext.Set<Author>().AsQueryable();

var books = dbContext.Set<Book>().AsQueryable();

var booksSeries = dbContext.Set<BookSeries>().AsQueryable();

var resList = authurs.GroupJoin(books, authur => authur.Id, book => book.AuthorId, (a, b) => new { Author = a, Book = b.DefaultIfEmpty() })

              .SelectMany(obj => obj.Book.Select(b => new { Author = obj.Author, Book = b }))

              .AsQueryable()

              .GroupJoin(booksSeries, obj1 => obj1.Book.BookSeriesId, booksSerie => booksSerie.Id, (obj2, bs) => new { AuthorBook = obj2, BookSeries = bs.DefaultIfEmpty() })

              .SelectMany(obj3 => obj3.BookSeries.Select(bs => new { Author = obj3.AuthorBook.Author.Name, Book = obj3.AuthorBook.Book.Name, BookSeries = bs.Description })).ToList();

Resulting query is a join with three tables where Books and BookSeries tables are joined with left join.  

You just saw how you can use GroupJoin and DefaultIfEmpty to make left joins. In the current examples I use method syntax, you can change it to LINQ query syntax.

The last example will look like this with query syntax: 

var query = from a in authurs

                         join b in books on a.Id equals b.AuthorId into auth_books

                         from left in auth_books.DefaultIfEmpty()

                         join bs in booksSeries on left.BookSeriesId equals bs.Id into books_bookseries

                         from left2 in books_bookseries.DefaultIfEmpty()

                         select new

                         {

                             Author = a.Name,

                             Book = left.Name,

                             BookSeries = left2.Description

                         };

var resList2 = query.ToList();

Keep experimenting with Entity Framework and it will not stop surprising you.