linq - Linq 连接表和可为空的变量

Question

我正在尝试使用 db.ClincalAssets 表上的连接对 Ef6 进行 LINQ 查询，其中 AssetDisposalID 字段为空

即我不希望该表中 AssetDisposalID 不为空的数据行

以下代码似乎不起作用，因为它返回与我修改代码之前相同的 23 个结果

var today = DateTime.Now.Date;
            var tomorrow = today.AddDays(30);


            var dateresult = db.ClinicalINSs.GroupBy(d => d.ClinicalAssetID)
                       .SelectMany(g => g.OrderByDescending(d => d.NextInspectionDate)
                                         .Take(1));


            var ClinicalIDVM = (from s in dateresult.Where(q => q.NextInspectionDate <= tomorrow)
                                join co in db.ClinicalAssets.Where (t => t.AssetDisposalID == null) on  s.ClinicalAssetID  equals co.ClinicalAssetID into AR
                                let subred = AR.OrderByDescending(subredASS => subredASS.ClinicalAssetID).FirstOrDefault()
                                join cp in db.ClinicalPATs on s.ClinicalAssetID equals cp.ClinicalAssetID into AP
                                let subpat = AP.OrderByDescending(SubPATASS => SubPATASS.ClinicalAssetID).FirstOrDefault()
                                orderby s.NextInspectionDate descending


                                select new ClinicalIDVM
                                {
                                    ClinicalAssetID = s.ClinicalAssetID,
                                    ProductName = subred.ProductName ,
                                    SerialNo = subred.SerialNo == null ? "" : subred.SerialNo,
                                    InspectionDate = subpat.InspectionDate,
                                   NextInspectionDate = s.NextInspectionDate == null ? (DateTime?)null : s.NextInspectionDate,

                               }).ToList();

我也试过这个：

var today = DateTime.Now.Date;
           var tomorrow = today.AddDays(30);


           var dateresult = db.ClinicalINSs.GroupBy(d => d.ClinicalAssetID)
                      .SelectMany(g => g.OrderByDescending(d => d.NextInspectionDate)
                                        .Take(1));


           var ClinicalIDVM = (from s in dateresult from Disp in db.ClinicalAssets where s.NextInspectionDate <= tomorrow && Disp.AssetDisposalID == null

                               join co in db.ClinicalAssets on  s.ClinicalAssetID  equals co.ClinicalAssetID into AR
                               let subred = AR.OrderByDescending(subredASS => subredASS.ClinicalAssetID).FirstOrDefault()

                               join cp in db.ClinicalPATs on s.ClinicalAssetID equals cp.ClinicalAssetID into AP
                               let subpat = AP.OrderByDescending(SubPATASS => SubPATASS.ClinicalAssetID).FirstOrDefault()

                               orderby s.NextInspectionDate descending


                               select new ClinicalIDVM
                               {
                                   ClinicalAssetID = s.ClinicalAssetID,
                                   ProductName = subred.ProductName ,
                                   SerialNo = subred.SerialNo == null ? "" : subred.SerialNo,
                                   InspectionDate = subpat.InspectionDate,
                                  NextInspectionDate = s.NextInspectionDate == null ? (DateTime?)null : s.NextInspectionDate,

                              }).ToList();

但我得到了重复的记录。

标签： linqentity-framework-6