Answered by:
join on multiple conditions

Question
-
Hello
Sorry for this stupid question but how can i have a multiple condition on a join?
I want to write
Code Snippetfrom p in persons
join w in workers on p.LastName equals w.LastName and p.FirstName == w.FirstNameAs it's not compile, I do
Code Snippetfrom p in persons
join w in workers on p.LastName equals w.LastName
where p.FirstName == w.FirstNameBut first code should be better.
What is the syntax to do this?
Thanks,
Matthieu
Wednesday, May 16, 2007 10:45 PM
Answers
-
Just use an anonymous type to combine the fields you want to join on:
Code Snippetfrom p in persons
join w in workers
on new { p.FirstName, p.LastName } equals new { w.FirstName, w.LastName }
select ...;
Anders
Thursday, May 17, 2007 12:26 AM -
You are getting this error because the types on either side of the "equals" clause do not match. I can't tell from your example whether Vehicle.VehicleKey and Vehicle.ParticipantNumber are both of type int?, but I suspect one or both are not. If, in your second example, you insert (int?) casts in the left hand side anonymous type you should be fine, but probably some of those casts are unnecessary.
I notice that you are doing a "join vd in ... into damages" immediately followed by a "from vd in damages". That is a complicated way to writing an inner join. Just delete the "into damages" and the following "from vd in damages".
Anders
Thursday, January 31, 2008 8:20 PM
All replies
-
Just use an anonymous type to combine the fields you want to join on:
Code Snippetfrom p in persons
join w in workers
on new { p.FirstName, p.LastName } equals new { w.FirstName, w.LastName }
select ...;
Anders
Thursday, May 17, 2007 12:26 AM -
ThanksThursday, May 17, 2007 4:44 PM
-
Is there a way to override the GetHashCode() for anonymous types for a more dynamic Equals() evaluation? I could see the need to use GUIDs and identity values for these types of joins.
Thanks!
Michael
Monday, June 4, 2007 7:16 AM -
Anders:
How about if I must join on multiple conditions, some involving nullables?
If I do this:
var
q = from v in db.Vehicles join vd in db.VehicleDamages on new { v.VehicleKey, v.ParticipantNumber } equals new { (int?)vd.VehicleKey, (int?)vd.ParticipantNumber } into damages from vd in damages select new { v.VehicleKey, vd.FirstPointOfImpactIndicator };...then I get the following message(s):
Error 1 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.
Error 2 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.
But if I do this:
var
q = from v in db.Vehicles join vd in db.VehicleDamages on new { VehicleKey = v.VehicleKey, ParticipantNumber = v.ParticipantNumber } equals new { VehicleKey = (int?)vd.VehicleKey, ParticipantNumber = (int?)vd.ParticipantNumber } into damages from vd in damages select new { v.VehicleKey, vd.FirstPointOfImpactIndicator };...then I'm still left with:
Error 1 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.
These kind of joins, as you know, are very common in OLAP systems. For my case, I have a data warehouse system with a snowflake style schema. So I run into these queries a high percentage of time - probably two-thirds of the queries involve composite joins and also involve optional relationships.
Ramble: It is going to be exciting to see what kind of LINQ snippets percolate out of the woodwork when SQL Server 2008 rolls around with more warehousing features!
- Dustin
Thursday, January 31, 2008 6:36 PM -
You are getting this error because the types on either side of the "equals" clause do not match. I can't tell from your example whether Vehicle.VehicleKey and Vehicle.ParticipantNumber are both of type int?, but I suspect one or both are not. If, in your second example, you insert (int?) casts in the left hand side anonymous type you should be fine, but probably some of those casts are unnecessary.
I notice that you are doing a "join vd in ... into damages" immediately followed by a "from vd in damages". That is a complicated way to writing an inner join. Just delete the "into damages" and the following "from vd in damages".
Anders
Thursday, January 31, 2008 8:20 PM -
Ah, ok. I see now. This is why strict type checking is a good thing!
The goal of that query was to exercise one-sided nullability of a key, such as:
Code Snippetvar q =
from o in db.Orders
join e in db.Employees
on o.Employee.EmployeeID equals (int?)e.EmployeeID into emps
from e in emps
select new { o.OrderID, e.FirstName };
As obvious, I just morph'ed that example into my context.
Thanks for pointing that out.
Anders, you and the C# team have thoroughly spoiled us! Naughty! Kidding. This is really evolutionary stuff. Just want to tip my hat to you and the team - thanks for your work!
Carry on,
Dustin
Thursday, January 31, 2008 9:20 PM -
Hi Anders,
what is the case, in below query, with OR operator rather than AND operator in join on condition,
from p in persons
join w in workers on p.LastName equals w.LastName and p.FirstName == w.FirstName
please help me out.
thanks
-Praveen.Wednesday, March 5, 2008 12:30 PM -
You can do this:
Code Snippet(from p in persons
join w in workers on p.LastName equals w.LastName
select new {P = p, W = w}).
Union(
from p in persons
join w in workers on p.FirstName equals w.FirstName
select new {P = p, W = w}).
Distinct();
Wednesday, March 5, 2008 2:46 PM -
Hi
Your solution worked fine, But what the problem is, writing 2 queries and applying Union generating Too big query.
Instead of that, we can give like this?.
join someclassname on condition1 or condition2.
any way thanks for your post.
thanks
-Praveeen,Thursday, March 6, 2008 7:47 AM -
I am new with LINQ and find it is not too hard to follow. However, I still have a long way to go and now I have a SQL query that I would like to rewrite in LINQ and wondering anybody could help me in this. Here is the query:
Code SnippetSELECT
[t2].[ProjectID]FROM
[userdept] AS [t0]INNER
JOIN ( [userdist] AS [t1]ON [t1].[district] = [t4].[ShortName] ) ON [t0].[dept_code] = [t3].[DeptID]
INNER
JOIN [BSProjects_FundingSources_cap] AS [t5] ON [t2].[ProjectID] = [t5].[ProjectID]WHERE
[t1].[userid] = 15 AND [t0].[userid] = 15What would be an optimum way to turn the above SQL into LINQ? Thanks in advance for any response.
Sunday, May 18, 2008 8:13 PM -
Praveeen, you can try the following. It's essentially a cross-join with a where clause that results in the same thing as creating an inner join on two conditions:
Code Snippetvar q = from p in db.Persons
from w in db.Workers
where p.FirstName == w.FirstName || p.LastName == w.LastName
select new
{
Person = p,
Worker = w
};
Mirzasa, I think the following implementation could work:
Code Snippetvar q = from bspc in db.BSProjects_cap
join dar in db.Dept_Agency_reg on bspc.DeptID equals dar.DeptID
join dr in db.Districts_reg on dar.District equals dr.ShortName
join udi in db.userdist on udi.district equals dr.ShortName
join ude in db.userdept on ude.dept_code equals dar.DeptID
join bspfsc in db.BSProjects_FundingSources_cap on bspc.ProjectID = bspfsc.ProjectID
where udi.userid == 15 && uder.userid == 15
select bspc.ProjectID
Tuesday, May 20, 2008 3:58 PM -
just more 2 cents,
if the join columns on your schema don't have the same name don't forget to name the properties of the anonymous type, so you can have a math the left side with the right
Code Snippetfrom t1 in table1
join t2 in table2 on new { KEY1 = t1.TB1Key1, KEY2 = t1.TB1Key2 }
equals new { KEY1 = t2.TB2Key1, KEY2 = t2.TB2Key2 }select new { t1 , t2}
Monday, June 30, 2008 12:32 PM -
Thanks Martin, your answer for Praveen is exactly what I was looking for. Out of curiosity I wander what would the expression look like if it was methods rather than linq sql syntax.Wednesday, October 1, 2008 1:46 AM
-
Unfortunately, I think that the implementation through extension methods rather than native LINQ requires a bit more work. You'd have to define, in addition to your method call two additional classes:
1) A key class that exposes properties required to build the join condition
2) An IEqualityComparer<T> implementation for said key class
Essentially, the Equals method you're implementing for the IEqualityComparer generic interface resolves the join condition, and you would make the entire method call as follows:
Code Snippetvar results = db.Persons.Join(
db.Workers,
person => new MyKey { FirstName = person.FirstName, LastName = person.LastName },
worker => new MyKey { FirstName = worker.FirstName, LastName = worker.LastName },
(person, worker) => new { Person = person, Worker = worker),
new MyKeyEqualityComparer());
Wednesday, October 1, 2008 7:34 PM -
Check the following post
http://sivakrishnakuchi.blogspot.com/2010/04/linq-query-for-multiple-join-conditions.html
Wednesday, April 14, 2010 9:29 AM -
I have the following query:
var stuff =
((from t in this.ObjectContext.Tasks
join r in this.ObjectContext.Residences
on new { id = t.idEmployee, crt = true} equals
new { id = r.idEmployee, crt = r.isCurrent }
into details
from o in details
select new
{
taskName = t.name,
taskDetails = t.details,
residenceStreetAdress = o.streetAddress,
residenceCity = o.city,
residenceDistrict = o.district
}));
When I run the query, it doesn't bring the Tasks with no Residence asigned. In TSQL I would have implemented left join. How can I solve this in linq?
Monday, September 13, 2010 5:26 PM