Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / SQL

How to Join Multiple Columns with Linq To SQL

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
27 Jan 2012CPOL 47.5K   2   3
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
11My value 1
12My value 2
13My value 3

Table ReferrerInfo

ServerID (PK)ReferrerID (PK)Value
11More info… 1
12More info… 2
13More 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:

C#
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!

C#
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.

License

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


Written By
Software Developer (Senior) LBMC Technologies
United States 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.

Comments and Discussions

 
SuggestionNot a blog ... Pin
Wonde Tadesse28-Jan-12 14:25
professionalWonde Tadesse28-Jan-12 14:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.