Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LEFT OUTER JOIN in LINQ

Tags:

c#

join

linq

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; } 
like image 409
Toy Avatar asked Aug 04 '10 11:08

Toy


People also ask

How Use left and right join in LINQ?

A Left Outer join returns all records from the left table and the matching record from the right table. If there are no matching records in the right table then it returns null. If we want to do a Left Outer join in LINQ then we must use the keyword "into" and method "DefaultIfEmpty".

Which join is valid in LINQ?

LINQ Inner Join Inner Join produces the result from two or more than two tables. So, basically we are meant to get the records from both tables based on matching conditions. Basically in SQL, we use the INNER JOIN keyword to make relationship between both tables. The following is the Linq query for above SQL query.

What is left outer join?

A left outer join is a method of combining tables. The result includes unmatched rows from only the table that is specified before the LEFT OUTER JOIN clause. If you are joining two tables and want the result set to include unmatched rows from only one table, use a LEFT OUTER JOIN clause or a RIGHT OUTER JOIN clause.

Is LINQ join inner or outer?

One commonly used feature of Language-Integrated Query (LINQ) is the facility to combine two sequences of related data using joins. The standard join operation provides an inner join but with a minor modification can be changed to give a left outer join.


2 Answers

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 }; 
like image 181
ajay_whiz Avatar answered Oct 19 '22 19:10

ajay_whiz


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     }  ); 
like image 21
Stefan Steiger Avatar answered Oct 19 '22 18:10

Stefan Steiger