locked
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

    Wednesday, May 16, 2007 10:45 PM

Answers

  • 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

    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 Snippet

    from 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
  • Thanks
    Thursday, 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 04, 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'. 

     

    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

    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! Smile 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

    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 05, 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 05, 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 06, 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 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.

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

     

     

     

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

     

     


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

     

     

    Wednesday, October 01, 2008 7:34 PM
  • 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