How to Join Multiple Columns with 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 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 &amp;&amp;
                   r.ReferrerID == ri.ReferrerID
                select r;

This successfully executes my multi-column join.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Dave C Andrews

Software Developer (Senior) LBMC Technologies
United States

I've been developing software since I was 9 years old, in BASIC on a Precomputer 2000. OK, that's a stretch, but hopefully some of the stuff that I learned from that little toy have helped my professional career.

I have a bachelors degree in computer science from MTSU. Currently, I am employed as a senior software developer for LBMC in Nashville, TN. We serve as consultant developers for clients across the southeastern United States, developing mostly in Microsoft .Net with Microsoft SQL Server.

I greatly enjoy my job and my hobbies lie in open source software and in teaching others how to be better programmers. I have a beautiful wife, 2 dogs (a dachshund and an olde-english bulldog) and a cat.

Follow on

Twitter

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

A Technical Blog, originally posted at http://davethedev.com/2011/12/how-to-join-multiple-columns-with-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 i

Type	Technical Blog
Licence	CPOL
First Posted	27 Jan 2012
Views	9,108
Bookmarked	2 times

WCF

		9,932,402 members (36,332 online) Visit CodeProject.TV Discuss CodeProject.TV Sign in Email Password Forgot your password? Sign in using

How to Join Multiple Columns with Linq To SQL

License

About the Author

Comments and Discussions