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 lt JoinPair gt  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 lt  JoinPair gt  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

User · Answer

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

User · Answer

Here s an example if you need to join more than 2 tables   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     Ref  https   stackoverflow com a 17142392 2343

User · Answer

Simple solution for the LEFT OUTER JOIN   var setA   context SetA  var setB   context SetB Select st  gt st Id  Distinct   ToList    var leftOuter    setA Where stA  gt   setB Contains stA Id       notes     To improve performance SetB could be converted to a Dictionary  if that is done then you have to change this   setB Contains stA Id   or a HashSet When there is more than one field involved this could be achieve using Set operations and a class that implement  IEqualityComparer

User · Answer

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   List lt Person gt  persons   new List lt Person gt        new Person   id   1  name    Alex   phone    4235234         new Person   id   2  name    Bob   phone    0014352         new Person   id   3  name    Sam   phone    1345         new Person   id   4  name    Den   phone    3453452         new Person   id   5  name    Alen   phone    0353012         new Person   id   6  name    Simon   phone    0353012        List lt School gt  schools   new List lt School gt        new School   id   1  name    Saint  John s school        new School   id   2  name    Public School 200        new School   id   3  name    Public School 203       List lt PersonSchool gt  persons schools   new List lt PersonSchool gt        new PersonSchool id person   1  id school   1       new PersonSchool id person   2  id school   2       new PersonSchool id person   3  id school   3       new PersonSchool id person   4  id school   1       new PersonSchool id person   5  id school   2        a relation to the person with id 6 is absent     var query   from person in persons             join person school in persons schools on person id equals person school id person             into persons schools joined             from person school joined in persons schools joined DefaultIfEmpty               from school in schools Where var school   gt  person school joined    null   false   var school id    person school joined id school  DefaultIfEmpty               select new   Person   person name  School   school    null   String Empty   school name     foreach  var elem in query        System Console WriteLine   0   1    elem Person  elem School

User · Answer

I would like to add that if you get the MoreLinq extension there is now support for both homogenous and heterogeneous left joins now  http   morelinq github io 2 8 ref api html Overload MoreLinq MoreEnumerable LeftJoin htm  example     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   gt  company ClientCompanyID                     First key         employee   gt  employee ClientCompanyID                   Second Key         company   gt  new  company  employee    Employee null     Result selector when there isn t a match          company  employee    gt  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   You can instead use a GroupJoin as described here  https   stackoverflow com a 24273804 4251433  This will ensure that it stays as an IQueryable in case you need to do further logical operations on it later

User · Answer

Now as an extension method   public static class LinqExt       public static IEnumerable lt TResult gt  LeftOuterJoin lt TLeft  TRight  TKey  TResult gt  this IEnumerable lt TLeft gt  left  IEnumerable lt TRight gt  right  Func lt TLeft  TKey gt  leftKey  Func lt TRight  TKey gt  rightKey          Func lt TLeft  TRight  TResult gt  result                return left GroupJoin right  leftKey  rightKey   l  r    gt  new   l  r                  SelectMany                   o   gt  o r DefaultIfEmpty                      l  r    gt  new   lft  l l  rght   r                  Select o   gt  result Invoke o lft  o rght              Use like you would normally use join   var contents   list LeftOuterJoin list2                l   gt  l country                r   gt  r name               l  r    gt  new   count   l Count    l country  l reason  r people      Hope this saves you some time

User · Answer

Here is a fairly easy to understand version using method syntax   IEnumerable lt JoinPair gt  outerLeft       lefts SelectMany l   gt           rights Where r   gt  l Key    r Key                 DefaultIfEmpty new Item                   Select r   gt  new JoinPair   LeftId   l Id  RightId   r Id

User · Answer

