欢迎光临

木子屋

标题: LINQ to Entities尝试将NULL转为System.Int32出错一例

作者: dnawo

日期: 2014-12-19 10:35 PM

分类: Win编程

内容: 今天使用LINQ to Entities时出现了一个错误，为方便说明创建了个Demo表用于还原问题：
复制内容到剪贴板 程序代码create table Person
(
Id int identity(1,1) primary key,
Name nvarchar(20) not null,
Age int not null
)
insert into Person select 'person1',20
insert into Person select 'person2',30
insert into Person select 'person3',40
LINQ to Entities相关程序：
复制内容到剪贴板 程序代码var context = new testContext();
var data = context.People
.GroupBy(item => item.Name)
.Select(item => new { item.FirstOrDefault().Name, Age = item.Where(item2 => item2.Age >= 30).Sum(item2 => item2.Age) });
foreach (var item in data)
{
Console.WriteLine("{0},{1}", item.Name, item.Age);
}
程序执行出错，错误信息如下：
引用内容The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.
在SQL Server Profiler获取了对应SQL语句：
复制内容到剪贴板 程序代码Select
1 AS [C1],
[Project4].[C1] AS [C2],
[Project4].[C2] AS [C3]
FROM ( Select
[Project3].[C1] AS [C1],
(Select
SUM([Extent3].[Age]) AS [A1]
FROM [dbo].[Person] AS [Extent3]
Where ([Project3].[Name] = [Extent3].[Name]) AND ([Extent3].[Age] >= 30)) AS [C2]
FROM ( Select
[Distinct1].[Name] AS [Name],
(Select TOP (1)
[Extent2].[Name] AS [Name]
FROM [dbo].[Person] AS [Extent2]
Where [Distinct1].[Name] = [Extent2].[Name]) AS [C1]
FROM ( Select DISTINCT
[Extent1].[Name] AS [Name]
FROM [dbo].[Person] AS [Extent1]
) AS [Distinct1]
) AS [Project3]
) AS [Project4]
查询得到的记录集：
引用内容1 person1 NULL
1 person2 30
1 person3 40
所以，问题的原因是Where(item2 => item2.Age >= 30)导致了查询结果存在NULL值，在尝试将它转换成System.Int32时出了错，解决方法如下：
复制内容到剪贴板 程序代码var data = context.People
.GroupBy(item => item.Name)
.Select(item => new { item.FirstOrDefault().Name, Age = item.Where(item2 => item2.Age >= 30).Sum(item2 => (int?)item2.Age) ?? 0 });
参考资料
[1].Stack Overflow：http://stackoverflow.com/questions/6864311/the-cast-to-value-type-int32-failed-because-the-materialized-value-is-null

+ 查看当前日志评论 (0)

登录

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.547 ms

返回

标题: LINQ to Entities尝试将NULL转为System.Int32出错一例

你没有权限发表评论

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.547 ms

返回

暂无评论

返回

────────────

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.578 ms

返回