none
Linq: Problem with Multiple joins on composite key and where clause RRS feed

  • Question

  • Hi, I am new to LINQ, I am trying to make a multiple join in 3 tables. It works as long as the join condition has one item, but if the join condition has more than one item then it fails to generate SQL.

    Following is the code that works 

     

                var query =     (from header in headerTable

                                    join details in detailTable

                                        on header.OrderId  equals details.OrderId                      

           join trans in transactions                                

      on header.OrderId  equals trans.OrderId

                                    where header.Client == "EN"                

                       && header.OrderId == 123456789

                                    select new { header.Client, header.OrderId, header.Status });

     

    But when I change to multipe join consditions as follows it fails!

     

                var query =     (from header in headerTable

                                    join details in detailTable

                                         on new { header.OrderId, header.Client } equals new { details.OrderId, details.Client }                        

            join trans in transactions                                

       on new { details.OrderId, details.Client } equals new { trans.OrderId, trans.Client }

                                     where header.Client == "EN"                

                        && header.OrderId == 123456789

                                    select new { header.Client, header.OrderId, header.Status });

     

    Any solutions to the problem?

     


    Haris Asghar
    Friday, December 10, 2010 3:55 PM

Answers

  • Hi Haris,

    I can't see anything immediately wrong with the code, though I do notice a subtle difference between your two examples; the first is joining transactions to header, whilst the second is joining transactions to details. That said, I am presuming that this is NOT the cause of your problem.

    In the meantime, could you explain a bit more about the relationships between these entities; e.g. how many transactions does a detail have.

    Also could you expand about the nature of the failure when you say that "it fails to generate SQL". Does it cause an exception or does it not return any data?

    Thanks,

    John.

    • Marked as answer by liurong luo Monday, December 20, 2010 10:42 AM
    Wednesday, December 15, 2010 3:22 PM

All replies

  •  

    Hi Haris Asghar,

     

    Thanks for posting in MSDN forum,  I am Jackie.

     

    I think you can submit your suggestions to Microsoft Connect feedback portal

    http://connect.microsoft.com ,Microsoft engineers will evaluate them seriously, thanks.

    If this issue is urgent, please contact support at http://support.microsoft.com .


    Best Regards,

     

     


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, December 15, 2010 10:10 AM
    Moderator
  • Hi Haris,

    I can't see anything immediately wrong with the code, though I do notice a subtle difference between your two examples; the first is joining transactions to header, whilst the second is joining transactions to details. That said, I am presuming that this is NOT the cause of your problem.

    In the meantime, could you explain a bit more about the relationships between these entities; e.g. how many transactions does a detail have.

    Also could you expand about the nature of the failure when you say that "it fails to generate SQL". Does it cause an exception or does it not return any data?

    Thanks,

    John.

    • Marked as answer by liurong luo Monday, December 20, 2010 10:42 AM
    Wednesday, December 15, 2010 3:22 PM
  • Both queries are doing joins but you never use the joined values theirfore they wouldn't be included.

    You may want to add where Details != null or something.

     

    Thursday, December 16, 2010 10:07 PM