|
LINQ To SQL Join On Multiple Conditions
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
34618 views, 154 so far
today |
Get Updates By Email
Popular Post
Tag Cloud
Code Snippets
- Get Current Windows User In C#
- Get Width And Height Of Image In C#
- Get Windows Registry Size With WMI And C#
- Reverse Array Elements Using C#
- Convert Hexadecimal To Number In C#
- Get Free Disk Space Using T-SQL
- SQL Server 2008 – Get All Indexes In A Database
- Get Name Of Current Executing Assembly In C#
- Get CD Or DVD Drive Information Using WMI And C#
- Get Last Row From Table Using LINQ To SQL
October 3rd, 2008 at 8:55 am
Awesome… bump, so hopefully this will get a little google traction.
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;
December 30th, 2008 at 8:27 am
Thanks for the tip jwright.
February 11th, 2009 at 10:37 pm
What is context here?
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.onedotnetway.com/linq-to-sql-tutorial/
June 25th, 2009 at 4:04 am
What if when joining one condition is an “equals”
and another condition is “not equals”?
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])
July 8th, 2009 at 9:20 pm
Ian,
What does your LINQ query look like? Can you post it here?
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
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]
July 9th, 2009 at 7:26 pm
IanF, no worries.