一聚教程网:一个值得你收藏的教程网站

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

时间:2014-12-20 00:00:00 编辑:简简单单 来源:转载

今天使用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.

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

在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 });

文章评论

热门栏目