Win编程 订阅所有【Win编程】的日志

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

编辑:dnawo 日期:2014-12-19

字体大小: 小 中 大
今天使用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

上一篇: RichTextBox控件添加指定颜色文本示例
下一篇: PJBlog3 V3.2.9.518可恶意注册BUG解决方法
文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
最新日志:
评论: 0 | 引用: 0 | 查看次数: 5682
发表评论
请登录后再发表评论！