Linq 查询错误

Question

我有以下 Linq:

var ownerRegistryId = 731752693037116688;
var excludeTypes = new[]
{
    "CA00", "CA01", "CA03", "CA04", "CA02",
    "PA00", "PA01", "PA02", "PA03", "PA04"
};

var maxStateChangeMonth = 4;
var excludeStatusId = 999;
var includeMortgage = new[] { "CL10", "CL11", "PL10", "PL11" };

 var sum = (
    from account in context.Accounts
    from owner in account.AccountOwners
    where owner.AccountOwnerRegistryId == ownerRegistryId
    where !excludeTypes.Contains(account.AccountType)
    where account.StateChangeDate == null ||
        (account.StateChangeDate.Month - DateTime.Now.Month)
            <= maxStateChangeMonth
    where includeMortgage.Contains(account.AccountType) ||
            account.AccountType.Contains("Mortgage")
    where account.AccountStatusId != excludeStatusId
    select account.MinimumInstallment)
    .Sum(minimumInstallment => Math.Abs(minimumInstallment));

但出现错误：

转换为值类型“Decimal” 失败是因为物化值 为空。结果类型的 通用参数或查询必须 使用可为 null 的类型。

一旦我添加以下内容，就会出现此错误：

where (includeMortgage.Contains(account.AccountType) ||
    account.AccountType.Contains("Mortgage"))

如果我从上面的查询中删除此内容，它就会起作用。

该查询是以下 SQL 的翻译：

    SELECT Sum(ABS([Minimum Installment])) AS SumOfMonthlyPayments FROM tblAccount 
    INNER JOIN tblAccountOwner ON tblAccount.[Creditor Registry ID] = tblAccountOwner.
    [Creditor Registry ID] AND tblAccount.[Account No] = tblAccountOwner.[Account No] 
    WHERE (tblAccountOwner.[Account Owner Registry ID] = 731752693037116688)
     AND (tblAccount.[Account Type] NOT IN 
    ('CA00', 'CA01', 'CA03', 'CA04', 'CA02', 'PA00', 'PA01', 'PA02', 'PA03', 'PA04'))
    AND (DATEDIFF(mm, tblAccount.[State Change Date], GETDATE()) <= 
   4 OR tblAccount.[State Change Date] IS NULL AND ((tblAccount.[Account Type] IN ('CL10','CL11','PL10','PL11')) OR
    tblAccount.[Account Type] LIKE 'Mortgage')) AND (tblAccount.[Account Status ID] <> 999)

Answer 1

我会尝试像这样重写查询的最后两行：

var sum = (
...
select account)
.Sum(a => Math.Abs(a.MinimumInstallment));

这就是我解释异常“...或者查询必须使用可为空类型”的这一部分的方式。通过使用投影select account.MinimumInstallment，您将获得一个不可为 null 的类型，即 decimal，它是 account.MinimumInstallment 的类型。

但不确定，只是猜测。

编辑

问题实际上可能是最终赋值var sum = ...。由于您没有明确指定结果类型，编译器将在此处将类型推断为十进制，因为 MinimumInstallment 是十进制。当所选记录集为空时，查询实际上可以返回 null，因此不可能转换为十进制。

因此，让我们帮助编译器将查询的结果类型推断为 decimal？：（

var sum = (decimal?)(from ... ) ?? 0;

将 from ... 替换为您的原始查询或我上面的修改版本。 )

编辑 2

好的，第一个编辑不起作用（根据另一个问题中的评论）。事实上，我可以在类似的例子中重现这个问题。但以下内容在我的示例中有效：

var sum = (
    ...
    select account)
    .Sum(a => (decimal?)Math.Abs(a.MinimumInstallment))
    .GetDefaultOrValue();

Answer 2

尝试使用：
Math.Abs​​((decimal)(minimumInstallment.HasValue ? minimumInstallment : 0));

怎么样：

 Math.Abs((decimal)(minimumInstallment!= null ? minimumInstallment : 0));