LEFT OUTER JOIN in LINQ

Question

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

List<JoinPair> innerFinal = (from l in lefts from r in rights where l.Key == r.Key                              select new JoinPair { LeftId = l.Id, RightId = r.Id}) 

but for left outer join I need a solution. Mine is something like this but it's not working

List< JoinPair> leftFinal = (from l in lefts from r in rights                              select new JoinPair {                                              LeftId = l.Id,                                              RightId = ((l.Key==r.Key) ? r.Id : 0                                         }) 

where JoinPair is a class:

public class JoinPair { long leftId; long rightId; } 

Answer 1

As stated on:

101 LINQ Samples - Left outer join

var q =     from c in categories     join p in products on c.Category equals p.Category into ps     from p in ps.DefaultIfEmpty()     select new { Category = c, ProductName = p == null ? "(No products)" : p.ProductName }; 

Answer 2

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

Detailed Example

var query2 = (     from users in Repo.T_User     from mappings in Repo.T_User_Group          .Where(mapping => mapping.USRGRP_USR == users.USR_ID)          .DefaultIfEmpty() // <== makes join left join     from groups in Repo.T_Group          .Where(gruppe => gruppe.GRP_ID == mappings.USRGRP_GRP)          .DefaultIfEmpty() // <== makes join left join      // where users.USR_Name.Contains(keyword)     // || mappings.USRGRP_USR.Equals(666)       // || mappings.USRGRP_USR == 666      // || groups.Name.Contains(keyword)      select new     {          UserId = users.USR_ID         ,UserName = users.USR_User         ,UserGroupId = groups.ID         ,GroupName = groups.Name     }  );   var xy = (query2).ToList(); 

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 

Edit:

See also " Convert SQL Server query to Linq query " for a more complex example.

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

    var query2 = (     from users in Repo.T_Benutzer     join mappings in Repo.T_Benutzer_Benutzergruppen on mappings.BEBG_BE equals users.BE_ID into tmpMapp     join groups in Repo.T_Benutzergruppen on groups.ID equals mappings.BEBG_BG into tmpGroups     from mappings in tmpMapp.DefaultIfEmpty()     from groups in tmpGroups.DefaultIfEmpty()     select new     {          UserId = users.BE_ID         ,UserName = users.BE_User         ,UserGroupId = mappings.BEBG_BG         ,GroupName = groups.Name     }  );