Click here to Skip to main content
11,430,402 members (76,510 online)
Click here to Skip to main content
Technical Blog

Tagged as

How to Join Multiple Columns with Linq To SQL

, 27 Jan 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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.

License

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

Share

About the Author

Dave C Andrews
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.
Follow on   Twitter

Comments and Discussions

 
SuggestionAnonymous types... Pin
Richard Deeming1-Feb-12 9:46
memberRichard Deeming1-Feb-12 9:46 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150428.2 | Last Updated 27 Jan 2012
Article Copyright 2012 by Dave C Andrews
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid