Join on Multiple Columns in Linq To SQL

Today I had to create a Linq-To-SQL query which joined two tables. These tables had compound primary keys (table simplified for example):

Table Referrers

ServerID (PK) ReferrerID (PK) Value
1 1 My value 1
1 2 My value 2
1 3 My value 3

Table ReferrerInfo

ServerID (PK) ReferrerID (PK) Value
1 1 More info… 1
1 2 More info… 2
1 3 More Info… 3

So I wanted to join these together using a Linq-To-SQL query. The first thought that occured to me was to use a join:

1
2
3

var referrers = from r in Referrers
               join ri in ReferrerInfo on r.ServerConnectionID equals ri.ServerConnectionID ..... ??????
               select r;

So, apparently in C# you cannot do multiple columns in your join. I have to join on both ServerConnectionID and ReferrerID.

This page: http://www.onedotnetway.com/linq-to-sql-join-on-multiple-conditions/ explains one method to do so, but unfortunately that Linq query only works in VB.

So, I ended up writing a Linq-To-SQL that just uses the old style of joining, by using the where clause!

1
2
3
4
5

var referrers = from r in Referrers
                from ri in Referrer_Info
                where r.ServerConnectionID == ri.ServerConnectionID &&
                   r.ReferrerID == ri.ReferrerID
                select r;

This successfully executes my multi-column join.

Follow Dave on Twitter

This entry was posted on Friday, November 20th, 2009 at 10:57 am and is filed under CSharp, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply