木子屋
 

标题: 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.609 ms

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

你没有权限发表评论

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

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.609 ms

暂无评论

返回

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

木子屋

PJBlog3 v3.2.9.518 Inside.

Processed In 0.609 ms