So today, I’m going to discuss an interesting little gem that many don’t use or use properly when doing LINQ to SQL queries. The topic of the day is the method DefaultIfEmpty(). This method is often missed when doing multiple JOINS or INNER JOINS that may or may not be what you need. I will most likely do a separate post on TSQL with procedures at some point, but that is not the focus of this post, so I will keep this post to an explanation of the differences between a JOIN and a LEFT OUTER JOIN. The reason I say LEFT JOIN is because that is what the DefaultIfEmpty() method converts to when it is sent to SQL or Oracle. This also holds true in EF. At the end of this post, I will show an exaple of where to use DefaultIfEmpty() in C# code.
Differences between a JOIN and LEFT OUTER JOIN
The JOIN (also known as an INNER JOIN), allows you to join tables together that have a common column or key. Usually, but not always, one of the columns is a tables primary key and the other tables column is a foreign key. Again, I am not going to go into what those are, but feel free to do a quick Google on the subject.
If you are creating your own tables and trying to follow along, here is a quick script to fill the Employees and Titles tables with data.
So lets say that you want to get the title of all the employees. This can easily be accomplished with the INNER JOIN. Using the following, we will get all the columns in both the Employees and Titles table. This returns all employees with a title. Since the TitleI is a foreign key, there are no issues here because the TitleID is forced ensuring that all Employees have a Title. This is one of the uses of a foreign key.
Again, I’m not going to go into how this works, but if you are familiar with the syntax, this is probably pretty obvious. Now lets try adding a join that retrieves all the Departments associated with these employees and their titles by joining the Title_Departments table like this.
Looking at the circled part, running this query returns No results. So why is this? The reason is that even though there employees with titles and there are titles linked to them via foreign key, there is no data in the TITLE_DEPARTMENTS table. When you use the INNER JOIN, nothing is returned if there are no results for that CLAUSE (in this case TD.ID = TITLE.ID. When there are multiple joins, if any fail, none of them return results, so even though there were titles, because there were no departments, no results are returned.
So how do we address this. With a LEFT OUTER JOIN. A LEFT OUTER JOIN (or a RIGHT JOIN) will return any results that matches the INNER JOIN as well as the the LEFT JOIN. If the LEFT JOIN does not return any results, those columns are NULL. Here is an image with an example of such a query.
This makes sure that you get the results you are looking for regardless of the LEFT JOIN results.
So when would you use this type of query? Lets get rid of the foreign key between the tables DEPARTMENTS and TITLE_DEPARTMENTS. I will say up front that I usually shy away from not having relationships, but in my experience there are situations when you don’t want to do a FK or you inherit a system that didn’t do it and there is too much data to change it at that point. Regardless of the reason, here is a diagram of what the structures now look like.
Now lets put some data in the database for DEPARTMENTS and TITLE_DEPARTMENTS.
Now, if you run that same query for the LEFT OUTER JOIN, you will see both the rows with and without departments.
So again, when is this useful, well what if you want to know what employees have a title and department link.
Or how about if you wanted to know who has a title, but not a department.
You will notice that the query returns everything with a title, but not assigned a title. You could use this in a procedure, that finds all of these and prepopulate with the correct department.
So that wraps up LEFT JOINS and as promised, here is an example of C# code that will implement this idea
var query = from employee in employeeSet join title in titleSet on employee.TitleId equals title.ID join title_department in title_departmentSet on title_department.TitleId equals title.ID into titleGrp from title in titleGrp.DefaultIfEmpty()