from a in db Assignments      join b in db Deliveryboys on a AssignTo equals b EmployeeId           from d in eGroup DefaultIfEmpty        join  c in  db Deliveryboys on a DeliverTo equals c EmployeeId into eGroup2      from e in eGroup2 DefaultIfEmpty        where  a Collected    false       select new                 OrderId   a OrderId           DeliveryBoyID   a AssignTo           AssignedBoyName   b Name           Assigndate   a Assigndate           Collected   a Collected           CollectedDate   a CollectedDate           CollectionBagNo   a CollectionBagNo           DeliverTo   e    null    Null    e Name           DeliverDate   a DeliverDate           DeliverBagNo   a DeliverBagNo           Delivered   a Delivered

User · Answer

Perform left outer joins in linq C     Perform left outer joins  class Person       public string FirstName   get  set        public string LastName   get  set       class Child       public string Name   get  set        public Person Owner   get  set      public class JoinTest       public static void LeftOuterJoinExample                 Person magnus   new Person   FirstName    Magnus   LastName    Hedlund             Person terry   new Person   FirstName    Terry   LastName    Adams             Person charlotte   new Person   FirstName    Charlotte   LastName    Weiss             Person arlene   new Person   FirstName    Arlene   LastName    Huff              Child barley   new Child   Name    Barley   Owner   terry            Child boots   new Child   Name    Boots   Owner   terry            Child whiskers   new Child   Name    Whiskers   Owner   charlotte            Child bluemoon   new Child   Name    Blue Moon   Owner   terry            Child daisy   new Child   Name    Daisy   Owner   magnus                Create two lists          List lt Person gt  people   new List lt Person gt    magnus  terry  charlotte  arlene            List lt Child gt  childs   new List lt Child gt    barley  boots  whiskers  bluemoon  daisy             var query   from person in people                     join child in childs                     on person equals child Owner into gj                     from subpet in gj DefaultIfEmpty                       select new                                               person FirstName                          ChildName   subpet  null  subpet Name  No Child                                                   PetName   subpet  Name    String Empty             foreach  var v in query                        Console WriteLine    v FirstName       -25  v ChildName                             This code produces the following output                Magnus         Daisy        Terry          Barley        Terry          Boots        Terry          Blue Moon        Charlotte      Whiskers        Arlene         No Child   https   dotnetwithhamid blogspot in

User · Answer

Easy way is to use Let keyword  This works for me   from AItem in Db A Let BItem   Db B Where x   gt  x id    AItem id   FirstOrDefault    Where SomeCondition Select new YourViewModel       X1   AItem a      X2   AItem b      X3   BItem c     This is a simulation of Left Join  If each item in B table not match to A item   BItem return null

User · Answer

Overview  In this code snippet  I demonstrate how to group by ID where Table1 and Table2 have a one to many relationship   I group on Id  Field1  and Field2    The subquery is helpful  if a third Table lookup is required and it would have required a left join relationship  I show a left join grouping and a subquery linq   The results are equivalent   class MyView   public integer Id  get set       public String Field1   get set   public String Field2  get set       public String SubQueryName  get set                                 IList lt MyView gt  list   await  from ci in  dbContext Table1                                                join cii in  dbContext Table2                                                    on ci Id equals cii Id                                                 where ci Field1    criterion                                                group new                                                                                                     ci Id                                                  by new   ci Id  cii Field1  ci Field2                                              into pg                                                select new MyView                                                                                                     Id   pg Key Id                                                     Field1   pg Key Field1                                                     Field2   pg Key Field2                                                     SubQueryName                                                      from chv in  dbContext Table3 where chv Id  pg Key Id select chv Field1  FirstOrDefault                                                     ToListAsync lt MyView gt        Compared to using a Left Join and Group new  IList lt MyView gt  list   await  from ci in  dbContext Table1                                                join cii in  dbContext Table2                                                    on ci Id equals cii Id                         join chv in  dbContext Table3                                                   on cii Id equals chv Id into lf chv                                                 from chv in lf chv DefaultIfEmpty                                                   where ci Field1    criterion                                                group new                                                                                                     ci Id                                                  by new   ci Id  cii Field1  ci Field2  chv FieldValue                                              into pg                                                select new MyView                                                                                                     Id   pg Key Id                                                     Field1   pg Key Field1                                                     Field2   pg Key Field2                                                     SubQueryName pg Key FieldValue                                                   ToListAsync lt MyView gt

