How does linq decide between inner & outer joins

Question

Hi

Usually linq is using an left outer join for its queries but on some cases it decides to use inner join instead. I have a situation where that decision results in wrong results since the second table doesn't always have suitable records and that removes the records from the first table. I'm using a linqdatasource over a dbml where the relevant tables are identical but one holds historical records removed from the first. both have the same primary key. and I'm using a dataloadoption to load both tables at once with out round trips.

Would you explain why linq decided to use an inner join here?

Thanks

Answer 1

No, unfortunately you are incorrect. The LINQ Join operator always does an inner join. http://www.hookedonlinq.com/JoinOperator.ashx

If you want to do a left outer join, you need to use a combination of select or foreach. See these examples:

• http://solidcoding.blogspot.com/2007/12/left-outer-join-in-linq.html
• http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/c139313e-d745-4e1d-b3dc-ab355507eb48

or google it, there are a ton of examples out there.

Answer 2

It is better practice not to use the join operator where possible, and rely on traversing the relationships set up in the data context between the entities.

from r in Rabbits
select
{
    r.Name
    r.Parent.Name
}

This will automatically traverse the parent relationship and decide on the appropriate sql to run.

Using .DefaultIfEmpty() appropriately will mean that any NULL entries are added, hence it is translated into the appropriate outer join. This can be used with the join operator by including an 'into'.

http://stackoverflow.com/questions/1013031/join-and-left-join-equivalent-in-linq