LEFT OUTER JOIN in LINQ

Question

How to perform left outer join in C# LINQ to objects without using join-on-equals-into clauses? Is there any way to do that with where clause? Correct problem: For inner join is easy and I have a solution like this

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

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

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

where JoinPair is a class:

public class JoinPair { long leftId; long rightId; }

Microbalance

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... – Microbalance

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. – Perfidy

Related post - Linq join iquery, how to use defaultifempty – Widen

See also Linq to Entities join vs groupjoin. – Kora

For Linq-to-Entities (not Objects), see this question: #19356939 – Cassilda

Answer 1

As stated in "Perform left outer joins":

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

Calorific

now we can use null coalescing like select new { Category = c, ProductName = p.ProductName ?? "(No products)" }; – Kronos

from "p" in ps.DefaultIfEmpty(), is this "p" has any relationship with the "p" in join p in products? – Bewhiskered

@Bewhiskered no – Calorific

What is the equivalent in Linq with lambda? – Could

Why not distinguish the two ps by giving different names? They are in different contexts? – Doxology

How can we add where clause on p? – Renn

Answer 2

If a database driven LINQ provider is used, a significantly more readable left outer join can be written as such:

from c in categories 
from p in products.Where(c == p.Category).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.SomeTable.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, there will be no DB-optimizer ...

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

I stress again, if you are doing that 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).

Here's the old fashioned way to do it, with actual left-joins:

    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
    }

);

Now, as an example with a more complex query, if you need further explanation on how it works, consider this SQL statement:

DECLARE @BE_ID integer; 
DECLARE @stichtag datetime; 
DECLARE @in_standort uniqueidentifier; 
DECLARE @in_gebaeude uniqueidentifier; 

SET @BE_ID = 123; 
SET @stichtag = CURRENT_TIMESTAMP; 
SET @in_standort = '00000000-0000-0000-0000-000000000000'; 
SET @in_gebaeude = '00000000-0000-0000-0000-000000000000'; 

DECLARE @unixTimestamp bigint; 
DECLARE @bl national character varying(MAX); 
SET @unixTimestamp =  DATEDIFF(SECOND, '1970-01-01T00:00:00.000', CONVERT(DATETIME, @stichtag, 1));

SET @bl = (
    SELECT TOP 1 FC_Value
    FROM T_FMS_Configuration
    WHERE FC_Key = 'basicLink'
);


-- SELECT @unixTimestamp AS unix_ts, @bl AS bl; 


SELECT
    so.SO_Nr AS RPT_SO_Nr 
    ,so.SO_Bezeichnung AS RPT_SO_Bezeichnung
    ,gb.GB_Bezeichnung
    ,gb.GB_GM_Lat
    ,gb.GB_GM_Lng
    ,objTyp.OBJT_Code
    ,@bl + '/Modules/App150/index.html'
        + '?Code=' + COALESCE(objTyp.OBJT_Code, 'BAD')
        + '&UID=' + COALESCE(CAST(gb.GB_UID AS national character varying(MAX)), '')
        + '&Timestamp=' + CONVERT(national character varying(MAX), @unixTimestamp, 126)
    AS RPT_QR 
FROM T_AP_Gebaeude AS gb
LEFT JOIN T_AP_Standort AS so ON gb.GB_SO_UID = so.SO_UID
LEFT JOIN T_OV_Ref_ObjektTyp AS objTyp ON 'GB' = objTyp.OBJT_Code 
LEFT JOIN T_Benutzer AS benutzer ON benutzer.BE_ID = @BE_ID AND benutzer.BE_Status = 1
WHERE gb.GB_Status = 1
AND @stichtag >= gb.GB_DatumVon
AND @stichtag <= gb.GB_DatumBis
AND so.SO_Status = 1
AND @stichtag >= so.SO_DatumVon
AND @stichtag <= so.SO_DatumBis
AND (@in_standort = '00000000-0000-0000-0000-000000000000' OR so.SO_UID = @in_standort)
AND (@in_gebaeude = '00000000-0000-0000-0000-000000000000' OR gb.GB_UID = @in_gebaeude)
AND 
(
       benutzer.BE_ID IS NULL
    OR benutzer.BE_ID < 0
    OR benutzer.BE_usePRT = 0
    OR EXISTS 
    (
            SELECT 1
            FROM T_COR_Objekte AS obj
            INNER JOIN T_COR_ZO_ObjektRechte_Lesen AS objR
                ON objR.ZO_OBJR_OBJ_UID = obj.OBJ_UID
                AND objR.ZO_OBJR_OBJ_OBJT_Code = obj.OBJ_OBJT_Code
            WHERE obj.OBJ_UID = gb.GB_UID
    )
); 