User · Answer

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   gt  mapping USRGRP USR    users USR ID            DefaultIfEmpty       lt    makes join left join     from groups in Repo T Group           Where gruppe   gt  gruppe GRP ID    mappings USRGRP GRP            DefaultIfEmpty       lt    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  quot  Convert SQL Server query to Linq query  quot  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

User · Answer

class Program       List lt Employee gt  listOfEmp   new List lt Employee gt         List lt Department gt  listOfDepart   new List lt Department gt          public Program                 listOfDepart   new List lt Department gt                 new Department   Id   1  DeptName    DEV                 new Department   Id   2  DeptName    QA                 new Department   Id   3  DeptName    BUILD                 new Department   Id   4  DeptName    SIT                         listOfEmp   new List lt Employee gt                 new Employee   Empid   1  Name    Manikandan  DepartmentId 1                new Employee   Empid   2  Name    Manoj   DepartmentId 1               new Employee   Empid   3  Name    Yokesh   DepartmentId 0               new Employee   Empid   3  Name    Purusotham  DepartmentId 0                        static void Main string   args                Program ob   new Program            ob LeftJoin            Console ReadLine               private void LeftJoin                 listOfEmp GroupJoin listOfDepart DefaultIfEmpty    x   gt  x DepartmentId  y   gt  y Id   x  y    gt  new   EmpId   x Empid  EmpName   x Name  Dpt   y FirstOrDefault      null   y FirstOrDefault   DeptName   null    ToList   ForEach              z   gt                                Console WriteLine  Empid  0  EmpName  1  Dept  2    z EmpId  z EmpName  z Dpt                            class Employee       public int Empid   get  set        public string Name   get  set        public int DepartmentId   get  set       class Department       public int Id   get  set        public string DeptName   get  set        OUTPUT

User · Answer

This is a SQL syntax compare to LINQ syntax for inner and left outer joins   Left Outer Join   http   www ozkary com 2011 07 linq-to-entity-inner-and-left-joins html   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

User · Answer

Extension method that works like left join with Join syntax  public static class LinQExtensions       public static IEnumerable lt TResult gt  LeftJoin lt TOuter  TInner  TKey  TResult gt           this IEnumerable lt TOuter gt  outer  IEnumerable lt TInner gt  inner           Func lt TOuter  TKey gt  outerKeySelector           Func lt TInner  TKey gt  innerKeySelector           Func lt TOuter  TInner  TResult gt  resultSelector                return outer GroupJoin              inner               outerKeySelector               innerKeySelector               outerElement  innerElements    gt  resultSelector outerElement  innerElements FirstOrDefault                just wrote it in  NET core and it seems to be working as expected   Small test           var Ids   new List lt int gt    1  2  3  4           var items   new List lt Tuple lt int  string gt  gt                        new Tuple lt int  string gt  1  a                new Tuple lt int  string gt  2  b                new Tuple lt int  string gt  4  d                new Tuple lt int  string gt  5  e                        var result   Ids LeftJoin              items              id   gt  id              item   gt  item Item1               id  item    gt  item    new Tuple lt int  string gt  id   not found              result ToList           Count   4          0     1  a            1     2  b            2     3  not found            3     4  d

User · Answer

