LINQ : Left Join And Right Join with a double selection

Question

I am looking for a solution to have all the content of the table PART (by adding a right/left join I suppose) in the following LINQ query :

var query = (from p in db.PARTS
             join oc in db.OUTPUT_CONTROLS on p.id equals oc.partid
             join f in db.FCT on p.fct equals f.id
             select new
             { p.id, p.plant, p.unit, p.type, p.num, f.name, oc.datetime, oc.ncr } 
             into x
             group x by new 
             { x.id, x.plant, x.unit, x.type, x.num, x.name } 
             into g
             select new 
             { g.Key.id, g.Key.plant, g.Key.unit, g.Key.type, g.Key.num, g.Key.name, startdate = g.Min(oc => oc.datetime), endate = g.Max(oc => oc.datetime), sumncr = g.Sum(oc => oc.ncr) })
             .OrderByDescending(oc => oc.startdate);

Thanks

score 1 · Answer 1 · answered Feb 20 '19 at 15:02

I found the solution on my own thanks to this post : LINQ Left Join And Right Join

The solution :

var query = (from p in db.PARTS
                             join oc in db.OUTPUT_CONTROLS on p.id equals oc.partid into joined
                             join f in db.FCT on p.fct equals f.id
                             from j in joined.DefaultIfEmpty()
                             select new
                             { p.id, p.plant, p.unit, p.type, p.num, f.name, j.datetime, j.ncr } into x
                             group x by new { x.id, x.plant, x.unit, x.type, x.num, x.name } into g
                             select new { g.Key.id, g.Key.plant, g.Key.unit, g.Key.type, g.Key.num, g.Key.name, startdate = g.Min(oc => oc.datetime), endate = g.Max(oc => oc.datetime), sumncr = g.Sum(oc => oc.ncr) })
                             .OrderByDescending(oc => oc.startdate);

score 0 · Accepted Answer · answered Feb 21 '19 at 08:39

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 And Right Join with a double selection

2 Answers2