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 Snippet

from p in persons
 join w in workers on p.LastName equals w.LastName and p.FirstName == w.FirstName

 

As it's not compile, I do

Code Snippet

from p in persons
  join w in workers on p.LastName equals w.LastName
  where p.FirstName == w.FirstName

 

But first code should be better.

 

What is the syntax to do this?

 

Thanks,

Matthieu

Answer 1

Just use an anonymous type to combine the fields you want to join on:

 

Code Snippet

from p in persons

join w in workers

on new { p.FirstName, p.LastName } equals new { w.FirstName, w.LastName }

select ...;

 

Anders

Answer 2

Thanks

Answer 3

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

Answer 4

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'. 

 

So, is there a way to do these type of joins using the native LINQ operators? Or must I do it all in the where clause?

 

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

Answer 5

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

Answer 6

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 Snippet

var 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

Answer 7

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.

Answer 8

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();

 

 

 

 

Answer 9

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,

Answer 10

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 Snippet

SELECT [t2].[ProjectID]

FROM [userdept] AS [t0]

INNER JOIN ( [userdist] AS [t1]

INNER JOIN ( [BSProjects_cap] AS [t2]

INNER JOIN [Dept_Agency_reg] AS [t3] ON [t2].[DeptID] = [t3].[DeptID]

INNER JOIN [Districts_reg] AS [t4] ON [t2].[District] = [t4].[ShortName]  )

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] = 15

 

 

 

What would be an optimum way to turn the above SQL into LINQ? Thanks in advance for any response.

Answer 11

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 Snippet

var 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 Snippet

var 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

 

 

 

Answer 12

 

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 Snippet

from 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}

 

 

Answer 13

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.

Answer 14

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 Snippet

var 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());

 

 

Answer 15

Check the following post

http://sivakrishnakuchi.blogspot.com/2010/04/linq-query-for-multiple-join-conditions.html

Answer 16

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?