LINQ To SQL Join On Multiple Conditions

September 16th, 2008 | 20 Comments | Posted in Programming

LINQ To SQL or just plain LINQ allows us to do a join on multiple conditions using an elegant technique. In this post I will show you how to perform a Join on multiple conditions. As an example I will take two tables called House and ShoppingMall.

Both these tables have PostCode and CouncilCode as common fields. Lets say that we want to retrieve all records from ShoppingMall where both PostCode and CouncilCode on House match. This requires us to do a join using two columns. In LINQ such a join can be done using anonymous types. Here is an example.

var query = from s in context.ShoppingMalls

            join h in context.Houses

on

            new { s.CouncilCode, s.PostCode }

            equals

             new { h.CouncilCode, h.PostCode }

            select s;

The code above gets translated into this SQL query.

SELECT [t0].[ShoppingMallId], [t0].[Address],

[t0].[PostCode], [t0].[CouncilCode]

FROM [dbo].[ShoppingMall] AS [t0]

INNER JOIN [dbo].[House] AS [t1]

ON ([t0].[CouncilCode] = [t1].[CouncilCode])

AND ([t0].[PostCode] = [t1].[PostCode])

And when the above query is executed it produces the results we want.

Leave a Reply 66519 views, 10 so far today |

Tags: LINQ

Follow Discussion

20 Responses to “LINQ To SQL Join On Multiple Conditions”

loopGhost Says:
October 3rd, 2008 at 8:55 am
Awesome… bump, so hopefully this will get a little google traction.
jwright Says:
December 30th, 2008 at 4:13 am
something to keep in mind.
if your joining two tables where the linking columns have different names, the anonymous types’ properties must match.
i.e.,

var query = from s in context.ShoppingMalls
join h in context.Houses
on
new {CouncilCode=s.CouncilCode, PostCode=s.PostCode }
equals
new {CouncilCode=h.District, PostCode=h.ZipCode }
select s;
Deepak Says:
December 30th, 2008 at 8:27 am
Thanks for the tip jwright.
mp09ind Says:
February 11th, 2009 at 10:37 pm
What is context here?
Deepak Says:
February 12th, 2009 at 8:29 am
mp09ind,

context is my DataContext here which I generated using Visual Studio designer. You can find more details on how to do that here.

http://www.thereforesystems.com/linq-to-sql-tutorial/
mike313t Says:
June 25th, 2009 at 4:04 am
What if when joining one condition is an “equals”
and another condition is “not equals”?
IanF Says:
July 8th, 2009 at 9:15 pm
Hi this is great unfortunatley it results in an ‘OR’ for me and not an AND. i.e.:

SELECT [t0].[ShoppingMallId], [t0].[Address],[t0].[PostCode], [t0].[CouncilCode]FROM [dbo].[ShoppingMall] AS [t0]INNER JOIN [dbo].[House] AS [t1]ON ([t0].[CouncilCode] = [t1].[CouncilCode])OR ([t0].[PostCode] = [t1].[PostCode])
Deepak Says:
July 8th, 2009 at 9:20 pm
Ian,

What does your LINQ query look like? Can you post it here?
IanF Says:
July 9th, 2009 at 1:13 am
I can include a little bit, well the bit that is the offending part.

join E in bfd.BFDStateMachineTypeState
on new { D.StateMachineTypeID, A.StateID
equals new { E.StateMachineTypeID, E.StateID }
join L in bfd.BFDStateMachineTypeService
on new { D.StateMachineTypeID, H.ServiceID }
equals new { L.StateMachineTypeID, L.ServiceID }

Thanks,

Ian
IanF Says:
July 9th, 2009 at 1:25 am
Sorry ignore me, it is not the join that is causing the OR condition. I have run Profiler and I am getting superfluos null tests like this:

INNER JOIN [dbo].[BFDStateMachineTypeState] AS [Extent8] ON (([Extent7].[StateMachineTypeID] = [Extent8].[StateMachineTypeID]) [b]OR (([Extent7].[StateMachineTypeID] IS NULL) AND ([Extent8].[StateMachineTypeID] IS NULL)))[/b] AND (([Extent6].[StateID] = [Extent8].[StateID]) [b]OR (([Extent6].[StateID] IS NULL) AND ([Extent8].[StateID] IS NULL)))[/b]
Deepak Says:
July 9th, 2009 at 7:26 pm
IanF, no worries.
Andrei Says:
March 9th, 2010 at 1:21 am
i have this sql query
select * from db.lc_Products p, db.lc_Products pc
where ( p.ProductID = pc.Recommendation1
or p.ProductID = pc.Recommendation2)
and pc.ProductID = ”

can someone help me, please, to transform it to Linq…

var query = (from p in db.lc_Products
join pc in db.lc_Products on p.ProductID equals pc.Recommendation1
where (p.ProductID == pc.Recommendation1 || p.ProductID == pc.Recommendation2)&& pc.ProductID == ProductID
select p);

I need another join with (||) like
join pc in db.lc_Products on p.ProductID equals pc.Recommendation1 or p.ProductID equals pc.Recommendation2
Alex Says:
July 9th, 2010 at 8:04 am
Hello IanF,

the NULL-Conditions are NOT superfluos if you have allow NULL-values in the table.
Simple keep in mind, that some Databases like ORACLE does not match NULL like NULL.
This is a real point of too much Database-Application-Bugs for database programmers:)
Tog Says:
August 5th, 2010 at 10:13 pm
Hi,

I did a little goog’ling without much success. Maybe someone here know of a way/workaround. I want to join two tables by using something other that ‘equals’ – like (greater than). Is there a way of doing that with these statements?

Thanx.
Paulo Says:
August 6th, 2010 at 7:42 am
Can you show an example of a join on multiple conditions where either is allowed.
Example sql:
select from users u
join other_users o
where u.email = o.email or u.login = o.login
Deepak Says:
August 6th, 2010 at 9:44 am
Hi Tog,

As per documentation on MSDN the only joins supported are equijoins.

http://msdn.microsoft.com/en-us/library/bb311040.aspx

But you can achieve what you want by using subqueries.
Joppe Says:
August 12th, 2010 at 9:17 pm
Thanks! And thanks to jwright! You saved my life! ;) (why isn’t this stuff in the manual…or am I just blind…probably)
Deepak Says:
August 13th, 2010 at 9:52 am
Joppe,

I agree. Documentation on LINQ To SQL is not all that good. Maybe because they are still hell bent on making EF the primary choice for data access when clearly LINQ To SQL has many advantages over EF.

Anyhow thanks for you comment and I’m glad that my site could be of help.
Swapna Says:
September 1st, 2010 at 7:30 pm
Thnx…
Deepak Says:
September 1st, 2010 at 8:14 pm
most welcome Swapna.