which yields the following LINQ:
(this is the DB context, dump is a method of LINQpad)

int BE_ID = 123; 
System.DateTime stichtag = System.DateTime.Now;
System.Guid in_standort = System.Guid.Empty;
System.Guid in_gebaeude = System.Guid.Empty;

long unixTimestamp = (long)(stichtag.ToUniversalTime() - new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalSeconds;

string bl = (
    from c in this.T_FMS_Configuration
    where c.FC_Key == "basicLink"
    select c.FC_Value
).FirstOrDefault();



(
    from gb in this.T_AP_Gebaeude
    join so in this.T_AP_Standort on gb.GB_SO_UID equals so.SO_UID into gb_so
    from so in gb_so.DefaultIfEmpty()
    join objTyp in this.T_OV_Ref_ObjektTyp on "GB" equals objTyp.OBJT_Code into gb_objTyp
    from objTyp in gb_objTyp.DefaultIfEmpty()
    join benutzer in this.T_Benutzer.Where(b => b.BE_ID == BE_ID && b.BE_Status == 1) on 1 equals 1 into gb_benutzer
    from benutzer in gb_benutzer.DefaultIfEmpty()
    where gb.GB_Status == 1 
    && stichtag >= gb.GB_DatumVon 
    && stichtag <= gb.GB_DatumBis 
    && so.SO_Status == 1
    && stichtag >= so.SO_DatumVon 
    && stichtag <= so.SO_DatumBis 
    && (in_standort == System.Guid.Empty|| so.SO_UID == in_standort) 
    && (in_gebaeude == System.Guid.Empty || gb.GB_UID == in_gebaeude) 
    && 
    (
        benutzer == null 
        || benutzer.BE_ID < 0 
        || benutzer.BE_usePRT == false 
        || this.T_COR_Objekte.Any(
                obj => obj.OBJ_UID == gb.GB_UID 
                && this.T_COR_ZO_ObjektRechte_Lesen.Any(objR => objR.ZO_OBJR_OBJ_UID == obj.OBJ_UID && objR.ZO_OBJR_OBJ_OBJT_Code == obj.OBJ_OBJT_Code)
        )
    )


    select new { 
         RPT_SO_Nr = so.SO_Nr 
        ,RPT_SO_Bezeichnung = so.SO_Bezeichnung
        // ,RPT_GB_UID = gb.GB_UID 
        // ,gb.GB_Nr 
        ,gb.GB_Bezeichnung
        // ,adr = gb.GB_Strasse + " " + gb.GB_StrasseNr + ", CH-" + gb.GB_PLZ + " " + gb.GB_Ort 
        ,gb.GB_GM_Lat
        ,gb.GB_GM_Lng
        // ,objTyp.OBJT_UID
        ,objTyp.OBJT_Code 
        
        ,RPT_QR = bl + "/Modules/App150/index.html" 
        + "?Code=" + (objTyp.OBJT_Code ?? "BAD")
        + "&UID=" + (System.Convert.ToString(gb.GB_UID) ?? "" )
        + "&Timestamp=" + unixTimestamp.ToString(System.Globalization.CultureInfo.InvariantCulture)
    }
).Dump();

Alternative

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. – Swabber

@Stefan Steiger Can you plz check the link #31939284 – Herodias

I think the syntax of explicit join is much more readable and clear than a where followed by DefaultIfEmpty – Ssr

@user3441905: As long as you just have to join table a with table b, this may be. But as soon as you have more than that, it will not be. But even for only 2 table, I think it's overly verbous. Popular opinion also seems to be against you, as this answer started with 0 when the top answer already had 90+ upvotes. – Alternative

Note I had trouble with this when combined with a later Where statement which filtered on a column in the left-joined table. Adding the Where statement (example Where(mappings.SomeField = Parameter) will convert the mappings join to an INNER JOIN in the SQL translation. I think this is because EF wants to be able to read this property all the time and doesn't want to deal with a null object for mappings, despite the presence of DefaultIfEmpty above. – Eclogue

@pseudocoder: If in SQL you do a WHERE on a field in a left-joined table, the result is the same as in an INNER JOIN. You need to specifically allow NULL in the SQL-where clause... e.g. WHERE (MAP_DateTo > Now() OR MAP_DateTo IS NULL). – Alternative

@StefanSteiger Thanks, I did realize that after thinking about it for a while. (haha, get it?) You bring up an interesting point so I did some further testing. I was having trouble expressing field is null in LINQ because the field I tested on is not a nullable type and I got a runtime error. Then I tried checking for the entity being null and there was no effect on the SQL; an inner join was still listed. So, not sure what to make of this, but in this particular case I don't want to allow the row in the case of a NULL value so the INNER JOIN works just fine. – Eclogue

@pseudocoder: If the field is NOT NULL (in entity), then I suppose it is NOT NULL in the db. You can, however, still get NULL values when you left join an object that doesn't exist yet (e.g. a building that does not yet have floors inputted, but you join the floors). Another possibility is, you have not set a foreign key, and there is some garbage data in this field (e.g. a reference that doesn't exist anymore, or that never actually existed in the first place, but because you're missing a foreign-key, or have set it as no-check...). – Alternative

@StefanSteiger, on your select new block, mappings and groups may be null and an exception would be thrown, wouldn't it? – Chaps

@HeyJude; No it wouldn't. Some of mappings and group's individual values may be NULL, but that won't throw an exception. However, it's worth noting that "from xxx where" is the same as an actual outer-apply/left-join-lateral, but that means it's still a left join under the hood. – Alternative

Why this answer is not bad it's not a good recommandation anyway. The question doesn't even mention that a database driven LINQ provider is used and even if, it's not recommended to relay on the database optimizer to fix your sql query. For that reason i have downvoted. In Linq-To-Objects this approach can destroy your performance, so it's better to get familiar with the not "old-fashioned" but the correct way. – Downturn

@Tim Schmelter: You're right. Fixed that. The problem with the "familiar" syntax is that they nowhere provide an example of how a left join over MULTIPLE tables works. That's why I added that example earlier. If they would have, I would never have had to figure this out in the first place. The so-called "familiar" syntax is not very inutive if you're not familiar with LINQ. – Alternative

I know what you mean, it's getting even worse if you use method syntax instead of query syntax. Btw, even in sql using old-style JOINs is a bad habit to kick. – Downturn

@Tim Schmelter: Indeed, but it's an interesting feature - might be able to do a left-join that way in LINQ ;) – Alternative

@HeyJude they would throw an exception if doing LINQ to objects as you'd be accessing a property on a null reference. However if run on the database, the value just becomes null. – Petronella

The from xxx in Repo.T_ANY_TABLE.Where(join condition).DefaultIfEmpty() syntax does not work properly with Entity Framework Core. It turns into a pretty nasty CROSS APPLY that ends up failing with An exception occurred in the database while iterating the results of a query. Nullable object must have a value.. Too bad too, I really prefer that syntax... – Lyall

@Christopher Haws: Then I suggest you open an issue on entity framework core. Because first it should be an outer apply, not a cross-apply, and second cross applies should work, too. I suggest you avoid entity-framwork-core for the time being, because it's really work-in-progress, from top-to-bottom... And not just because of left-joins/outer-apply. Dapper is a lot better anyway. I actually had to write my own code to generate the entity-schema from the db... that's how FUBAR it is. – Alternative

@StefanSteiger Dapper doesn't have a dependency graph, which is what EF gives you out of the box. I use dapper in simpler solutions where I dont need the power of a graph or the ability to easily unit test my classes. – Lyall

Christopher Haws: Ys, but Dapper doesn't need a dependency graph. – Alternative

Sadly to note that generated queries differ in .net core 2.2 using SQL Server provider. In your approach adds a meaningless cross apply statement into the query. – Agbogla

In Entity Framework Core 5, it's working perfectly. So easy solution. Thank you. – Mammy

Very useful as I am facing the issue for 3 tables join one is parent and other 2 are child This help me a lot .DefaultIfEmpty() // <== makes join left join – Maximinamaximize

Doesn't the Where call in your first example solution need to be passed a lambda? – Cotswolds

Answer 3

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

Passionate

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

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

Don't really need the last Select(), the anon obj in the SelectMany() can be refactored for the same output. Another thought is to test y for null to simulate a closer LEFT JOIN equivalence. – Bantling

The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally. Not the best option apparently. – Judicative

As @DennyJacob hinted at, you need to do s.Product?.Name or handle nulls in some other way. It's baffling how many answers on this page will throw an exception if the left list contains an item that the right doesn't, even though handling that exact situation is the entire purpose of a left join. – Fisticuffs

@Fisticuffs That isn't correct if your LINQ is translated to SQL (SQL propagates nulls automatically) so unless in EF Core 2.x should be okay. – Fussell

@Fussell The question is about LINQ to objects, meaning manipulating IEnumerables rather than generating SQL statements. – Fisticuffs

Answer 4

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.

Urena

This is nice, but in your example, won't r.people throw an exception if list contains keys list2 doesn't because r will be null? Shouldn't it be r?.people? Otherwise, it's just an inner join that also throws exceptions. Or, I think you could add a "default right element" parameter to LeftOuterJoin() and pass it into DefaultIfEmpty(). – Fisticuffs

Shouldn't this be for IQueryable instead? – Fussell

Answer 5

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

Comeon

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

@FarhadAlizadehNoori Yes It can. – Simonne

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. – Petronella

@Devart, when I read your query it reminded me of movie Clockwise with John Cleese, lol. – Urena

From left into right into leftrights in rights on left in leftRights... Oh jeez... The syntax of using LEFT OUTER JOIN in LINQ really isn't clear, but these names really make it even more unclear. – Cautious

@Simonne no it can't since into makes new closure. – Hurl

Answer 6

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

Glyceryl

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. – Patin

But the question was "How to perform left outer join in C# LINQ to objects ..." – Glyceryl

Last param "comparer" in LeftJoin method should be optional parameter equals null I guess – Reprise

Answer 7

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)

Plasmosome

the output should be: Arlene: Does not Exist – Bewhiskered

Answer 8

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.

Dorsum

where does b_value come from? – Preempt

Answer 9

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://mcmap.net/q/64921/-linq-to-sql-multiple-tables-left-outer-join

Nunciature

Answer 10

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

Entomology

Answer 11

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://mcmap.net/q/64922/-how-do-you-perform-a-left-outer-join-using-linq-extension-methods

This will ensure that it stays as an IQueryable in case you need to do further logical operations on it later.

Aestivation

+1 Since the question specifically requested a solution for "LINQ to objects" rather than "LINQ to SQL," etc., this is the best answer (not reinventing the wheel). Plus, many of the answers here actually throw an exception if items exist in the left collection but not the right one, meaning they aren't left joins at all. They are simply inner joins with pointless exceptions sprinkled on top. – Fisticuffs

Answer 12

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

Daladier

Answer 13

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

Stannfield

While this is maybe the answer of the question provide some explanation about your answer :) – Strapping

