Null reference when sumarizing totals from two joined tables

Question

For each part, I want to summarize the total onhand and allocated in the warehouse and on order in order lines.

I'm working with a simplified version in LINQPad:

void Main()
{
    var partQuantities = (
        from part in Parts()

        join orderLine in OrderLines()
            on part.PartID equals orderLine.PartID
            into orderLineLeftJoin 
        from orderLine in orderLineLeftJoin.DefaultIfEmpty()

        join warehouse in Warehouses()
            on part.PartID equals warehouse.PartID
            into warehouseLeftJoin 
        from warehouse in warehouseLeftJoin.DefaultIfEmpty()

        group new { warehouse, orderLine } by part into partQtys

 /*
        select partQtys
// */

// /*
        select new
        {
            partQtys.Key.PartID,
            OnHandQty = partQtys.Sum(partQty => partQty.warehouse.OnHandQty),
            AllocatedQty = partQtys.Sum(partQty => partQty.warehouse.AllocatedQty),
            OrderQty = partQtys.Sum(partQty => partQty.orderLine.OrderQty)
        }
// */
        ).Dump(); 
}

class Part { public string PartID; }
private Part[] Parts() { return new Part[] {new Part { PartID = "PartA" },new Part { PartID = "PartB" },new Part { PartID = "PartC" },new Part { PartID = "PartD" },new Part { PartID = "PartE" },new Part { PartID = "PartF" }}; }

class Warehouse { public string WarehouseID; public string PartID; public int OnHandQty; public int AllocatedQty; }
private Warehouse[] Warehouses() { return new Warehouse[] {new Warehouse { WarehouseID = "Whse1", PartID = "PartA", OnHandQty =101, AllocatedQty = 21 },new Warehouse { WarehouseID = "Whse1", PartID = "PartB", OnHandQty =102, AllocatedQty = 22 },new Warehouse { WarehouseID = "Whse1", PartID = "PartC", OnHandQty =103, AllocatedQty = 23 },new Warehouse { WarehouseID = "Whse1", PartID = "PartD", OnHandQty =104, AllocatedQty = 24 },new Warehouse { WarehouseID = "Whse2", PartID = "PartC", OnHandQty =105, AllocatedQty = 25 },new Warehouse { WarehouseID = "Whse2", PartID = "PartD", OnHandQty =106, AllocatedQty = 26 },new Warehouse { WarehouseID = "Whse2", PartID = "PartE", OnHandQty =107, AllocatedQty = 27 },new Warehouse { WarehouseID = "Whse2", PartID = "PartF", OnHandQty =108, AllocatedQty = 28 }}; }

class OrderLine { public string OrderID; public string PartID; public int OrderQty; }
private OrderLine[] OrderLines() { return new OrderLine[] {new OrderLine { OrderID = "Order1", PartID = "PartB", OrderQty = 71 }, new OrderLine { OrderID = "Order1", PartID = "PartF", OrderQty = 72 },new OrderLine { OrderID = "Order2", PartID = "PartD", OrderQty = 73 }, new OrderLine { OrderID = "Order2", PartID = "PartF", OrderQty = 74 }}; }

But this gets a NullReferenceException because not all parts have both warehouse stock and orders.

EDIT: I'd already ruled out Gilad's null propagating operator because the unsimplified version is an Expression Tree Lambda and I get a compile error. Though this would work in LinqPad.

How do I summarize this data from two tables?

I bet you faced *default* part of `DefaultIfEmpty` – Sergey Berezovskiy Jul 19 '17 at 10:57 — Sergey Berezovskiy, Jul 19 '17 at 10:57

score 4 · Accepted Answer · edited Sep 29 '17 at 14:17

4

Use Null Propagation ?. operator:

select new
{
    partQtys.Key.PartID,
    OnHandQty = partQtys.Sum(partQty => partQty.warehouse?.OnHandQty),
    AllocatedQty = partQtys.Sum(partQty => partQty.warehouse?.AllocatedQty),
    OrderQty = partQtys.Sum(partQty => partQty.orderLine?.OrderQty)
}

You can see explanation of the usage of it in a left join under DefaultIfEmpy

Following your comment and the error you get have a look at: Why can't I use the null propagation operator in lambda expressions? If that is really your scenario then use instead the ?: operator:

OnHandQty = partQtys.Sum(p => p.warehouse == null ? 0 : p.warehouse.OnHandQty)

edited Sep 29 '17 at 14:17

Graham

7,431
18
59
84

answered Jul 19 '17 at 10:58

Gilad Green

36,708
7
61
95

That works in LinqPad but unfortunately, in Visual Studio I get "CS8072 An expression tree lambda may not contain a null propagating operator." so I'd ruled that out. – Stephen Turner Jul 19 '17 at 11:02
1

@Anderson Pimentel - thanks for the fix :) missed that – Gilad Green Jul 19 '17 at 11:07
That's worked, thanks. I guess I assumed that the ?. was just syntactic sugar would be expanded by the compiler so didn't even try expanding it manually. – Stephen Turner Jul 19 '17 at 11:14
@StephenTurner - that's the process of learning :) Glad it helped – Gilad Green Jul 19 '17 at 11:15

score 1 · Answer 2 · edited Jul 19 '17 at 11:20

1

try code

 select new
    {
        partQtys.Key.PartID,
        OnHandQty = partQtys.Where(c=>c.warehouse!=null).Sum(partQty => partQty.warehouse.OnHandQty),
        AllocatedQty = partQtys.Where(c=>c.warehouse!=null).Sum(partQty => partQty.warehouse.AllocatedQty),
        OrderQty = partQtys.Where(c=>c.orderLine!=null).Sum(partQty => partQty.orderLine.OrderQty)
    }

edited Jul 19 '17 at 11:20

Stephen Turner

7,125
4
51
68

answered Jul 19 '17 at 11:11

kari kalan

497
3
20

thanks, this also works, though the extra check for nulls on the ints was unnecessary. – Stephen Turner Jul 19 '17 at 11:21

Null reference when sumarizing totals from two joined tables

2 Answers2