How to perform left outer join in C# LINQ to objects without using join-on-equals-into clauses? Is there any way to do that with where clause?
Correct problem:
For inner join is easy and I have a solution like this

can you give an example of what you're trying to achieve?
– jeroenhAug 4 '10 at 11:21

normal left outer join is something like this: var a = from b in bb join c in cc on b.bbbbb equals c.ccccc into dd from d in dd.DefaultIfEmpty() select b.sss; Mine question is there any way to do that witouth using join-on-equals-into clauses something like this var a = from b in bb from c in cc where b.bbb == c.cccc ... and so on...
– ToyAug 4 '10 at 11:32

1

sure there is, but you should post an example of your code you already have so people can give you a better answer
– slothAug 4 '10 at 11:34

If a database driven LINQ provider is used, a significantly more readable left outer join can be written as such:

from maintable in Repo.T_Whatever
from xxx in Repo.T_ANY_TABLE.Where(join condition).DefaultIfEmpty()

If you omit the DefaultIfEmpty() you will have an inner join.

Take the accepted answer:

from c in categories
join p in products on c equals p.Category into ps
from p in ps.DefaultIfEmpty()

This syntax is very confusing, and it's not clear how it works when you want to left join MULTIPLE tables.

Note
It should be noted that from alias in Repo.whatever.Where(condition).DefaultIfEmpty() is the same as an outer-apply/left-join-lateral, which any (decent) database-optimizer is perfectly capable of translating into a left join, as long as you don't introduce per-row-values (aka an actual outer apply). Don't do this in Linq-2-Objects (because there's no DB-optimizer when you use Linq-to-Objects).

When used with LINQ 2 SQL it will translate nicely to the following very legible SQL query:

SELECT
users.USR_ID AS UserId
,users.USR_User AS UserName
,groups.ID AS UserGroupId
,groups.Name AS GroupName
FROM T_User AS users
LEFT JOIN T_User_Group AS mappings
ON mappings.USRGRP_USR = users.USR_ID
LEFT JOIN T_Group AS groups
ON groups.GRP_ID == mappings.USRGRP_GRP

Also, If you're doing it in Linq-2-Objects (instead of Linq-2-SQL), you should do it the old-fashioned way (because LINQ to SQL translates this correctly to join operations, but over objects this method forces a full scan, and doesn't take advantage of index searches, whyever...):

LINQ to SQL translates this correctly to join operations. Over objects however this method forces a full scan, This is why the official documentation offers the group join solution that can take advantage of hashes to index searches.
– Tamir DanielyJan 5 '15 at 0:37

3

I think the syntax of explicit join is much more readable and clear than a where followed by DefaultIfEmpty
– FindOut_QuranSep 7 '15 at 3:05

Do you have to use .GroupJoin or can you also just use .Join?
– JessNov 3 '14 at 22:07

5

Both Join and GroupJoin don't really support left-join. The trick with using GroupJoin is that you can have empty groups and then translate those empty groups into empty values. DefaultIfEmpty simply does that, meaning Enumerable.Empty<Product>.DefaultIfEmpty() will return an IEnumerable with a single value of default(Product).
– Tamir DanielyJan 5 '15 at 0:44

Author probably meant to re-use r in the second from clause. i.e. from r in lrs.DefaultIfEmpty() Otherwise this query doesn't make much sense and probably doesn't even compile due to r being out of context for the select.
– Saeb AminiMay 31 '18 at 0:30

@Devart, when I read your query it reminded me of movie Clockwise with John Cleese, lol.
– Matas VaitkeviciusApr 18 at 4:30

From left into right into leftrights in rights on left in leftRights... Oh jeez... The syntax of using LEFT OUTER JOIN in LINQ really isn't clear, but these names really make it even more unclear.
– Mike GledhillMay 21 at 7:39

However here's an explanation that I hope will clarify what this actually means!

join b in beta on b.field1 equals a.field1 into b_temp

essentially creates a separate result set b_temp that effectively includes null 'rows' for entries on the right hand side (entries in 'b').

Then the next line:

from b_value in b_temp.DefaultIfEmpty()

..iterates over that result set, setting the default null value for the 'row' on the right hand side, and setting the result of the right hand side row join to the value of 'b_value' (i.e. the value that's on the right hand side,if there's a matching record, or 'null' if there isn't).

Now, if the right hand side is the result of a separate LINQ query, it will consist of anonymous types, which can only either be 'something' or 'null'. If it's an enumerable however (e.g. a List - where MyObjectB is a class with 2 fields), then it's possible to be specific about what default 'null' values are used for its properties:

This ensures that 'b' itself isn't null (but its properties can be null, using the default null values that you've specified), and this allows you to check properties of b_value without getting a null reference exception for b_value. Note that for a nullable DateTime, a type of (DateTime?) i.e. 'nullable DateTime' must be specified as the 'Type' of the null in the specification for the 'DefaultIfEmpty' (this will also apply to types that are not 'natively' nullable e.g double, float).

You can perform multiple left outer joins by simply chaining the above syntax.

from d in context.dc_tpatient_bookingd
join bookingm in context.dc_tpatient_bookingm
on d.bookingid equals bookingm.bookingid into bookingmGroup
from m in bookingmGroup.DefaultIfEmpty()
join patient in dc_tpatient
on m.prid equals patient.prid into patientGroup
from p in patientGroup.DefaultIfEmpty()

There are three tables: persons, schools and persons_schools, which connects persons to the schools they study in. A reference to the person with id=6 is absent in the table persons_schools. However the person with id=6 is presented in the result lef-joined grid.

"The following example does a group join between product and category. This is essentially the left join. The into expression returns data even if the category table is empty. To access the properties of the category table, we must now select from the enumerable result by adding the from cl in catList.DefaultIfEmpty() statement.

//Pretend a ClientCompany object and an Employee object both have a ClientCompanyID key on them
return DataContext.ClientCompany
.LeftJoin(DataContext.Employees, //Table being joined
company => company.ClientCompanyID, //First key
employee => employee.ClientCompanyID, //Second Key
company => new {company, employee = (Employee)null}, //Result selector when there isn't a match
(company, employee) => new { company, employee }); //Result selector when there is a match

EDIT:

In retrospect this may work, but it converts the IQueryable to an IEnumerable as morelinq does not convert the query to SQL.

Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).