EF Core Linq query with grouping: Why does DefaultIfEmpty() not work here?-CodePudding

I have two tables that I'm trying to group with the EF Core equivalent of a Left Join. Table "Orders" lists the total amount of orders placed for a given item in each month of the year:

     ITEM     |     JANUARY     |     FEBRUARY     |     MARCH     |
--------------------------------------------------------------------
     Item A         $5,000            $3,000             $6,000
     Item B         $1,000            $4,000             $5,000
     Item C         $7,000            $9,000             $2,000

Table "Categories" lists the items on offer:

     ITEM     
--------------
     Item A   
     Item B
     Item C
     Item D

I'm trying to join the two lists into a single output table, where all the items from "Categories" are listed along with all the figures given in "Orders", if any. A month column should show "$0" if no orders for the item were placed that month, or zeroes across the board if the item doesn't appear in the Orders table at all:

     ITEM     |     JANUARY     |     FEBRUARY     |     MARCH     |
--------------------------------------------------------------------
     Item A         $5,000            $3,000             $6,000
     Item B         $1,000            $4,000             $5,000
     Item C         $7,000            $9,000             $2,000
     Item D         $0                $0                 $0

My ef core linq query is below, linking Orders to Categories on the Item column. Per the MS documentation linked above, I'm selecting from the grouping using ".DefaultIfEmpty()", since I still want Item D to appear in my list even if it does not appear in the Orders table.

var output = (from c in categories 
                join o in orders on c.item equals o.item into grouping
                from b in grouping.DefaultIfEmpty()
                select new Output_Model
                {
                    product = c.product,
                    January = b.January,
                    February = b.February,
                    March = b.March,
                    April = b.April,
                    May = b.May,
                    June = b.June,
                    July = b.July,
                    August = b.August,
                    September = b.September,
                    October = b.October,
                    November = b.November,
                    December = b.December
                }).ToList();

However, when the query gets to Item D, the process fails with the "Object reference not set" error, and the monthly values throwing a System.NullReferenceException. I thought ".DefaultIfEmpty()" was supposed to handle this sort of thing? The month columns in Output_Model are set as nullable.

If I alter my query so that "Categories" is joined on "Orders" with the same criteria, the process completes successfully, but "Item D" is not included in the list. How can I get this working?

CodePudding user response：

Thanks to @NavoneelTalukdar for this one.

It turned out I need to handle my nullities individually, so after I added null handling to each item in the query, I got the results I was looking for.

var output = (from c in categories 
                join o in orders on c.item equals o.item into grouping
                from b in grouping.DefaultIfEmpty()
                select new 
                {
                    product = c.product,
                    January = b != null ? b.January : 0,
                    February = b != null ? b.February : 0,
                    March = b != null ? b.March : 0,
                    April = b != null ? b.April : 0,
                    May = b != null ? b.May : 0,
                    June = b != null ? b.June : 0,
                    July = b != null ? b.July : 0,
                    August = b != null ? b.August : 0,
                    September = b != null ? b.September : 0,
                    October = b != null ? b.October : 0,
                    November = b != null ? b.November : 0,
                    December = b != null ? b.December : 0
                }).ToList();