执行LINQ查询时,我遇到了以下错误:
“转换为值类型'System.Int32'失败,因为实例值为空。”
我认为这是因为其中一个列返回了null。以下是我的LINQ命令:
这是我尝试实现以下T-SQL查询的结果:
nulls在AccreditationID列中。以下是关系:
“转换为值类型'System.Int32'失败,因为实例值为空。”
我认为这是因为其中一个列返回了null。以下是我的LINQ命令:
var facts = (from b in Program.db.ProductBrands
join bm in Program.db.BrandManufacturers on b.ProductBrandID equals bm.ProductBrandID
join c in Program.db.Companies on bm.ProductManufacturerID equals c.CompanyID
join s in Program.db.AnimalSources on b.AnimalCode equals s.AnimalCode
join ba in Program.db.BrandAccreditations on b.ProductBrandID equals ba.ProductBrandID into bax
from credJoins in bax.DefaultIfEmpty()
join a in Program.db.Accreditations on credJoins.AccreditationID equals a.AccreditationID into ax
from accreds in ax.DefaultIfEmpty()
join bt in Program.db.BrandTypes on b.ProductBrandID equals bt.BrandTypeID into btx
from brandJoins in btx.DefaultIfEmpty()
join t in Program.db.ProductTypes on brandJoins.ProductTypeID equals t.ProductTypeID into tx
from types in tx.DefaultIfEmpty()
select new { c.CompanyID, types.ProductTypeID, b.ProductBrandID, accreds.AccreditationID, s.AnimalName }).Distinct();
这是我尝试实现以下T-SQL查询的结果:
SELECT DISTINCT c.CompanyID, b.ProductBrandID, s.AnimalName, a.AccreditationID, t.ProductTypeID
FROM dbo.ProductBrand b
INNER JOIN dbo.BrandManufacturer bm ON b.ProductBrandID = bm.ProductBrandID
INNER JOIN dbo.Company c ON bm.ProductManufacturerID = c.CompanyID
INNER JOIN dbo.AnimalSource s ON b.AnimalCode = s.AnimalCode
LEFT OUTER JOIN dbo.BrandAccreditation ba ON b.ProductBrandID = ba.ProductBrandID
LEFT OUTER JOIN dbo.Accreditation a ON ba.AccreditationID = a.AccreditationID
LEFT OUTER JOIN dbo.BrandType bt ON b.ProductBrandID = bt.ProductBrandID
LEFT OUTER JOIN dbo.ProductType t ON bt.ProductTypeID = t.ProductTypeID;
nulls在AccreditationID列中。以下是关系:
我的问题实际上有两个部分:
- 我是否已正确将T-SQL查询转换为LINQ?
- 当我实际上希望出现nulls(因此是左外连接)时,如何解决与nulls相关的问题?
谢谢。
Accreditations.AccreditationID
定义为int?
?另一种选择可能是选择AccreditationID = accreds?.AccreditationId
而不是简单地选择accreds.AccredidationId
。 - Rob