强制转换为值类型'；System.Int32'；失败，因为具体化的值为null
本文关键字：因为 具体化 null 失败 Int32 类型 转换 System | 更新日期: 2023-09-27 18:00:37
不过，我正试图返回一个结果列表。每当没有结果时，我就会收到我在上面发布的错误消息。然而，这很奇怪，因为每当我添加变量q而不是return时，它只会不返回任何结果，这很好。我更愿意按照我现在的方式来做，有人知道这个查询出了什么问题吗？每当我在LINQPad中运行它时，它都能完全正常工作。
public IQueryable<ClaimNumberReport> GetClaimsByClaimNumber(int ClientID, int ClaimID) {
/*var q = */ return (from d in camOnlineDb.Details
join a in camOnlineDb.Areas
on new { a = d.ClientID, b = d.AreaID ?? 0 }
equals new { a = a.ClientID, b = a.AreaID }
where d.ClientID == ClientID
join r in camOnlineDb.Reasons
on new { a = d.ClientID, b = d.ReasonID ?? 0 }
equals new { a = r.ClientID, b = r.ReasonID }
join sd in camOnlineDb.SuppDepts
on new { a = d.ClientID, b = d.CategoryID ?? 0 }
equals new { a = sd.ClientID, b = sd.CategoryID } into sdd
from sd in sdd.DefaultIfEmpty()
join h in camOnlineDb.Headers
on new { d.ClientID, d.ClaimID}
equals new { h.ClientID, h.ClaimID }
where h.ClaimID == ClaimID
join su in camOnlineDb.Suppliers
on new { h.ClientID, h.SupplierID }
equals new {su.ClientID, su.SupplierID }
join cp in camOnlineDb.ClaimPacks
on new { h.ClientID, h.ClaimID }
equals new { cp.ClientID, cp.ClaimID }
join rev in camOnlineDb.Reviews
on new { h.ClientID, h.ReviewID }
equals new { rev.ClientID, rev.ReviewID }
join revp in camOnlineDb.ReviewPeriods
on new { a = rev.ClientID, b = rev.ReviewPeriodID ?? 0 }
equals new { a = revp.ClientID, b = revp.ReviewPeriodID }
join st in camOnlineDb.Statuses
on new { a = d.ClientID, b = d.StatusID ?? 0 }
equals new { a = st.ClientID, b = st.StatusID }
join stcm in camOnlineDb.StatusCategoryMappings
on new { st.ClientID, st.StatusID }
equals new { stcm.ClientID, stcm.StatusID }
join stc in camOnlineDb.StatusCategories
on new { stcm.StatusCategoryID }
equals new { stc.StatusCategoryID }
where stc.StatusCategoryTypeID == 1
select new ClaimNumberReport {
TypeID = d.ClaimTypeID,
CPAttached = cp.FileName,
ReviewPeriodName = revp.ReviewPeriodName,
ClaimID = d.ClaimID,
Line = d.ClaimLine,
AccountNo = su.AccountNo,
SupplierName = su.SupplierName,
Amount = d.Amount,
Status = st.StatusDesc,
DateSent = d.DateSent,
DayOS = d.DaysOS,
NominalPeriod = d.NominalPeriod,
SLInvoiceNo = d.SLInvoiceNo,
Area = a.AreaDesc,
DebitRef = d.DebitFile,
DebitDate = d.JournalDate,
DeductDate = d.DeductDate,
StatusCategoryID = stc.StatusCategoryID,
StatusCategoryDesc = stc.StatusCategoryDesc,
APLReason = r.ReasonDesc,
ClientID = d.ClientID,
DeptNo = sd.DepartmentID,
DeptName = sd.DepartmentName,
Agreed = d.Agreed
});
/*return q;*/
}
此错误是由以下情况引起的：查询结果类型具有不可为null类型的列/属性，但生成的查询结果为NULL值。
这可能被认为是一个错误或不是。很难看出L2S团队在这里应该做些什么。我认为他们应该添加一个更好的错误信息。这个错误是阴险的，因为它有时只在不寻常的数据下出现在生产中。。。
您的左侧联接（sd）似乎不匹配，并且您选择的sd.*属性之一必须是int。这样解决：
DeptNo = (int?)sd.DepartmentID, //Cast to nullable
d.CategoryID ?? 0
你在这里干什么？这似乎是使联接可编译的一种方法。最好使用：
join r in camOnlineDb.Reasons
on new { a = d.ClientID, b = (int?)d.ReasonID }
equals new { a = r.ClientID, b = (int?)r.ReasonID }
此强制转换使匿名类型签名兼容。生成的SQL现在应该更快了。如果说x ?? 0可以转换为COALESCE(x, 0)，可以防止索引使用等等。