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

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

Answer 1

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

Answer 2

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

Answer 3

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 [e-mail]

---

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.

Answer 4

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