LINQ to SQL DefaultIfEmpty() usage

Introduction

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.

image

 

 

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.

image

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.

image

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.

image

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.

image

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.

image

Now lets put some data in the database for DEPARTMENTS and TITLE_DEPARTMENTS.

image

 

Now, if you run that same query for the LEFT OUTER JOIN, you will see both the rows with and without departments.

 

image

So again, when is this useful, well what if you want to know what employees have a title and department link.

image

Or how about if you wanted to know who has a title, but not a department.

image

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()

 

Happy Coding Smile

Advertisements

About Gregg Coleman

I am Senior-level Software Engineer working primarily these days with .NET. I have a good working knowledge of ASP.NET MVC, Web Forms, WCF web services and Windows Services. I spend much of my time in the Web Services (SOAP and REST) world in my current job designing and implementing various SOA architectures. I have been in the software engineering industry for about 6 years now and will not now nor ever consider myself an "expert" in programming because there is always so much to learn. My favorite thing about designing software is there are always new emerging technologies and something to learn every day! My current job has me spending much of my job on the bleeding edge of technologies and changing gears all the time, so I'm never bored and always challenged. On my spare time I enjoy weight training, reading and venturing to new places near by. Of course programing and learning new technologies are another hobby of mine.
This entry was posted in .NET, .NET 4.5, Best Practices, C#, Classes, Databases, Entity Framework, Generics, LINQ, LINQ To SQL, Programming, Programming Paradims, SQL Server and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s