Ufyukyu

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; }

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... — Aug 4 '10 at 11:32
sure there is, but you should post an example of your code you already have so people can give you a better answer — Aug 4 '10 at 11:34
I was looking for a "Left excluding" JOIN (and I confused it with the concept of "OUTER"). This answer was closer to what I wanted. — Jun 5 '17 at 16:53

Arithmomaniac 2,83212749 · Accepted Answer · 2017-05-16 13:58:00Z

522

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 };

edited May 16 '17 at 13:58

Arithmomaniac

2,83212749

answered Aug 5 '10 at 10:15

ajay_whiz

10.7k32943

7

I'm trying the same thing but getting an error on the join operator, which says "The type of one of the expressions in the join clause is incorrect."

– Badhon Jain
Jan 8 '14 at 8:52

3

@jain if your types are different the join will not work. So likely your keys are of different datatypes. Are both keys int for example?

– Yooakim
Feb 2 '14 at 17:42

2

Whats the solution Jain? I am also facing the same error and the types are same in my case as well.

– Sandeep
Oct 31 '14 at 16:47

1

@Sandeep check your keys where you joined it. Suppose if those are of types string and int then just convert string key to int.

– Ankitkumar Bhatt
Jul 27 '16 at 9:35

2

updated link: 101 LINQ Samples - Left outer join

– BukeMan
May 5 '17 at 16:28

|
show 5 more comments

jpmc26 15k758105 · Accepted Answer · 2019-01-10 00:38:43Z

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

    }



);

This answer is actually helpful. Thank you for actually offering syntax that is understandable. — Oct 23 '14 at 15:01
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. — Jan 5 '15 at 0:37
I think the syntax of explicit join is much more readable and clear than a where followed by DefaultIfEmpty — Sep 7 '15 at 3:05

N RockingN Rocking 1,69721423 · Accepted Answer · 2014-02-05 18:01:14Z

108

Using lambda expression

db.Categories    

  .GroupJoin(

      db.Products,

      Category => Category.CategoryId,

      Product => Product.CategoryId,

      (x, y) => new { Category = x, Products = y })

  .SelectMany(

      xy => xy.Products.DefaultIfEmpty(),

      (x, y) => new { Category = x.Category, Product = y })

  .Select(s => new

  {

      CategoryName = s.Category.Name,     

      ProductName = s.Product.Name   

  })

answered Feb 5 '14 at 18:01

N Rocking

1,69721423

Do you have to use .GroupJoin or can you also just use .Join?

– Jess
Nov 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 Daniely
Jan 5 '15 at 0:44

43

All this to perform a left join??

– FindOut_Quran
Sep 7 '15 at 3:07

6

Thanks for this! Not too many lambda expression examples out there, this worked for me.

– Johan Henkens
Jan 21 '16 at 22:43

1

Thanks for the answer. It yielded the closest thing to the raw SQL LEFT OUTER JOIN I've written over the years

– John Gathogo
Feb 8 '17 at 6:00

|
show 1 more comment

Nielsvh 84111529 · Accepted Answer · 2018-07-14 06:13:46Z

37

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 };

edited Jul 14 '18 at 6:13

Nielsvh

84111529

answered Aug 5 '10 at 10:11

Devart

99.3k19135160

3

Can r be accessed in the select clause after using a join into?

– Farhad Alizadeh Noori
Jun 2 '14 at 21:48

@FarhadAlizadehNoori Yes It can.

– Po-ta-toe
Oct 12 '16 at 7:56

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 Amini
May 31 '18 at 0:30

add a comment |

Matas VaitkeviciusMatas Vaitkevicius 34.3k16168176 · Accepted Answer · 2016-08-18 13:54:10Z

Now as an extension method:

public static class LinqExt

{

    public static IEnumerable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(this IEnumerable<TLeft> left, IEnumerable<TRight> right, Func<TLeft, TKey> leftKey, Func<TRight, TKey> rightKey,

        Func<TLeft, TRight, TResult> result)

    {

        return left.GroupJoin(right, leftKey, rightKey, (l, r) => new { l, r })

             .SelectMany(

                 o => o.r.DefaultIfEmpty(),

                 (l, r) => new { lft= l.l, rght = r })

             .Select(o => result.Invoke(o.lft, o.rght));

    }

}

