error in Linq query

问题:

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)

回答1:

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();

回答2:

Try using:
Math.Abs((decimal)(minimumInstallment.HasValue ? minimumInstallment : 0));

How about:

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