在LINQ查询错误 [英] error in Linq query

问题描述

我有以下的Linq：

  VAR ownerRegistryId = 731752693037116688;
VAR excludeTypes =新的[]
{
    CA00，CA01，CA03，CA04，CA02，
    PA00，PA01，PA02，PA03，PA04
};

变种maxStateChangeMonth = 4;
VAR excludeStatusId = 999;
VAR includeMortgage =新的[] {CL10，CL11，PL10，PL11};

 VAR之和=（
    从账户context.Accounts
    从老板account.AccountOwners
    其中，owner.AccountOwnerRegistryId == ownerRegistryId
    在那里！excludeTypes.Contains（account.AccountType）
    其中，account.StateChangeDate == NULL ||
        （account.StateChangeDate.Month  -  DateTime.Now.Month）
            ＆LT; = maxStateChangeMonth
    其中，includeMortgage.Contains（account.AccountType）||
            account.AccountType.Contains（按揭）
    其中，account.AccountStatusId！= excludeStatusId
    选择account.MinimumInstallment）
    .SUM（minimumInstallment =＆GT; Math.Abs​​（minimumInstallment））;
 

但我得到的错误：

  

演员到值类型十进制   失败的原因是物化的价值   为null。无论结果类型的   泛型参数或查询必须   使用可空类型。

这个错误，当我加入这个来了：

 其中（includeMortgage.Contains（account.AccountType）||
    account.AccountType.Contains（按揭））
 

如果我删除此从上面的查询，它的工作原理。

查询是以下SQL翻译：

  SELECT SUM（ABS（[最小分期付款]））AS SumOfMonthlyPayments从tblAccount
    INNER JOIN tblAccountOwner ON tblAccount。[债权人登记ID = tblAccountOwner。
    [债权人登记ID]和tblAccount。[帐号] = tblAccountOwner。[帐号]
    WHERE（tblAccountOwner。[帐户所有者注册ID = 731752693037116688）
     AND（tblAccount。[账户类型] NOT IN
    （'CA00'，'CA01'，'CA03'，'CA04'，'CA02'，'PA00'，'PA01'，'PA02'，'PA03'，'PA04'））
    和（DATEDIFF（毫米，tblAccount [状态更改日期]，GETDATE（））＆LT; =
   4或tblAccount。[状态更改日期] IS NULL AND（（tblAccount。[账户类型] IN（CL10，CL11，PL10，PL11））或
    tblAccount [帐户类型] LIKE'按揭'））AND（tblAccount [帐户状态ID]＆LT;＆GT; 999）
 

解决方案

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

  VAR之和=（
...
选择账户）
.SUM（一个=＆GT; Math.Abs​​（a.MinimumInstallment））;
 

这就是我跨$ P $私人这部分除......或查询必须使用可空类型。通过使用投影选择account.MinimumInstallment 你有一个非可空类型，即十进制这是类型 account.MinimumInstallment 。

不知道，虽然，只是猜测。

修改

这个问题实际上可能最终作业 VAR总和= ... 。既然你不指定结果类型明确地编译器将在这里推出其类型十进制，因为 MinimumInstallment 是小数。查询其实可以返回空时选定的记录是空的，所以要转换成十进制是不可能的。

那么，让我们帮助编译器来推断查询的结果类型十进制：

  VAR之和=（十进制？）（从...）？ 0;
 

（替换从... 由原始查询或者通过我上面的修改后的版本。）

编辑2

OK，先编辑没有（根据在另一个问题发表意见）的工作。事实上，我可以在一个类似的例子重现该问题。但在我的例子以下工作：

  VAR之和=（
    ...
    选择账户）
    .SUM（A =＆GT;？（十进制）Math.Abs​​（a.MinimumInstallment））
    .GetDefaultOrValue（）;
 

I have following 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));

but I get the error:

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

this error comes as soon as I add this:

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

If I remove this from above query, it works.

The query is translation of following 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)

解决方案

I'd try to rewrite the last two lines of your query like so:

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

That's how I interprete this part of the exception "...or the query must use a nullable type". By using the projection select account.MinimumInstallment you have a non-nullable type, namely decimal which is the type of account.MinimumInstallment.

Not sure though, just a guess.

Edit

The problem might actually be the final assignment var sum = .... Since you don't specify the result type explicitely the compiler will here infer the type to decimal because MinimumInstallment is decimal. The query can actually return null when the selected recordset was empty so the cast to decimal is impossible.

So, let's help the compiler to infer the result type of the query to decimal?:

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

(Replace from ... by your original query or maybe by my modified version above.)

Edit 2

OK, the first Edit didn't work (according to comment in another question). Indeed I could reproduce the issue in a similar example. But the following worked in my example:

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

这篇关于在LINQ查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！