take look at this example   class Person       public int ID   get  set        public string FirstName   get  set        public string LastName   get  set        public string Phone   get  set       class Pet       public string Name   get  set        public Person Owner   get  set       public static void LeftOuterJoinExample         Person magnus   new Person  ID   1  FirstName    Magnus   LastName    Hedlund        Person terry   new Person  ID   2  FirstName    Terry   LastName    Adams        Person charlotte   new Person  ID   3  FirstName    Charlotte   LastName    Weiss        Person arlene   new Person  ID   4  FirstName    Arlene   LastName    Huff         Pet barley   new Pet  Name    Barley   Owner   terry       Pet boots   new Pet  Name    Boots   Owner   terry       Pet whiskers   new Pet  Name    Whiskers   Owner   charlotte       Pet bluemoon   new Pet  Name    Blue Moon   Owner   terry       Pet daisy   new Pet  Name    Daisy   Owner   magnus           Create two lists      List lt Person gt  people   new List lt Person gt   magnus  terry  charlotte  arlene       List lt Pet gt  pets   new List lt Pet gt   barley  boots  whiskers  bluemoon  daisy        var query   from person in people         where person ID    4         join pet in pets on person equals pet Owner  into personpets         from petOrNull in personpets DefaultIfEmpty           select new   Person person  Pet   petOrNull           foreach  var v in query                 Console WriteLine   0 -15  1    v Person FirstName         v Pet    null    Does not Exist    v Pet Name                This code produces the following output        Magnus         Daisy    Terry          Barley    Terry          Boots    Terry          Blue Moon    Charlotte      Whiskers    Arlene    now you are able to include elements from the left even if that element has no matches in the right  in our case we retrived Arlene even he has no matching in the right  here is the reference   How to  Perform Left Outer Joins  C  Programming Guide

User · Answer

This is the general form  as already provided in other answers   var c       from a in alpha     join b in beta on b field1 equals a field1 into b temp     from b value in b temp DefaultIfEmpty       select new   Alpha   a  Beta   b value      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   var c       from a in alpha     join b in beta on b field1 equals a field1 into b temp     from b value in b temp DefaultIfEmpty  new MyObjectB   Field1   String Empty  Field2    DateTime   null        select new   Alpha   a  Beta field1   b value Field1  Beta field2   b value Field2      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

User · Answer

If you need to join and filter on something  that can be done outside of the join   Filter can be done after creating the collection    In this case if I do this in the join condition I reduce the rows that are returned    Ternary condition is used     n    null          n MonDayNote     If the object is null  so no match   then return what is after the        in this case  Else  return what is after the    n MonDayNote    Thanks to the other contributors that is where I started with my own issue             var schedLocations    from f in db RAMS REVENUE LOCATIONS               join n in db RAMS LOCATION PLANNED MANNING on f revenueCenterID equals                    n revenueCenterID into lm                from n in lm DefaultIfEmpty                  join r in db RAMS LOCATION SCHED NOTE on f revenueCenterID equals r revenueCenterID               into locnotes                from r in locnotes DefaultIfEmpty                 where f LocID    nLocID  amp  amp  f In Use    true  amp  amp  f revenueCenterID  gt  1000                orderby f Areano ascending  f Locname ascending               select new                                   Facname   f Locname                    f Areano                    f revenueCenterID                    f Locabbrev                         MonNote   n    null          n MonDayNote                    MonNote   n    null          n MonDayNote                    TueNote   n    null          n TueDayNote                    WedNote   n    null          n WedDayNote                    ThuNote   n    null          n ThuDayNote                     FriNote   n    null          n FriDayNote                    SatNote   n    null          n SatDayNote                    SunNote   n    null          n SunDayNote                    MonEmpNbr   n    null   0   n MonEmpNbr                    TueEmpNbr   n    null   0   n TueEmpNbr                    WedEmpNbr   n    null   0   n WedEmpNbr                    ThuEmpNbr   n    null   0   n ThuEmpNbr                    FriEmpNbr   n    null   0   n FriEmpNbr                    SatEmpNbr   n    null   0   n SatEmpNbr                    SunEmpNbr   n    null   0   n SunEmpNbr                    SchedMondayDate   n    null   dMon   n MondaySchedDate                    LocNotes   r    null    Notes  N A    r LocationNote                   ToList                    Func lt int  string gt  LambdaManning    x    gt    return x    0         Manning     x ToString               DataTable dt ScheduleMaster   PsuedoSchedule Tables  ScheduleMasterWithNotes            var schedLocations2   schedLocations Where x   gt  x SchedMondayDate    dMon

