Left Outer Join in LINQ

So, you’re gonna notice that I’m just gonna start throwing random code at you for the next few weeks. I’m building an application in Silverlight 2, and there’s going to be many lessons for me to learn along the way.

Those lessons that I can turn into a tutorial will become posts, and today is the second one. (If you’re just joining us, the first one was Simple Scaled Resizing In Silverlight 2).

So after I got my application resizing nicely, I wanted to start working with my legacy database. We’ll cover how I created my WCF Service in a future post, but for today, I want to talk about how to do a LEFT OUTER JOIN statement in LINQ. It’s not necessarily obvious.

First things first, let’s look at my data structure.

Ideally, I want to be able to pull out a list of teams that meet a certain criteria, and also retrieve their information for this year (2008 in this example) if it even exists, which it may not. The standard join statement I would write for this would look like:

But, because there is actually NO data (I haven’t populated it yet) in the team_annual_data table, my query returns 0 rows, because, much like an INNER JOIN in T-SQL, there wasn’t matching data on each side of the query, so there weren’t any qualifying rows to return. But I want all of the rows from the left side, and if there’s data on the right, let’s have that too, but it’s not required. To do that, we need to structure our join statement a little differently:

The primary difference is the “join into” statement. That creates the “LEFT OUTER JOIN” in the resulting T-SQL statement, allowing me to retrieve my “maybe it does, maybe it doesn’t” set of data.

5 thoughts on “Left Outer Join in LINQ

  1. players plurals narayan licensor conveyed conceded agricultural bravo painkiller mobilization thus semelokertes marchimundui

  2. Oh how ugly is the outer join syntax in Linq. I guess it is because they were trying to go for a provider agnostic syntax, but it sure isn’t discoverable for some things.

  3. very good article. its very useful to me

  4. Where is 'a' coming from in the second example? Most of it is making sense to me, except that.

  5. Updating your tourney site from the old-school stuff to new technology? Definitely interested to see how this app turns out… and it gives me a reason to maybe try to keep up with you guys and your Silverlight talks.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s