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.
44 Responses to LINQ To SQL Join On Multiple Conditions
Leave a Reply
Connect with me
-
Awesome… bump, so hopefully this will get a little google traction.
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;
Thanks for the tip jwright.
What is context here?
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/
What if when joining one condition is an “equals”
and another condition is “not equals”?
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])
Ian,
What does your LINQ query look like? Can you post it here?
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
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]
IanF, no worries.
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
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:)
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.
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
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.
Thanks! And thanks to jwright! You saved my life! ;) (why isn’t this stuff in the manual…or am I just blind…probably)
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.
Thnx…
most welcome Swapna.
[...] Innen. [...]
Thanks
Thanks for this, I’m just experimenting with Linq to SQL. Any ideas how you do multiple joins? I.e. more than two tables? Or is it possible to use a variable outside of the query in it?
Thanks
Hi James,
LINQ provides a keyword “Let” which can be used within a query. You can do joins on multiple tables as you would with one table. I’m happy to help if you have any specific query you need help with.
Thanks! Just read that Linq to SQL is discontinued.. D’oh!
Nevermind, still good for learning general OR/M syntax. I’ll look into NHiberate in the near future :)
Thanks,
James,
I’m not sure if LINQ to SQL is discontinued. However it does fall low in priority for Microsoft. Entity Framework is the preferred ORM from Microsoft and the product has become a lot more stable and useful in its latest version i.e. Entity Framework 4
You can find join and more using LINQ or LAMBDA example from following link
http://webmingle.blogspot.com/2010_09_01_archive.html
In your query
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;
what if PostCode is Nullable datatype in House and not null in ShoppingMall how do we change the query?
Kunal,
Without actually running the query I’m presenting a suggestion. What if you say h.PostCode.Value? Will that work?
thanks for the replies.. (sorry for the delay)
Thanks but its not working error will raised in “join” i.e Failed to call inference
Thanks a lot.
It works very fine for my case.
I have this SQL query:
/* Get all employees for an approver who did not enter a timecard yesterday */
select l.empl_no, l.first_name, l.last_name
from ldempl l
where l.aprv_id = ’0001234′
and l.term_date is null
and not exists (select 1
from employee e2
where e2.empl_id = l.empl_no
);
Can anyone help translate this to Linq or Lambda? I have found some examples but none that come close. Much appreciated!
Thanks for sharing simple, to the point code .
Thanks for help :)
Most welcome Gosia.
i have tried above given example but i was facing some problem with join keyword that error says:
“The type of one of the expressions in the join clause is incorrect. Type interface failed in the call to ‘Join’.”
but later i tried this below code then its working well….
var joinLinq = from e in db.Emps
join d in db.Depts on e.Fk_Did equals d.Did
select e;
—-
|or|
—-
var joinQuery = from e in db.Emps
join d in db.Depts on e.Fk_Did equals d.Did
select new
{
e.empno,
e.ename,
e.esal,
d.DeptName
};
if anybody facing the same problem, jus go up with this…
Happy coding…!!! :-)
Thanks Vishnu.
@Vishnu: I have the same problem. “Cannot resolve method join….”
still don’t know why :(
Thanks! Works 100%
Most welome Jlu.
Can anyone help me? I have a query which need to join two field to do a search function. However, I don’t know how to do in Linq.
eg. Select * from user where (user.address1 + user.address2) = ‘TestingABC’
thanks
Thanks so much! It works great
FADU