I’ve recently been playing with Entity Framework again. When using EF in the past, I’ve always managed to keep the queries relatively simple, or used something like a custom query. In this post, I’m going to cover how to join two tables, both via an inner, and outer, join.

LINQ vs Lambda

I’m a big fan of Lambda expressions, and they work great with EF; however, when you’re joining multiple tables, it becomes far to complex to write or read. As a result, I’ve reverted to LINQ To Entities for these examples.

Basic Query

Before we launch into how to join tables, let’s look at a basic query, and how to debug that query if it doesn’t work.

Let’s take the following SQL Query:

SELECT *
FROM MYTABLE M
WHERE M.MYVALUE = '1'

I imagine most people that are (at least vaguely) familiar with SQL will have seen such a query: select everything from MYTABLE where the record has a column named MYVALUE set to 1. For example, if MYTABLE looked like this:

Then the above query would return:

Since only the first record has a MYVALUE set to 1.

The EF version of this syntax would be something like this:

var result = 
  from t in _dbContext.MyTable
  where (t.MyValue == "1")
  select t;

This gets translated to actual SQL, which you can see in the Output debug window of VS, or in the console window, where it should look very similar to the original SQL.

Joins

Now let’s look at a simple join in SQL:

SELECT M.*, S.*
FROM MYTABLE M
INNER JOIN SECONDTABLE S ON
  S.TABLEID = M.ID
WHERE M.MYVALUE = '1'

In LINQ, we have a similar syntax:

var result = 
  from t in _dbContext.MyTable
  join s in _dbContext.SecondTable
    on t.Id equals s.TableId 
  select new { t, s }

This is an inner join; that is, only records that appear in both tables will be returned. What if we want an outer join (records from one table are returned, and from the second table if they are available)?

We can do something like this:

var result = 
  from t in _dbContext.MyTable
  join s in _dbContext.SecondTable
    on t.Id equals s.TableId into secondtablejoined
  from st in secondtablejoined.DefaultIfEmpty()
  select new { t, st }

This should return records from MYTABLE, and any records from SECONDTABLE where they exist. It’s worth bearing in mind that if they don’t exist, they will be null.