User · Answer

As per my answer to a similar question  here   Linq to SQL left outer join using Lambda syntax and joining on 2 columns  composite join key   Get the code here  or clone my github repo  and play   Query           var petOwners               from person in People             join pet in Pets             on new                               person Id                  person Age                            equals new                               pet Id                  Age   pet Age   2     owner is twice age of pet                           into pets             from pet in pets DefaultIfEmpty               select new PetOwner                               Person   person                  Pet   pet                   Lambda           var petOwners   People GroupJoin              Pets              person   gt  new   person Id  person Age                pet   gt  new   pet Id  Age   pet Age   2                 person  pet    gt  new                               Person   person                  Pets   pet                 SelectMany              pet   gt  pet Pets DefaultIfEmpty                 people  pet    gt  new                               people Person                  Pet   pet

User · Answer

An implementation of left outer join by extension methods could look like  public static IEnumerable lt Result gt  LeftJoin lt TOuter  TInner  TKey  Result gt     this IEnumerable lt TOuter gt  outer  IEnumerable lt TInner gt  inner     Func lt TOuter  TKey gt  outerKeySelector  Func lt TInner  TKey gt  innerKeySelector     Func lt TOuter  TInner  Result gt  resultSelector  IEqualityComparer lt TKey gt  comparer          if  outer    null        throw new ArgumentException  outer         if  inner    null        throw new ArgumentException  inner         if  outerKeySelector    null        throw new ArgumentException  outerKeySelector         if  innerKeySelector    null        throw new ArgumentException  innerKeySelector         if  resultSelector    null        throw new ArgumentException  resultSelector         return LeftJoinImpl outer  inner  outerKeySelector  innerKeySelector  resultSelector  comparer    EqualityComparer lt TKey gt  Default          static IEnumerable lt Result gt  LeftJoinImpl lt TOuter  TInner  TKey  Result gt         IEnumerable lt TOuter gt  outer  IEnumerable lt TInner gt  inner         Func lt TOuter  TKey gt  outerKeySelector  Func lt TInner  TKey gt  innerKeySelector         Func lt TOuter  TInner  Result gt  resultSelector  IEqualityComparer lt TKey gt  comparer          var innerLookup   inner ToLookup innerKeySelector  comparer        foreach  var outerElment in outer              var outerKey   outerKeySelector outerElment         var innerElements   innerLookup outerKey          if  innerElements Any            foreach  var innerElement in innerElements            yield return resultSelector outerElment  innerElement         else         yield return resultSelector outerElment  default TInner                  The resultselector then has to take care of the null elements  Fx      static void Main string   args            var inner   new     Tuple Create 1   1    Tuple Create 2   2    Tuple Create 3   3           var outer   new     Tuple Create 1   11    Tuple Create 2   22            var res   outer LeftJoin inner  item   gt  item Item1  item   gt  item Item1   it1  it2    gt       new   Key   it1 Item1  V1   it1 Item2  V2   it2    null   it2 Item2   default string            foreach  var item in res         Console WriteLine string Format   0    1    2    item Key  item V1  item V2

User · Answer

Using lambda expression  db Categories        GroupJoin db Products        Category   gt  Category CategoryId        Product   gt  Product CategoryId         x  y    gt  new   Category   x  Products   y       SelectMany        xy   gt  xy Products DefaultIfEmpty           x  y    gt  new   Category   x Category  Product   y       Select s   gt  new           CategoryName   s Category Name             ProductName   s Product Name

User · Answer

Take a look at this example  This query should work   var leftFinal   from left in lefts                 join right in rights on left equals right Left into leftRights                 from leftRight in leftRights DefaultIfEmpty                   select new   LeftId   left Id  RightId   left Key  leftRight Key   leftRight Id   0

[c#] LEFT OUTER JOIN in LINQ

Examples related to c#

Examples related to linq

Examples related to join