Linq Left Join and Right Join

LINQ Left Join And Right Join

The example data and output you've provided does not demonstrate a left join. If it was a left join your output would look like this (notice how we have 3 results for user 1, i.e. once for each Growth record that user 1 has):

User | age| Data |Growth
------------------------                           
1    |2   |43.5  |46.5                           
1    |2   |43.5  |49.5     
1    |2   |43.5  |48.5     
2    |3   |44.5  |                          
3    |4   |45.6  |

Assuming that you still require a left join; here's how you do a left join in Linq:

var results = from data in userData
              join growth in userGrowth
              on data.User equals growth.User into joined
              from j in joined.DefaultIfEmpty()
              select new 
              {
                  UserData = data,
                  UserGrowth = j
              };

If you want to do a right join, just swap the tables that you're selecting from over, like so:

var results = from growth in userGrowth
              join data in userData
              on growth.User equals data.User into joined
              from j in joined.DefaultIfEmpty()
              select new 
              {
                  UserData = j,
                  UserGrowth = growth
              };

The important part of the code is the into statement, followed by the DefaultIfEmpty. This tells Linq that we want to have the default value (i.e. null) if there isn't a matching result in the other table.

LINQ : Left Join And Right Join with a double selection

If you have a SQL where you see a join followed by a GroupJoin, consider using the LINQ GroupJoin.

Quite often you'll see this in situations where you want "Schools with their Students", "Customers with their Orders", "Zoos with their Animals"

It seems that you have 3 tables: Parts, OutputControls and Fcts.

Every Part has zero or more OutputControls, and every OutputControl belongs to exactly one Part, using foreign key PartId: a straightforward one-to-many relation

A Part has a foreign key FctId, that points to the Fct of the part.

You want (some properties of) the Parts, with their OutputControls and its Fct

var result = parts.GroupJoin(outputControls,   // GroupJoin Parts and OutputControls
    part => part.Id,                           // from every part take the Id
    outputControl => outputControl.PartId,     // from every outputControl take the PartId

    // result selector: when these Ids match,
    // use the part and all its matching outputControls to make one new object:
    (part, outputControlsOfThisPart) => new
    {
        // select the part properties you plan to use:
        Id = part.id,
        Plant = part.plant,
        Unit = part.unit

        // the output controls of this part:
        OutputControls = outputControlsOfThisPart.Select(outputControl => new
        {
             // again, select only the output control properties you plan to use
             Id = outputControl.Id,
             Name = outputControl.Name,
             ...
        })
        .ToList(),

        // For the Fct, take the Fct with Id equal to Part.FctId
        Fct = Fcts.Where(fct => fct.Id == part.Fct)
                  .Select(fct => new
                  {
                      // select only the Fct properties you plan to use
                      Id = fct.Id,
                      Name = fct.Name,
                      ...
                  })
                  // expect only one such Fct
                  .FirstOrDefault(),
    });

linq left join with filtering in joined

Two Way solve Solution

Remove Where Case

Add where Case In Join table

Show for Where Case Blow Linq Query

first Query

IEnumerable<DTOHotMealsPrice> lst = (from m in this.dbEntity.HOT_MEALS
join ml in this.dbEntity.HOT_MEALS_PRICE.where(c=>c.HOTID==hotelId)   on m.MEALSID equals ml.MEALSID into mls
from mls1 in mls.DefaultIfEmpty()   
select new DTOHotMealsPrice
{
    MEALSID = m.MEALSID,
    MEALSNAME = m.MEALSNAME,
    CHPRICE = mls1.CHPRICE,
    PRICE = mls1.PRICE,
    HOTID = mls1.HOTID
}).Distinct().ToList();

Second is:

IEnumerable<DTOHotMealsPrice> lst = (from m in this.dbEntity.HOT_MEALS
join ml in this.dbEntity.HOT_MEALS_PRICE   on m.MEALSID equals ml.MEALSID into mls
from mls1 in mls.where(c=>c.HOTID==hotelId).DefaultIfEmpty()
select new DTOHotMealsPrice
{
    MEALSID = m.MEALSID,
    MEALSNAME = m.MEALSNAME,
    CHPRICE = mls1.CHPRICE,
    PRICE = mls1.PRICE,
    HOTID = mls1.HOTID
}).Distinct().ToList();

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.

linq: left join in linq with different type of conditions

Try like this;

(from s in db.PromotionalOffers
 join e in db.PromotionalOffersUsed on s.Id equals e.OfferId into joinT
 from e in joinT.DefaultIfEmpty()
 where s.ISVCSPId == 10 && (e == null || e.OfferId == null)
 select new { PromotionalOffers = s, PromotionalOffersUsed = joinT } ).ToListAsync();

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)

Implementation difference between left outer join and right outer join in LINQ

Here's a visual explanation of the different types of joins.

The major difference is that LEFT will keep all records from the 1st table (or left table), while RIGHT will keep all records from the 2nd table.

OUTER returns rows even if NULL values are found in the record.

---

Related Topics