VB.NET中Linq多Where子句组合后无数据返回问题求助

问题出在你用了两个独立的Where子句——这相当于把两组条件用逻辑与（AND）连接，但第一组要求Not r.ReportType = "ZERO REPORT"，第二组却要求r.ReportType.Equals("ZERO REPORT")，这两个条件不可能同时成立，自然返回0条记录。你实际需要的是两组条件的并集（70条+5条），所以应该把两组条件用**逻辑或（OR）**合并到同一个Where子句里。

修正后的代码：

submissionDetails = (From r In model.NIBRS_ReportStatusByORI
                     Where (sCritera.ORI.Contains(r.OriginatingORI) _
                                And Not r.ReportType = "ZERO REPORT" _
                                And r.OccurrenceDate >= sCritera.BeginDate _
                                And r.OccurrenceDate <= sCritera.EndDate _
                                And r.ActionCode <> "D")
                     OrElse (sCritera.ORI.Contains(r.OriginatingORI) _
                                And r.ReportType.Equals("ZERO REPORT") _
                                And r.YearMonthDate >= sCritera.BeginDate _
                                And r.YearMonthDate <= sCritera.EndDate _
                                And r.ActionCode <> "D")
                     Select New ReportStatusDetails() With {
                        .ChangeDate = r.Insertdate,
                        .IncidentId = r.IncidentID,
                        .IncidentIdentifier = r.IncidentIdentifier,
                        .OriginatingORI = r.OriginatingORI,
                        .ReportType = r.ReportType,
                        .StatusID = r.StatusID,
                        .SubmittedBy = r.Username,
                        .ReportDate = r.YearMonthDate,
                        .ReportDateString = r.YearMonth,
                        .OccurrenceDate = r.OccurrenceDate
               }).ToList()

你还可以优化重复条件，把sCritera.ORI.Contains(r.OriginatingORI)和r.ActionCode <> "D"抽出来，让代码更简洁：

submissionDetails = (From r In model.NIBRS_ReportStatusByORI
                     Where sCritera.ORI.Contains(r.OriginatingORI) _
                           And r.ActionCode <> "D" _
                           And ((Not r.ReportType = "ZERO REPORT" _
                                And r.OccurrenceDate >= sCritera.BeginDate _
                                And r.OccurrenceDate <= sCritera.EndDate) _
                                OrElse (r.ReportType.Equals("ZERO REPORT") _
                                And r.YearMonthDate >= sCritera.BeginDate _
                                And r.YearMonthDate <= sCritera.EndDate))
                     Select New ReportStatusDetails() With {
                        .ChangeDate = r.Insertdate,
                        .IncidentId = r.IncidentID,
                        .IncidentIdentifier = r.IncidentIdentifier,
                        .OriginatingORI = r.OriginatingORI,
                        .ReportType = r.ReportType,
                        .StatusID = r.StatusID,
                        .SubmittedBy = r.Username,
                        .ReportDate = r.YearMonthDate,
                        .ReportDateString = r.YearMonth,
                        .OccurrenceDate = r.OccurrenceDate
               }).ToList()

这样既保留了原本两组条件的逻辑，又避免了重复判断相同条件，同时确保两组条件是或的关系，就能得到70+5=75条记录的结果。

内容的提问来源于stack exchange，提问作者zwheeler