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 occurred to me was to use a join
:
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!
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
.