LINQ:强制转换为值类型'System.Int32'，因为物化值为null [英] LINQ: The cast to value type 'System.Int32' failed because the materialized value is null

问题描述

在数据库上执行LINQ查询时出现以下错误:

I'm getting the following error when executing a LINQ query on my database:

强制转换为值类型'System.Int32'，因为已实现值为空.

The cast to value type 'System.Int32' failed because the materialized value is null.

我相信这是因为其中一列返回空值.这是我的LINQ命令:

I believe it's because one of the columns is returning a null. Here is my LINQ command:

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查询:

Which is my attempt to implement the following T-SQL query:

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;

空值是AccreditationID列中的.这里是关系:

The nulls are the in the AccreditationID column. Here are the relationships:

我的问题实际上分为两部分:

My question is really in two parts:

1. 我是否已将T-SQL查询正确转换为LINQ?
2. 当我实际上希望有null值(因此左边的外部联接)时，如何解决与null值有关的问题?

谢谢.

推荐答案

(1)我是否已将T-SQL查询正确转换为LINQ?

(1) Have I correctly translated my T-SQL query into LINQ?

是的，您正确地做到了.

Yes, you did it correctly.

旁注(与主要问题无关):进行左外部连接时，无需使用其他名称.使用 into 子句后，用于访问实体记录的名称已超出范围，可以重复使用，这使得查询的其余部分看起来都很相似，无论联接类型如何( inner 或左外).例如

A side note (unrelated to the main issue): There is no need to use different names when doing left outer join. Once you use into clause, the name used to access the entity record is out of scope and can be reused, which makes the rest of the query look similar regardless of the join type (inner or left outer). For instance

join ba in Program.db.BrandAccreditations on b.ProductBrandID equals ba.ProductBrandID into bax
from credJoins in bax.DefaultIfEmpty()

可能是

join ba in Program.db.BrandAccreditations on b.ProductBrandID equals ba.ProductBrandID into baJoin
from ba in baJoin.DefaultIfEmpty()

如果从行中删除 in 和以下行，它将成为 inner join ，反之亦然.

If you remove into and the following from line, it will become inner join and vice versa.

(2)当我实际上期望空值(因此左边的外部联接)时，如何解决与空值有关的问题?

(2) How to I solve the problem related to nulls when I in facts expect nulls (hence the left outer joins)?

这是带有左联接和值类型字段的典型错误.实际上，完整的错误消息包含解决方案:

This is a typical error with left join and value type fields. Actually the full error message contains the solution:

结果类型的通用参数或查询必须使用可空类型.

换句话说，找到来自 left外部联接的 right 侧的不可为空的值类型字段，然后将它们转换为它们的可为空的等效值.

In other words, locate the non nullable value type fields that come from the right side of a left outer join and just convert them to their nullable equivalent.

在您的情况下，在这里:

In your case, here:

select new { c.CompanyID, types.ProductTypeID, b.ProductBrandID, accreds.AccreditationID, s.AnimalName }

此类字段是 types.ProductTypeID 和 accreds.AccreditationID ，因此解决方法是(假设它们的类型为 int ):

such fields are types.ProductTypeID and accreds.AccreditationID, so the fix is (assuming they are of type int):

select new { c.CompanyID, (int?)types.ProductTypeID, b.ProductBrandID, (int?)accreds.AccreditationID, s.AnimalName }

这篇关于LINQ:强制转换为值类型'System.Int32'，因为物化值为null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！