Allow null table in Group

Question

I'm trying to write a LINQ query in C# expression to get an overview for a Purchase Order which includes a table TblPODetails with the list of items in the purchase order. I want to get every line item in this table and then add to it the total amount of items which have been received which is recorded in the table TblPOReceipts. If there is no items received then TblPOReceipts shows null and the query returns 0 results.

How can I allow nulls in the TblPOReceipts and still get the rest of my results from TblPODetails?

This is my query:

from podetail in TblPODetails
from pricebook in TblPriceBooks.Where(x => x.ItemID == podetail.ItemID).DefaultIfEmpty()
from receipt in TblPOReceipts.Where(x => x.ItemID ==podetail.ItemID).DefaultIfEmpty()

where podetail.PONumber == 4707
where receipt.PONumber == 4707

group new { receipt, podetail, pricebook } by new {pricebook, podetail, receipt.QuantityReceived } into g

select new {
    g.Key.podetail.ItemDescription,
    TotalReceived = g.Sum (x => x.receipt.QuantityReceived),
    QuantityPosted = g.Where (x => x.receipt.Posted == true).Sum (x => x.receipt.QuantityReceived),
    g.Key.podetail.ItemID,
    g.Key.podetail.QuantityOrdered,
    g.Key.podetail.ProjectedCost,
    g.Key.podetail.TotalProjectedCost,
    g.Key.pricebook.BaseCost,
    g.Key.pricebook.ItemURL
}

Answer 1

It was a silly mistake in my query as I was using:

where receipt.PONumber == 4707

which was null and therefore returning no results.

I have fixed this as such:

where receipt.PONumber == null || receipt.PONumber == 4707

I have also cast the sum as an int.

    TotalReceived = (int?)g.Sum (x => x.receipt.QuantityReceived),
    QuantityPosted = (int?)g.Where (x => x.receipt.Posted == true).Sum (x => x.receipt.QuantityReceived),