Answer 14

Easy way is to use Let keyword. This works for me.

from AItem in Db.A
Let BItem = Db.B.Where(x => 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

Pearlene

Note that this only makes sense if you want exactly 1 item in your output list per item in the left input list. If the right list has duplicates, they will be discarded via FirstOrDefault(). So it's not a true left join. However, it's still useful for common situations like retrieving data from a lookup that has unique keys. – Fisticuffs

Answer 15

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 => new { person.Id, person.Age },
            pet => new { pet.Id, Age = pet.Age * 2 },
            (person, pet) => new
            {
                Person = person,
                Pets = pet,
            }).SelectMany(
            pet => pet.Pets.DefaultIfEmpty(),
            (people, pet) => new
            {
                people.Person,
                Pet = pet,
            });

Fortenberry

Answer 16

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.

Citreous

Answer 17

Here's a version of the extension method solution using IQueryable instead of IEnumerable

public class OuterJoinResult<TLeft, TRight>
{
    public TLeft LeftValue { get; set; }
    public TRight RightValue { get; set; }
}

public static IQueryable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(this IQueryable<TLeft> left, IQueryable<TRight> right, Expression<Func<TLeft, TKey>> leftKey, Expression<Func<TRight, TKey>> rightKey, Expression<Func<OuterJoinResult<TLeft, TRight>, TResult>> result)
{
    return left.GroupJoin(right, leftKey, rightKey, (l, r) => new { l, r })
          .SelectMany(o => o.r.DefaultIfEmpty(), (l, r) => new OuterJoinResult<TLeft, TRight> { LeftValue = l.l, RightValue = r })
          .Select(result);
}