Use like you would normally use join:

var contents = list.LeftOuterJoin(list2, 

             l => l.country, 

             r => r.name,

            (l, r) => new { count = l.Count(), l.country, l.reason, r.people })

Hope this saves you some time.

Console 5,67543254 · Accepted Answer · 2015-01-07 07:56:16Z

An implementation of left outer join by extension methods could look like

public static IEnumerable<Result> LeftJoin<TOuter, TInner, TKey, Result>(

  this IEnumerable<TOuter> outer, IEnumerable<TInner> inner

  , Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector

  , Func<TOuter, TInner, Result> resultSelector, IEqualityComparer<TKey> 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<TKey>.Default);

  }



  static IEnumerable<Result> LeftJoinImpl<TOuter, TInner, TKey, Result>(

      IEnumerable<TOuter> outer, IEnumerable<TInner> inner

      , Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector

      , Func<TOuter, TInner, Result> resultSelector, IEqualityComparer<TKey> 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 => item.Item1, item => item.Item1, (it1, it2) =>

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

   }

This is only an option for LINQ to objects however, and won't be able to translate the query to any query providers, which is the most common use case for this operation. — Mar 3 '14 at 21:32
But the question was "How to perform left outer join in C# LINQ to objects ..." — Mar 5 '14 at 20:32

Chris HalcrowChris Halcrow 11.1k47093 · Accepted Answer · 2017-07-04 04:11:08Z

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.

where does b_value come from?

– Jack Fraser
Dec 31 '18 at 17:34 — Dec 31 '18 at 17:34

score 9 · Accepted Answer · 2018-07-17 07:32:00Z

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<Person> people = new List<Person> {magnus, terry, charlotte, arlene};

    List<Pet> pets = new List<Pet> {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)

the output should be: Arlene: Does not Exist

– user1169587
Dec 29 '18 at 9:46 — Dec 29 '18 at 9:46

Community♦ 11 · Accepted Answer · 2017-05-23 12:26:31Z

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

Gilad Green 30.6k53359 · Accepted Answer · 2016-08-09 14:43:28Z

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<Person> persons = new List<Person>

{

    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<School> schools = new List<School>

{

    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<PersonSchool> persons_schools = new List<PersonSchool>

{

    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 => 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);

}

While this is maybe the answer of the question provide some explanation about your answer :) — Jan 31 '16 at 8:28

BezioBezio 411 · Accepted Answer · 2017-10-01 17:42:47Z

Extension method that works like left join with Join syntax

public static class LinQExtensions

{

    public static IEnumerable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(

        this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, 

        Func<TOuter, TKey> outerKeySelector, 

        Func<TInner, TKey> innerKeySelector, 

        Func<TOuter, TInner, TResult> resultSelector)

    {

        return outer.GroupJoin(

            inner, 

            outerKeySelector, 

            innerKeySelector,

            (outerElement, innerElements) => resultSelector(outerElement, innerElements.FirstOrDefault()));

    }

}

just wrote it in .NET core and it seems to be working as expected.

Small test:

        var Ids = new List<int> { 1, 2, 3, 4};

        var items = new List<Tuple<int, string>>

        {

            new Tuple<int, string>(1,"a"),

            new Tuple<int, string>(2,"b"),

            new Tuple<int, string>(4,"d"),

            new Tuple<int, string>(5,"e"),

        };



        var result = Ids.LeftJoin(

            items,

            id => id,

            item => item.Item1,

            (id, item) => item ?? new Tuple<int, string>(id, "not found"));



        result.ToList()

        Count = 4

        [0]: {(1, a)}

        [1]: {(2, b)}

        [2]: {(3, not found)}

        [3]: {(4, d)}

ozkaryozkary 1,4151420 · Accepted Answer · 2015-03-09 18:14:14Z

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.

