LINQ To SQL or just plain LINQ allows us to do a join on multiple conditions using an elegant technique. 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.