Is there a way to set the default value(0 instead of null) for a group join IN THE LINQ QUERY itself?

Question

I got a LINQ query that includes two tables while some of the records aren't included in the child table. I'm already using GROUP JOIN with DEFAULTIFEMPTY() and it used to work before i added the Table ITM_WH which returns Numeric Sum. how could i handle this error? "The null value cannot be assigned to a member with type system.Decimal which is a non-nullable value type"? Knowing That using WH In ci.DefaultIfEmpty(0) does not work. i need the column "Stock" to carry the value 0 incase record does not exist

here is my query:

Dim result = From B In database.ITEM_BARCODEs 
            Join i In database.ITEMs On B.ITM_CODE Equals (i.ITM_CODE) 
           Group Join WH In database.ITM_WHs On i.ITM_CODE Equals 
          (WH.ITM_CODE) Into ci = Group From WH In ci.DefaultIfEmpty() Join 
    S In database.SUBGROUPs On i.ITM_SUBGROUP Equals (S.SubGrp_ID) 
        Join G In database.GROUPs On S.SubGrp_PARENTID Equals (G.GRP_ID) 
        Group Join P In database.ITEM_PHOTOs On i.ITM_PROFILE Equals 
     (P.PHOTO_ID) Into co = Group From p In co.DefaultIfEmpty()


       Select BarcodeID = B.BARCODE_ID,
            code = i.ITM_CODE, 
           barcode = B.ITM_BARCODE, 
           Title = i.ITM_TITLE, 
          Stock = WH.OPENING_QTY + WH.STOCK_IN - WH.STOCK_OUT,
           Main_Price = B.ITM_PRICE, Price_lvl_2,
           Subgroup = S.SubGrp_Name, 
           Group = G.GRP_NAME, 
           photo = p.ITM_PHOTO

score 0 · Answer 1 · answered Apr 23 '19 at 14:21

0

From what I can gather from your question, I think a simple join without the grouping might work for you, as demonstrated here: https://stackoverflow.com/a/3413732/2685093

answered Apr 23 '19 at 14:21

Nick

461
5
17

Is there a way to set the default value(0 instead of null) for a group join IN THE LINQ QUERY itself?

1 Answers1