HamidHamid 213 · Accepted Answer · 2017-12-21 03:20:05Z

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<Person> people = new List<Person> { magnus, terry, charlotte, arlene };

        List<Child> childs = new List<Child> { 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/

Regular Joe 4,02121433 · Accepted Answer · 2017-03-25 17:31:48Z

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 && f.In_Use == true && f.revenueCenterID > 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<int, string> LambdaManning = (x) => { return x == 0 ? "" : "Manning:" + x.ToString(); };

        DataTable dt_ScheduleMaster = PsuedoSchedule.Tables["ScheduleMasterWithNotes"];

        var schedLocations2 = schedLocations.Where(x => x.SchedMondayDate == dMon);

score 1 · Accepted Answer · 2018-04-06 15:46:46Z

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

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.

Tim PohlmannTim Pohlmann 1,9301643 · Accepted Answer · 2018-05-18 14:24:43Z

Here is a fairly easy to understand version using method syntax:

IEnumerable<JoinPair> outerLeft =

    lefts.SelectMany(l => 

        rights.Where(r => l.Key == r.Key)

              .DefaultIfEmpty(new Item())

              .Select(r => new JoinPair { LeftId = l.Id, RightId = r.Id }));

Nikolay Kostov 9,2371865108 · Accepted Answer · 2017-04-11 07:48:15Z

(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



     });

Manikandan DeivasigamaniManikandan Deivasigamani 1 · Accepted Answer · 2017-06-02 07:52:00Z

class Program

{

    List<Employee> listOfEmp = new List<Employee>();

    List<Department> listOfDepart = new List<Department>();



    public Program()

    {

        listOfDepart = new List<Department>(){

            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<Employee>(){

            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 => x.DepartmentId, y => y.Id, (x, y) => new { EmpId = x.Empid, EmpName = x.Name, Dpt = y.FirstOrDefault() != null ? y.FirstOrDefault().DeptName : null }).ToList().ForEach

            (z =>

            {

                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

instead of a screenshot pls. copy and paste the output directly to your answer — Jun 2 '17 at 8:17

Enrique RamosEnrique Ramos 365 · Accepted Answer · 2017-09-13 20:34:48Z

Simple solution for the LEFT OUTER JOIN:

var setA = context.SetA;

var setB = context.SetB.Select(st=>st.Id).Distinct().ToList();

var leftOuter  = setA.Where(stA=> !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

Arithmomaniac 2,83212749 · Accepted Answer · 2017-05-16 13:58:00Z

522

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 };

edited May 16 '17 at 13:58

Arithmomaniac

2,83212749

answered Aug 5 '10 at 10:15

ajay_whiz

10.7k32943

7

I'm trying the same thing but getting an error on the join operator, which says "The type of one of the expressions in the join clause is incorrect."

– Badhon Jain
Jan 8 '14 at 8:52

3

@jain if your types are different the join will not work. So likely your keys are of different datatypes. Are both keys int for example?

– Yooakim
Feb 2 '14 at 17:42

2

Whats the solution Jain? I am also facing the same error and the types are same in my case as well.

– Sandeep
Oct 31 '14 at 16:47

1

@Sandeep check your keys where you joined it. Suppose if those are of types string and int then just convert string key to int.

– Ankitkumar Bhatt
Jul 27 '16 at 9:35

2

updated link: 101 LINQ Samples - Left outer join

– BukeMan
May 5 '17 at 16:28

|
show 5 more comments

jpmc26 15k758105 · Accepted Answer · 2019-01-10 00:38:43Z

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

    }



);

This answer is actually helpful. Thank you for actually offering syntax that is understandable. — Oct 23 '14 at 15:01
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. — Jan 5 '15 at 0:37
I think the syntax of explicit join is much more readable and clear than a where followed by DefaultIfEmpty — Sep 7 '15 at 3:05

N RockingN Rocking 1,69721423 · Accepted Answer · 2014-02-05 18:01:14Z

108

Using lambda expression

db.Categories    

  .GroupJoin(

      db.Products,

      Category => Category.CategoryId,

      Product => Product.CategoryId,

      (x, y) => new { Category = x, Products = y })

  .SelectMany(

      xy => xy.Products.DefaultIfEmpty(),

      (x, y) => new { Category = x.Category, Product = y })

  .Select(s => new

  {

      CategoryName = s.Category.Name,     

      ProductName = s.Product.Name   

  })

answered Feb 5 '14 at 18:01

N Rocking

1,69721423

Do you have to use .GroupJoin or can you also just use .Join?

– Jess
Nov 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 Daniely
Jan 5 '15 at 0:44

43

All this to perform a left join??

– FindOut_Quran
Sep 7 '15 at 3:07

6

Thanks for this! Not too many lambda expression examples out there, this worked for me.

– Johan Henkens
Jan 21 '16 at 22:43

1

Thanks for the answer. It yielded the closest thing to the raw SQL LEFT OUTER JOIN I've written over the years

– John Gathogo
Feb 8 '17 at 6:00

|
show 1 more comment

Nielsvh 84111529 · Accepted Answer · 2018-07-14 06:13:46Z

37

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 };

edited Jul 14 '18 at 6:13

Nielsvh

84111529

answered Aug 5 '10 at 10:11

Devart

99.3k19135160

3

Can r be accessed in the select clause after using a join into?

– Farhad Alizadeh Noori
Jun 2 '14 at 21:48

@FarhadAlizadehNoori Yes It can.

– Po-ta-toe
Oct 12 '16 at 7:56

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 Amini
May 31 '18 at 0:30

add a comment |

Matas VaitkeviciusMatas Vaitkevicius 34.3k16168176 · Accepted Answer · 2016-08-18 13:54:10Z

Now as an extension method:

public static class LinqExt

{

    public static IEnumerable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(this IEnumerable<TLeft> left, IEnumerable<TRight> right, Func<TLeft, TKey> leftKey, Func<TRight, TKey> rightKey,

        Func<TLeft, TRight, TResult> result)

    {

        return left.GroupJoin(right, leftKey, rightKey, (l, r) => new { l, r })

             .SelectMany(

                 o => o.r.DefaultIfEmpty(),

                 (l, r) => new { lft= l.l, rght = r })

             .Select(o => result.Invoke(o.lft, o.rght));

    }

}

Use like you would normally use join:

var contents = list.LeftOuterJoin(list2, 

             l => l.country, 

             r => r.name,

            (l, r) => new { count = l.Count(), l.country, l.reason, r.people })

Hope this saves you some time.

Console 5,67543254 · Accepted Answer · 2015-01-07 07:56:16Z

An implementation of left outer join by extension methods could look like

public static IEnumerable<Result> LeftJoin<TOuter, TInner, TKey, Result>(

  this IEnumerable<TOuter> outer, IEnumerable<TInner> inner

  , Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector

  , Func<TOuter, TInner, Result> resultSelector, IEqualityComparer<TKey> 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<TKey>.Default);

  }



