Linq Inner-Join VS Left-Join

LINQ Inner-Join vs Left-Join

I think if you want to use extension methods you need to use the GroupJoin

var query =
    people.GroupJoin(pets,
                     person => person,
                     pet => pet.Owner,
                     (person, petCollection) =>
                        new { OwnerName = person.Name,
                              Pet = PetCollection.Select( p => p.Name )
                                                 .DefaultIfEmpty() }
                    ).ToList();

You may have to play around with the selection expression. I'm not sure it would give you want you want in the case where you have a 1-to-many relationship.

I think it's a little easier with the LINQ Query syntax

var query = (from person in context.People
             join pet in context.Pets on person equals pet.Owner
             into tempPets
             from pets in tempPets.DefaultIfEmpty()
             select new { OwnerName = person.Name, Pet = pets.Name })
            .ToList();

Linq left join returns inner join

For translating SQL to LINQ query comprehension:

1. Translate FROM subselects as separately declared variables.
2. Translate each clause in LINQ clause order, translating monadic operators (DISTINCT, TOP, etc) into functions applied to the whole LINQ query.
3. Use table aliases as range variables. Use column aliases as anonymous type field names.
4. Use anonymous types (new { ... }) for multiple columns.
5. Left Join is simulated by using a into join_variable and doing another from from the join variable followed by .DefaultIfEmpty().
6. Replace COALESCE with the conditional operator and a null test.
7. Translate IN to .Contains() and NOT IN to !...Contains()
8. SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
9. SELECT fields must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
10. Proper FULL OUTER JOIN must be handled with an extension method.

So from your SQL, your query should look like:

var ans = from s in ctx.Suppliers
          join sp in ctx.SupplierParts on s.SupplierID equals sp.SupplierID
          join sm in ctx.SupplierManufacturerRelations on sp.SupplierPNID equals sm.SupplierPNID into smj
          from sm in smj.DefaultIfEmpty()
          join mp in ctx.ManufacturerParts on sm?.MfgPNID equals mp.MfgPNID into mpj
          from mp in mpj.DefaultIfEmpty()
          join im in ctx.ItemMasters on new { key1 = (int)mp.PreID, key2 = (int)mp.PartNumber } equals new { key1 = im.PreID, key2 = im.PartNumber } into imj
          from im in imj.DefaultIfEmpty()
          select new {
              s.SupplierID, sp.SupplierPNID, sp.SupplierPN, sp.Description, sm.MfgPNID, mp.PreID, mp.PartNumber, im.CCID, Expr1 = im.Description
          };

SQL to LINQ - Left Join Before Inner Join

Use:

var query = from m in tableA
join s in tableB on m.ShipId equals s.ShipId
join p in tableC on s.PostageId equals p.PostageId
where m.MailId == "Specification" && p.PostageCode == "Package"
select m.MailId;

Your query uses a LEFT OUTER JOIN but it doesn't need it.

It will, in practice, function as an INNER JOIN due to your tc.PostageCode = 'Package' clause. If you compare to a column value in a table in a WHERE clause (and there are no OR clauses and you aren't comparing to NULL) then effectively all joins to get to that table will be treated as INNER).

That clause will never be true if TableB is null (which is why you use LEFT OUTER JOIN vs INNER JOIN) - so you should just use an INNER JOIN to make the problem simpler.

Multiple Left Join and Inner Join with LinQ

According to Microsoft documentation, the query will be :

var queryEncFor = (from enc in db.encf
                   join forF in db.forf on
                   new
                   {
                       enc.numero,
                       enc.manual,
                       enc.comproba,
                       enc.tipo_fac,
                       enc.sucursal
                   }
                   equals
                   new
                   {
                       forF.numero,
                       forF.manual,
                       forF.comproba,
                       forF.tipo_fac,
                       forF.sucursal
                   }
                   join plane in db.Planes on forF.num_pla equals plane.Numero into leftedPlanes
                   from leftedPlane in leftedPlanes.DefaultIfEmpty()

                   join tarjeta in db.Tarjetas on leftedPlane?.IdTarjeta equals tarjeta.ID into leftedTarjetas
                   from leftedTarjeta in leftedTarjetas.DefaultIfEmpty()

                   where enc.documento == 1
                   select new UnionEncForFact
                   {
                       numero = enc.numero,
                       cuo_pla = forF.cuo_pla == null ? 0 : forF.cuo_pla.Value,
                       num_pla = forF.num_pla,
                       Descripcion = leftedPlane?.Descripcion,
                       Nombre = leftedTarjeta?.Nombre ?? 0
                   }).ToList();

I hope you find this helpful.

Which method is better for multiple join and left join by Linq?

First, the SQL difference is because the two queries are not equivalent.

In the first query:

join t4 in Table4 on t3.Id2 equals t4.Id2 into t4

is actually a group join which produces a different shape in the projection (t4 is collection).

If you convert it to the left outer join LINQ pattern:

join t4 in Table4 on t3.Id2 equals t4.Id2 into t4Group
from t4 in t4Group.DefaultIfEmpty()

the generated SQL will be one and the same (depending on the query provider).

Now about performance. Since LINQ has no special left outer join operator, the first is the "officially" agreed LINQ pattern for implementing such operator. And LINQ to Objects it's definitely the way to go, because the Enumerable.Join implementation uses fast hash based lookup to perform the correlation woit O(n + m) time complexity, while from ... .Where(...).DefaultIfEmpty() is basically SelectMany with linear search operation with O(m * n) complexity.

For IQueryable<T> implementations, it really depends on the query provider. For EF6 provider both constructs are equivalent and gets translated to one and the same SQL. For EF Core - well, currently it's still in development and has many bugs and issues, especially with left joins, so it's hard to give you a general advice.

So for IQueryable<T> implementations, always check the query provider capabilities - what is supported or not, how it's translated / processed etc (LINQ is sort of broken in that regard).

In general I would say use the first pattern. Essential in LINQ to Objects and hopefully should be recognized by any good query provider.

But specifically for EF6, it's just a matter of a taste - since EF6 is smart enough to recognize and translate them to one and the same SQL, the performance is also one and the same.

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

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)

---

Related Topics