locked
How to do join on multiple conditions RRS feed

  • Question

  • Hi,

    I have 2 tables,

    Result: ResultID

    ResultResource: ResourceID, ResultID

    I want to select all rows in Result whose ResultID equals those in ResultResource that have same ResourceID as an integer. The linq query is like,          

    int resourceId; var results = (from r in Results                          

    join s in ResultResources                          

    on new { resourceId, r.ResultID } equals new { s.ResourceID, s.ResultID }                          

    select r).ToList();

    But it gets compiler error "The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'". What is the problem? How to do the linq query like this? Thanks.



    York

    Sunday, July 1, 2012 10:03 AM

Answers

  • Hi York;

    In order to join on multiple values as you have in your query the anonymous types must be of the same type. In your query the two anonymous types are of different types and the reason for the error, "The type of one of the expressions in the join clause is incorrect", to correct this issue you need to make both anonymous type of the same type and this can be done by giving the anonymous types property names and the properties in each must be in the same order. Please see the modified query below. 

    int resourceId;
    var results = (from r in Results
                   join s in ResultResources on 
                        new { ResourceID = resourceId, ResultID = r.ResultID } equals
                        new { ResourceID = s.ResourceID, ResultID = s.ResultID }
                   select r).ToList();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by york Z Tuesday, July 3, 2012 12:03 AM
    Sunday, July 1, 2012 1:27 PM
  • Hi York;

    If you copied and past the query from my post then the only thing I can think now is that the corresponding variables of each anonymous type is not of the same type. For example if RecourseID in one anonymous type is a integer and the other is a string then you will also see that error. If you did not copy and past the above query then please post the query as you implemented it in your code.

    To your question, "Is it possible to do it in "and" for join?", not sure what you mean by this can you give a code example like you would write it?


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by york Z Tuesday, July 3, 2012 12:03 AM
    Monday, July 2, 2012 10:09 PM

All replies

  • Hi York;

    In order to join on multiple values as you have in your query the anonymous types must be of the same type. In your query the two anonymous types are of different types and the reason for the error, "The type of one of the expressions in the join clause is incorrect", to correct this issue you need to make both anonymous type of the same type and this can be done by giving the anonymous types property names and the properties in each must be in the same order. Please see the modified query below. 

    int resourceId;
    var results = (from r in Results
                   join s in ResultResources on 
                        new { ResourceID = resourceId, ResultID = r.ResultID } equals
                        new { ResourceID = s.ResourceID, ResultID = s.ResultID }
                   select r).ToList();

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by york Z Tuesday, July 3, 2012 12:03 AM
    Sunday, July 1, 2012 1:27 PM
  • After change, it still gets error "The type of one of the expressions in the join clause is incorrect".  Is it possible to do it in "and" for join?


    York

    Monday, July 2, 2012 9:37 PM
  • Hi York;

    If you copied and past the query from my post then the only thing I can think now is that the corresponding variables of each anonymous type is not of the same type. For example if RecourseID in one anonymous type is a integer and the other is a string then you will also see that error. If you did not copy and past the above query then please post the query as you implemented it in your code.

    To your question, "Is it possible to do it in "and" for join?", not sure what you mean by this can you give a code example like you would write it?


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by york Z Tuesday, July 3, 2012 12:03 AM
    Monday, July 2, 2012 10:09 PM
  • In SQL query, it can be done like,

    Select r from Results Join s in ResultResources On (r.ResultID = s.ResultID) AND (s.ResourceID = resourceId)

    In other word, multiple condition can be done in AND operator.  I try like,

    var results = (from r in Results
                   join s in ResultResources on 
                        (s.ResourceID equals resourceId ) && 
                        (s.ResultID equals r.ResultID )
                   select r).ToList();
    But not working. How to do it exactly in Linq? Thanks.


    York


    • Edited by york Z Monday, July 2, 2012 10:36 PM
    Monday, July 2, 2012 10:35 PM
  •  

    No you can't use AND in Linq you must use the && operator.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, July 2, 2012 10:58 PM
  • Thanks a lot Fernando, s.ResourceID is int?, not int, and that is the reason.

    York

    Tuesday, July 3, 2012 12:04 AM
  •  

    Not a problem York, glad I was able to help. 

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, July 3, 2012 12:46 AM