  static IEnumerable<Result> LeftJoinImpl<TOuter, TInner, TKey, Result>(

      IEnumerable<TOuter> outer, IEnumerable<TInner> inner

      , Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector

      , Func<TOuter, TInner, Result> resultSelector, IEqualityComparer<TKey> 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 => item.Item1, item => item.Item1, (it1, it2) =>

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

   }

This is only an option for LINQ to objects however, and won't be able to translate the query to any query providers, which is the most common use case for this operation. — Mar 3 '14 at 21:32
But the question was "How to perform left outer join in C# LINQ to objects ..." — Mar 5 '14 at 20:32

Chris HalcrowChris Halcrow 11.1k47093 · Accepted Answer · 2017-07-04 04:11:08Z

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.

where does b_value come from?

– Jack Fraser
Dec 31 '18 at 17:34 — Dec 31 '18 at 17:34

score 9 · Accepted Answer · 2018-07-17 07:32:00Z

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<Person> people = new List<Person> {magnus, terry, charlotte, arlene};

    List<Pet> pets = new List<Pet> {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)

the output should be: Arlene: Does not Exist

– user1169587
Dec 29 '18 at 9:46 — Dec 29 '18 at 9:46

Community♦ 11 · Accepted Answer · 2017-05-23 12:26:31Z

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

Gilad Green 30.6k53359 · Accepted Answer · 2016-08-09 14:43:28Z

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<Person> persons = new List<Person>

{

    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<School> schools = new List<School>

{

    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<PersonSchool> persons_schools = new List<PersonSchool>

{

    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 => 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);

}

While this is maybe the answer of the question provide some explanation about your answer :) — Jan 31 '16 at 8:28

BezioBezio 411 · Accepted Answer · 2017-10-01 17:42:47Z

Extension method that works like left join with Join syntax

public static class LinQExtensions

{

    public static IEnumerable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(

        this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, 

        Func<TOuter, TKey> outerKeySelector, 

        Func<TInner, TKey> innerKeySelector, 

        Func<TOuter, TInner, TResult> resultSelector)

