Error in LINQ Left JOIN

Question

i have written below query in LINQ to perform left join but its throwing error:

var qry = from c in dc.category_feature_Name_trans_SelectAll_Active()
          join p in dc.product_category_feature_trans_SelectAll()
          on c.cft_id equals p.cft_id into cp
          from p in cp.DefaultIfEmpty()                      
          select new
          {
              c.cft_id,
              c.feature_id,
              c.feature_name,
              p.product_id ,
              p.value 
          };

Error:

Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the 
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 57:                       on c.cft_id equals p.cft_id into cp
Line 58:                       from p in cp.DefaultIfEmpty()                      
error Line 59:                       select new
Line 60:                       {
Line 61:                           c.cft_id,

Please help me.

Uitlander

Answer 1

cp.DefaultIfEmpty() returns a sequence which will have a single null value in if cp was empty.

That means you have to account for the fact that the p in

from p in cp.DefaultIfEmpty()

may be null. Now, you haven't really said what you want to happen in that case. You might want something like this:

var qry = from c in dc.category_feature_Name_trans_SelectAll_Active()
          join p in dc.product_category_feature_trans_SelectAll()
          on c.cft_id equals p.cft_id into cp
          from p in cp.DefaultIfEmpty()                      
          select new
          {
              c.cft_id,
              c.feature_id,
              c.feature_name,
              product_id = p == null ? null : p.product_id,
              value = p == null ? null : p.value 
          };

... or you may want some different handling. We don't know the types of p.product_id or p.value, which doesn't help. (For example, you'll need a bit more work with the above code if product_id is a value type.)

Wilk

product_id = p == null ? null : p.product_id will not compile, if product_id is int – Spaceport

@Alex: Which is why I wrote the last sentence... your version won't compile if product_id is a string :) – Wilk

no error... as usual Jon always comes with correct answer with proper explanation. Thanks Jon. – Uitlander

Answer 2

You are making left join, so p can be null. You need to account for that.

Here is the query that should work, although I don't know for sure what kind of value is p.value. The query will work if value is a reference type. If value is value type, than use the cast similar to product_id cast.

var qry = from c in dc.category_feature_Name_trans_SelectAll_Active()
            join p in dc.product_category_feature_trans_SelectAll()
            on c.cft_id equals p.cft_id into cp
            from p in cp.DefaultIfEmpty()
            select new
            {
                c.cft_id,
                c.feature_id,
                c.feature_name,
                product_id = p == null ? (int?)null : p.product_id,
                value = p == null ? null : p.value,
            };

Spaceport

Answer 3

I am facing the same issue while using the LEFT JOIN to multiple tables in LINQ query and I was facing null reference exception. I used the trick to check the null value using the "?" Please correct me if my approach is incorrect.

var deviceResultDetails = from pa in programAliasrecords
                          join pgm in Newprogramrecords on pa.program_id equals pgm.id into pgm_join
                          from pgm2 in pgm_join.DefaultIfEmpty()
                          join latest_fw in firmwareWithIdrecords on pgm2?.latest_firmware_id equals latest_fw?.id into latest_fw_join
                          from latest_fw2 in latest_fw_join.DefaultIfEmpty()
                          join dev_fw in firmwareWithIdrecords on pgm2?.firmware_group_id equals dev_fw?.firmware_group_id into dev_fw_join
                          from dev_fw2 in dev_fw_join.DefaultIfEmpty()
                          join vv in vulnerabilityrecords on pgm2?.id equals vv?.program_id into vv_join
                          from vv2 in vv_join.DefaultIfEmpty()
                          where 
                          dev_fw2?.version == row.firmware    
                          && pa?.keyword == row.model      

                             select new _deviceResults
                             {
                                 model = row.model, 
                                 serial = row.serial, 
                                 firmware = row.firmware, 
                                 firmware_date = dev_fw2.br_date == null ? null: dev_fw2.br_date,
                                 latest_firmware = latest_fw2.version == null ? null : latest_fw2.version,
                                 latest_firmware_date = latest_fw2.br_date == null ? null : latest_fw2.br_date,

                                 status = Convert.ToInt32(vulnerability_count) > 0 && pgm2.out_of_support == "TRUE" ? "Vulnerable (End of Suport)" :
                                          Convert.ToInt32(vulnerability_count) > 0 && pgm2.out_of_support == " " ? "Vulnerable (Upgradeable)" :
                                                 pgm2.out_of_support == "TRUE" ? "Out-of-support" :
                                                 Convert.ToInt32(dev_fw2.revs_out_of_date) > 1 ? "Out-of-date" :
                                                 pgm2.id == "NonHP" ? "NonHP" :
                                                 pgm2.id == "NoFirmware" ? "No Firmware" :
                                                 pgm2.id == "HPink" || pgm2?.id == "HPOther" ? "Not evaluated (model not included yet)" :
                                                 pgm2.id == " " ? "Not evaluated (model not recognized)" :
                                                 dev_fw2.version == " " ? "Not evaluated (firmware version missing)" :
                                                 dev_fw2.id == " " ? "Not evaluated (firmware version mismatch)" :    // && dev_fw.id  in (select version from firmware) 
                                                 dev_fw2.id == " " ? "Not evaluated (firmware version unrecognized)" :
                                                 dev_fw2.br_date == " " || pgm2.id == " " || dev_fw2.revs_out_of_date == " " ? "Not evaluated" : "OK",

                                 out_of_support = pgm2.out_of_support == "" ? "false" : pgm2.out_of_support,
                                 revs_out_of_date = dev_fw2.revs_out_of_date == null ? null : dev_fw2.revs_out_of_date,
                                 program_id = pgm2.id == null ? null :  pgm2.id,
                                 firmware_id = dev_fw2.id == null ? null : latest_fw2.br_date
                             };

Gravity

Answer 4

Use your Model Class as a parameter for DefaultIfEmpty() function.

from p in cp.DefaultIfEmpty(new yourModelClass())

Armory