转换为值类型 'Int32' 失败，因为具体化值为 null [英] The cast to value type 'Int32' failed because the materialized value is null

问题描述

我有以下代码.我收到错误:

I have the following code. I'm getting error:

转换为值类型 'Int32' 失败，因为具体化值为 null.结果类型的泛型参数或查询必须使用可空类型."

"The cast to value type 'Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type."

CreditHistory 表没有记录时.

when CreditHistory table has no records.

var creditsSum = (from u in context.User
                  join ch in context.CreditHistory on u.ID equals ch.UserID                                        
                  where u.ID == userID
                  select ch.Amount).Sum();

如何修改查询以接受空值?

How can I modify the query to accept null values?

推荐答案

linq-to-sql 查询不是作为代码执行，而是转换为 SQL.有时这是一个泄漏的抽象"，会产生意想不到的行为.

A linq-to-sql query isn't executed as code, but rather translated into SQL. Sometimes this is a "leaky abstraction" that yields unexpected behaviour.

一个这样的情况是空值处理，在不同的地方可能会出现意外的空值....DefaultIfEmpty(0).Sum(0) 可以在这种(非常简单)的情况下提供帮助，其中可能没有元素并且 sql 的 SUM 返回 null 而 c# 期望为 0.

One such case is null handling, where there can be unexpected nulls in different places. ...DefaultIfEmpty(0).Sum(0) can help in this (quite simple) case, where there might be no elements and sql's SUM returns null whereas c# expect 0.

一种更通用的方法是使用 ??，只要有生成的 SQL 返回意外空值的风险，它就会被转换为 COALESCE:

A more general approach is to use ?? which will be translated to COALESCE whenever there is a risk that the generated SQL returns an unexpected null:

var creditsSum = (from u in context.User
              join ch in context.CreditHistory on u.ID equals ch.UserID                                        
              where u.ID == userID
              select (int?)ch.Amount).Sum() ?? 0;

这首先转换为 int? 以告诉 C# 编译器该表达式确实可以返回 null，即使 Sum() 返回一个int.然后我们使用普通的 ?? 操作符来处理 null 的情况.

This first casts to int? to tell the C# compiler that this expression can indeed return null, even though Sum() returns an int. Then we use the normal ?? operator to handle the null case.

基于这个答案，我写了一篇博文 包含 LINQ to SQL 和 LINQ to Entities 的详细信息.

Based on this answer, I wrote a blog post with details for both LINQ to SQL and LINQ to Entities.

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