none
Can't get the linq query to show all records, inner join not outer join RRS feed

  • Question

  • Hi

    I am trying to run the following code.  I have included a join statement.  I am using the join statement to restrict the returned list of products so the results show those products with a corresponding organisation if the organisation_id is set.  It should show all the products if the organisation_id is 0.  The join seems to be doing an inner join and not an outer join, I think!

    Can someone help

     

    int organisationId = 0;
    string brand = string.Empty;

    var query =
        
    from prods in Products
        
    join orgs in Organisation_relateds on prods equals orgs.Product
        
    where ((orgs.Organisation_id == 57 || organisationId ==0)
            && prods.Brand == brand || brand ==
    string.Empty)
        
    select prods;

    query.Dump();

    (i was using linqpad by the way)

    thanks in advance...

    Thursday, November 5, 2009 3:31 PM

Answers

  • Twistedinferno is almost there just have the where line specify jo.Organisation_id (not just o as it went through j and jo names)

    [)amien
    Thursday, November 5, 2009 4:15 PM
    Moderator
  • Hi twistedinferno,

     

    Glad to see you again!

     

    As Damien said, you almost solved the question on your own, just modify o to jo.  J 

     

    As your first post said, the join by default is an INNER JOIN.  To perform a LEFT OUTER JOIN via LINQ to SQL, we can use DefaultIfEmpty extension method.  In the current scenario, you are selecting the product at last.  But sometimes, we may encounter some default value issues since some non-nullable types at the client side are not allowed to be set to null.  We may use some operators like ? : or ?? to avoid such problems. 

     

    Some additional references:

    http://bhaidar.net/cs/archive/2007/08/01/left-outer-join-in-linq-to-sql.aspx

    http://www.hookedonlinq.com/OuterJoinSample.ashx

     

     

    Have a nice weekend, twistedinferno and Damien!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, November 6, 2009 6:58 AM
    Moderator
  • thanks

    i was getting some duplicates in the results so I put a distinct around the whole query...

    organisationId = 0;
    string brand = string
    .Empty;

    var
    query =
        (
    from p in
    Products
        
    join o in Organisation_relateds on p equals
    o.Product
        
    into
    j
        
    from jo in
    j.DefaultIfEmpty()
        
    where (jo.Organisation_id == 57
    )
        
    select
    p).Distinct;

    query.Dump();

    Thanks all

    Friday, November 6, 2009 2:18 PM

All replies

  • oh, & I tried the following too as I thought that might work but got an 'o does not exist in the current context'.

    int

     

     

    organisationId = 0;
    string brand = string.Empty;

    var query =
        
    from p in Products
        
    join o in Organisation_relateds on p equals o.Product
        
    into j
        
    from jo in j.DefaultIfEmpty()
        
    where (o.Organisation_id == 57)
        
    select p;

    query.Dump();

    Thursday, November 5, 2009 3:41 PM
  • Twistedinferno is almost there just have the where line specify jo.Organisation_id (not just o as it went through j and jo names)

    [)amien
    Thursday, November 5, 2009 4:15 PM
    Moderator
  • Hi twistedinferno,

     

    Glad to see you again!

     

    As Damien said, you almost solved the question on your own, just modify o to jo.  J 

     

    As your first post said, the join by default is an INNER JOIN.  To perform a LEFT OUTER JOIN via LINQ to SQL, we can use DefaultIfEmpty extension method.  In the current scenario, you are selecting the product at last.  But sometimes, we may encounter some default value issues since some non-nullable types at the client side are not allowed to be set to null.  We may use some operators like ? : or ?? to avoid such problems. 

     

    Some additional references:

    http://bhaidar.net/cs/archive/2007/08/01/left-outer-join-in-linq-to-sql.aspx

    http://www.hookedonlinq.com/OuterJoinSample.ashx

     

     

    Have a nice weekend, twistedinferno and Damien!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, November 6, 2009 6:58 AM
    Moderator
  • thanks

    i was getting some duplicates in the results so I put a distinct around the whole query...

    organisationId = 0;
    string brand = string
    .Empty;

    var
    query =
        (
    from p in
    Products
        
    join o in Organisation_relateds on p equals
    o.Product
        
    into
    j
        
    from jo in
    j.DefaultIfEmpty()
        
    where (jo.Organisation_id == 57
    )
        
    select
    p).Distinct;

    query.Dump();

    Thanks all

    Friday, November 6, 2009 2:18 PM