Left Outer Join in Linq

C# - from SQL to Linq - Left Outer Join/Inner Join

According to MSDN:

To perform a left outer join in LINQ, use the DefaultIfEmpty method in combination with a group join to specify a default right-side element to produce if a left-side element has no matches. You can use null as the default value for any reference type, or you can specify a user-defined default type.

From what I can tell, you first join all the data via a GroupJoin This "correlates the elements of two sequences based on key equality and groups the results." Then, the following from specifies a default right-side element (by using DefaultIfEmpty()) to produce if a left-side element has no matches. You will need to perform this for all of your left joins:

public static List<value> GetSmthn(string value1, string value2, string value3)
    {           

    return (
         from A in Alpha

         join B in Beta on A.b equals B.k

         join C in Charlie on A.a equals C.a into cgroup
         from C in cgroup.DefaultIfEmpty()

         join D in Delta on B.j equals D.k into dgroup
         from D in dgroup.DefaultIfEmpty()

         join E in Echo on E.a equals A.a

         where (A.a == value1 && E.c == value2 && (A.a == value3 || value3 == "") && A.b == E.b)

         select new value() { a = A.a, b = A.b, c = E.c, d = A.d, e = A.e, f = A.f, g = A.g, h = D.h i = D.i }

         ).ToList();
    }

How do you perform a left outer join using linq extension methods

For a (left outer) join of a table Bar with a table Foo on Foo.Foo_Id = Bar.Foo_Id in lambda notation:

var qry = Foo.GroupJoin(
          Bar, 
          foo => foo.Foo_Id,
          bar => bar.Foo_Id,
          (x,y) => new { Foo = x, Bars = y })
       .SelectMany(
           x => x.Bars.DefaultIfEmpty(),
           (x,y) => new { Foo=x.Foo, Bar=y});

Linq to SQL left outer join using Lambda syntax and joining on 2 columns (composite join key)

I was able to get this LEFT OUTER JOIN on the composite foreign key pair barcode, event_id working in both Linq2Sql, and Entity Framework, converting to lambda syntax as per this query syntax example.

This works by creating an anonymous projection which is used in match of the left and right hand sides of the join condition:

var dnrs = context.participants.GroupJoin(
    context.prereg_participants,
    x => new { JoinCol1 = x.barcode, JoinCol2 = x.event_id }, // Left table join key
    y => new { JoinCol1 = y.barcode, JoinCol2 = y.event_id }, // Right table join key
    ...

Notes

This approach relies on the automagic equality given to identical anonymous classes, viz:

Because the Equals and GetHashCode methods on anonymous types are defined in terms of the Equals and GetHashCode methods of the properties, two instances of the same anonymous type are equal only if all their properties are equal.

So for the two projections for the join keys need to be of the same type in order to be equal, the compiler needs to see them as the same anonymous class behind the scenes, i.e.:

• The number of joined columns must be the same in both anonymous projections
• The field types must be of the same type compatable
• If the field names differ, then you will need to alias them (I've used JoinColx)

I've put a sample app up on GitHub here.

Sadly, there's no support yet for value tuples in expression trees, so you'll need to stick to anonymous types in the projections.

How to do a Linq Left Outer Join with a middle table?

Your LINQ translation is just a little off.

The SQL has an inner join on crm.tbljobs followed by outer joins on common.tblSalesPerson and common.tblSupervisors.

The LINQ has outer joins on dbc.tblJobs and dbc.tblSalesPersons followed by an inner join on dbc.tblSupervisors.

into temp1 ... from j in in temp1.DefaultIfEmpty() makes the outer join happen on the table introduced prior to the into, which is dbc.tblJobs.

So it should be:

...
// inner join
join j in dbc.tblJobs on jp.JobID equals j.JobID

// left outer join
join sp in dbc.tblSalesPersons on j.SalesPersonID equals sp.SalesPersonID into salesPersons
from sp in salesPersons.DefaultIfEmpty()

// left outer join
join s in dbc.tblSupervisors on j.SupervisorID equals s.SupervisorID into supervisors
from s in supervisors.DefaultIfEmpty()
...

I changed temp1 and temp2 to more meaningful names to demonstrate what they represent in the outer join syntax. Note the relationship and relative position of dbc.tblSalesPersons to salesPersons, for example.

One more thing to remember is that sp and s can be null, so make sure you check for that before accessing their FirstName, LastName, and Surname properties.

Complex joins in Linq with multiple tables and LEFT OUTER JOIN

You should apply Left Join this way:

join readers in db.Readers on readerdata.mac_address equals readers.mac_address into readersJ
from readers in readersJ.DefaultIfEmpty()

The full code:

var query = (
    from tags in db.Tags
    join tagreads in db.tag_reads on tags.epc equals tagreads.epc
    join readerdata in db.ReaderData on tagreads.ReaderDataId equals readerdata.Id
    join readers in db.Readers on readerdata.mac_address equals readers.mac_address into readersJ
    from readers in readersJ.DefaultIfEmpty()
    join locations in db.Locations 
        on new { ap = tagreads.antennaPort, rd = readerdata.Id }
        equals new { ap = locations.AntennaId, rd = locations.ReaderId }
    group tags by new { tags.TagFriendlyName, timestamp = tagreads.timeStamp, readerdata.mac_address } into grp

    select new CurrentStatus()
    {
        TagName = grp.Key.TagFriendlyName,
        LastSeen = grp.Key.timestamp,
        LocationName = grp.Key.mac_address
    }
)
.OrderByDescending(o => o.LastSeen)

Left outer join in LINQ query

You can use Enumerable.DefaultIfEmpty Method for left outer join.

You may see: How to: Perform Left Outer Joins (C# Programming Guide) - MSDN

Consider the following example from MSDN,

class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class Pet
{
    public string Name { get; set; }
    public Person Owner { get; set; }
}

Left outer join query can be:

var query = from person in people
            join pet in pets on person equals pet.Owner into gj
            from subpet in gj.DefaultIfEmpty()
            select new { person.FirstName, PetName = (subpet == null ? String.Empty : subpet.Name) };

LINQ for an SQL with multiple LEFT OUTER JOINS on different tables

You can simply convert your query to linq query like this:

var results = (from a in A_TABLE
               join b in B_TABLE
               on a.A_TABLE_Id equals b.A_TABLE_Id into ab
               from b in ab.DefaultIfEmpty()
               join c in C_TABLE_List on b.B_TABLE_Id equals c.B_TABLE_Id

               select new
                   {
                     ClassAProperty1 = a.Property1,
                     ClassBProperty1 = b.Property1,
                     ClassCProperty1 = c.Property1
                   }).ToList();

you can continue on joining tables whatever times you need.

Don't forget to change Property# to required property names.

for more info take a look at LINQ Left Outer Join, and this stack thread.

Update:
this is the Lambda expressions version of the query:

var result = A_TABLE
             .GroupJoin(B_TABLE,
                 a => a.A_TABLE_Id,
                 b => b.A_TABLE_Id,
                 (a, b) =>
                 new {
                       tableAProperty1 = a.Property1,
                       tableAProperty2 = a.Property2, /* You cannot access this property in next join if you don't add it here */
                       B_TABLE = b.FirstOrDefault() /* This is the way to access B_TABLE in next join */
                 }).GroupJoin(C_TABLE,
                       ab => ab.B_TABLE.B_TABLE_Id,
                       c => c.B_TABLE_Id,
                       (ab, c) =>
                       new {
                             ab.tableAProperty1,
                             B_TABLEProperty2 = ab.B_TABLE.Property2,
                             C_TABLE = c.FirstOrDefault()
                            }).ToList();

Left/outer join with linq on c# with where condition clause

I have found the answer from this SO question. From there, I realized that the position of the where clause is the problem. See the working code revision below

var userScores = 
(from e in db.Entries join se in db.UserEntries.Where(o => o.UserId == 
"898128" 
on e.Id equals se.EntryId                                           
into ese from se in 
ese.DefaultIfEmpty()
select new 
{
EntryLabel=e.Label,
EntryValue=se.ValueAmount,
}).ToList();

ViewData["userScores "] = userScores;

Thank you @Hazarath for your guide

---

Related Topics