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; }
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".
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.
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.
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.
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 };
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 } );
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With