Instrumental

And as often with extension methods, calling the method becomes more complex than just using the basic LINQ methods yourself. Calling GroupJoin in code is not hard at all, esp. not with query syntax. – Kora

Answer 18

This is the LeftJoin implementation I use. Notice that the the resultSelector expression accepts 2 parameters: one instance from both sides of the join. In most other implementations that I've seen the result selector only accepts one parameter, which is a "join model" with a left/right or outer/inner property. I like this implementation better because it has the same method signature as the built-in Join method. It also works with IQueryables and EF.

 var results = DbContext.Categories
      .LeftJoin(
            DbContext.Products, c => c.Id, p => p.CategoryId,
            (c, p) => new { Category = c, ProductName = p == null ? "(No Products)" : p.ProductName })
      .ToList();

---

public static class QueryableExtensions
{
    public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
           this IQueryable<TOuter> outer, 
           IEnumerable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector, 
           Expression<Func<TInner, TKey>> innerKeySelector, 
           Expression<Func<TOuter, TInner, TResult>> resultSelector)
    {
        var query = outer
            .GroupJoin(inner, outerKeySelector, innerKeySelector, (o, i) => new { o, i })
            .SelectMany(o => o.i.DefaultIfEmpty(), (x, i) => new { x.o, i });
        return ApplySelector(query, x => x.o, x => x.i, resultSelector);
    }

    private static IQueryable<TResult> ApplySelector<TSource, TOuter, TInner, TResult>(
        IQueryable<TSource> source,
        Expression<Func<TSource, TOuter>> outerProperty,
        Expression<Func<TSource, TInner>> innerProperty,
        Expression<Func<TOuter, TInner, TResult>> resultSelector)
    {
        var p = Expression.Parameter(typeof(TSource), $"param_{Guid.NewGuid()}".Replace("-", string.Empty));
        Expression body = resultSelector?.Body
            .ReplaceParameter(resultSelector.Parameters[0], outerProperty.Body.ReplaceParameter(outerProperty.Parameters[0], p))
            .ReplaceParameter(resultSelector.Parameters[1], innerProperty.Body.ReplaceParameter(innerProperty.Parameters[0], p));
        var selector = Expression.Lambda<Func<TSource, TResult>>(body, p);
        return source.Select(selector);
    }
}

public static class ExpressionExtensions
{
    public static Expression ReplaceParameter(this Expression source, ParameterExpression toReplace, Expression newExpression)
        => new ReplaceParameterExpressionVisitor(toReplace, newExpression).Visit(source);
}

public class ReplaceParameterExpressionVisitor : ExpressionVisitor
{
    public ReplaceParameterExpressionVisitor(ParameterExpression toReplace, Expression replacement)
    {
        this.ToReplace = toReplace;
        this.Replacement = replacement;
    }

    public ParameterExpression ToReplace { get; }
    public Expression Replacement { get; }
    protected override Expression VisitParameter(ParameterExpression node)
        => (node == ToReplace) ? Replacement : base.VisitParameter(node);
}

Residential

Answer 19

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/

Guild

Answer 20

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

Sulfate

Answer 21

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

Maggs

Answer 22

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


 Compared to using a Left Join and Group new

IList<MyView> 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<MyView>();

Redmond

Answer 23

This is the prettiest solution I use, give it a try! 😉

(from c in categories
      let product = products.Where(d=> d.Category == c.Category).FirstOrDefault()
    select new { Category = c, ProductName = p == null ? "(No products)" : product.ProductName };

Anticathode

Answer 24

(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

     });

Eklund