    {

        return outer.GroupJoin(

            inner, 

            outerKeySelector, 

            innerKeySelector,

            (outerElement, innerElements) => resultSelector(outerElement, innerElements.FirstOrDefault()));

    }

}

just wrote it in .NET core and it seems to be working as expected.

Small test:

        var Ids = new List<int> { 1, 2, 3, 4};

        var items = new List<Tuple<int, string>>

        {

            new Tuple<int, string>(1,"a"),

            new Tuple<int, string>(2,"b"),

            new Tuple<int, string>(4,"d"),

            new Tuple<int, string>(5,"e"),

        };



        var result = Ids.LeftJoin(

            items,

            id => id,

            item => item.Item1,

            (id, item) => item ?? new Tuple<int, string>(id, "not found"));



        result.ToList()

        Count = 4

        [0]: {(1, a)}

        [1]: {(2, b)}

        [2]: {(3, not found)}

        [3]: {(4, d)}

ozkaryozkary 1,4151420 · Accepted Answer · 2015-03-09 18:14:14Z

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.

HamidHamid 213 · Accepted Answer · 2017-12-21 03:20:05Z

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<Person> people = new List<Person> { magnus, terry, charlotte, arlene };

        List<Child> childs = new List<Child> { 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/

Regular Joe 4,02121433 · Accepted Answer · 2017-03-25 17:31:48Z

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 && f.In_Use == true && f.revenueCenterID > 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<int, string> LambdaManning = (x) => { return x == 0 ? "" : "Manning:" + x.ToString(); };

        DataTable dt_ScheduleMaster = PsuedoSchedule.Tables["ScheduleMasterWithNotes"];

        var schedLocations2 = schedLocations.Where(x => x.SchedMondayDate == dMon);

score 1 · Accepted Answer · 2018-04-06 15:46:46Z

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

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.

Tim PohlmannTim Pohlmann 1,9301643 · Accepted Answer · 2018-05-18 14:24:43Z

Here is a fairly easy to understand version using method syntax:

IEnumerable<JoinPair> outerLeft =

    lefts.SelectMany(l => 

        rights.Where(r => l.Key == r.Key)

              .DefaultIfEmpty(new Item())

              .Select(r => new JoinPair { LeftId = l.Id, RightId = r.Id }));

Nikolay Kostov 9,2371865108 · Accepted Answer · 2017-04-11 07:48:15Z

(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



     });

Manikandan DeivasigamaniManikandan Deivasigamani 1 · Accepted Answer · 2017-06-02 07:52:00Z

class Program

{

    List<Employee> listOfEmp = new List<Employee>();

    List<Department> listOfDepart = new List<Department>();



    public Program()

    {

        listOfDepart = new List<Department>(){

            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<Employee>(){

            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 => x.DepartmentId, y => y.Id, (x, y) => new { EmpId = x.Empid, EmpName = x.Name, Dpt = y.FirstOrDefault() != null ? y.FirstOrDefault().DeptName : null }).ToList().ForEach

            (z =>

            {

                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

instead of a screenshot pls. copy and paste the output directly to your answer — Jun 2 '17 at 8:17

Enrique RamosEnrique Ramos 365 · Accepted Answer · 2017-09-13 20:34:48Z

Simple solution for the LEFT OUTER JOIN:

var setA = context.SetA;

var setB = context.SetB.Select(st=>st.Id).Distinct().ToList();

var leftOuter  = setA.Where(stA=> !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

Search This Blog

Ufyukyu

LEFT OUTER JOIN in LINQ

19 Answers
19

protected by Community♦ Dec 1 '18 at 21:04

19 Answers
19

19 Answers
19

protected by Community♦ Dec 1 '18 at 21:04

Popular posts from this blog

android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

SQL update select statement

'app-layout' is not a known element: how to share Component with different Modules

Category

Random preview

LEFT OUTER JOIN in LINQ

19 Answers 19

protected by Community♦ Dec 1 '18 at 21:04

19 Answers 19

19 Answers 19

protected by Community♦ Dec 1 '18 at 21:04

Popular posts from this blog

android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

SQL update select statement

'app-layout' is not a known element: how to share Component with different Modules

19 Answers
19

19 Answers
19

19 Answers
19