LINQ 左连接错误

Question

我在 LINQ 中编写了以下查询来执行左连接，但它抛出错误：

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 
          };

错误：

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,

请帮助我。

Answer 1

cp.DefaultIfEmpty() 返回一个序列，如果 cp 为空，则该序列将具有单个 null 值。

事实

from p in cp.DefaultIfEmpty()

这意味着您必须考虑到 p 中的可能为空这一 。现在，你还没有真正说出在这种情况下你想要发生什么。您可能想要这样的东西：

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 
          };

...或者您可能想要一些不同的处理。我们不知道 p.product_id 或 p.value 的类型，这没有帮助。 （例如，如果 product_id 是值类型，则需要对上述代码进行更多处理。）

Answer 2

您正在进行左连接，因此 p 可以为 null。你需要考虑到这一点。

这是应该有效的查询，尽管我不确定 p.value 是什么类型的值。如果值是引用类型，则查询将起作用。如果 value 是值类型，则使用类似于 product_id 转换的转换。

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,
            };

Answer 3

在 LINQ 查询中使用 LEFT JOIN 到多个表时，我遇到了同样的问题，并且遇到了空引用异常。我使用了使用“？”来检查空值的技巧。如果我的方法不正确，请纠正我。

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
                             };

Answer 4

使用模型类作为 DefaultIfEmpty() 函数的参数。

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