Lambda left join with rows

Question

I have two tables. A table called Order and a table called OrderRows.

An Order can have zero or more OrderRows.

I want to query all Orders and do a Sum for all OrderRows that belong to that Order.

I do that like this:

var model = await _dbContext.Orders
    .Join(_dbContext.OrderRows, o => o.Id, or => or.OrderId, (o, or) => new {o, or})
    .GroupBy(x => new
    {
        x.o.Id,
        x.o.Name
    })
    .Select(g => new CustomDto
    {
        Id = g.Key.Id,
        Name = g.Key.Name,
        TotalPrice = g.Sum(x => x.wkr.Price)
    }).ToListAsync();

This works fine for all Orders that have OrderRows. However, some Orders don't have any OrderRows (yet).

Right now the Orders that don't have any OrderRows, are not included in the result.

In those cases I still want to have them in my result, but with a TotalPrice of 0.

What do I have to change in my Lambda query?

You need left join. See here: https://stackoverflow.com/a/21584913/809357 — trailmax, Aug 08 '19 at 13:00
First I'd suggest using navigation properties. I assume `Orders` has a `OrderRows` property you can use instead of joining. — juharr, Aug 08 '19 at 13:02
Indeed- it's the *ORM*'s job to create JOINs from the navigation properties. LINQ isn't a replacement for SQL and domain entities aren't tables. — Panagiotis Kanavos, Aug 08 '19 at 13:03
With proper navigation properties you could write `from order in Orders from row in order.Rows group row by order into g select new {g.Key.Id, g.Key.Name,Sum=g.Sum(x=>x.wkr.Price)}` — Panagiotis Kanavos, Aug 08 '19 at 13:14
Writing the same thing using methods `Orders.SelectMany(order=>order.Rows).GroupBy(row=>row.Order).Select(g=>new {g.Key.Id,g.Key.Name,Sum=g.Sum(x=>x.wkr.Price)});` — Panagiotis Kanavos, Aug 08 '19 at 13:16

score 1 · Accepted Answer · answered Aug 08 '19 at 13:00

You can use simple Select without grouping. Just calculate TotalPrice as sub-query:

var model = await _dbContext.Orders.Select(o => new CustomDto
    {
      Id = o.Id,
      Name = o.Name,
      TotalPrice = _dbContext.OrderRows.Where(or => or.OrderId == o.Id).Sum(or => or.wkr.Price)
    }).ToListAsync();

I've not tested it, but hope that idea is clear

Lambda left join with rows

1 Answers1