Linq Sum() precision

Question

In my project I have been using Linq's Sum() a lot. It's powered by NHibernate on MySQL. In my Session Factory I have explicitly asked NHibernate to deal with exactly 8 decimal places when it comes to decimals:

public class DecimalsConvention : IPropertyConvention
{
    public void Apply(IPropertyInstance instance)
    {
        if (instance.Type.GetUnderlyingSystemType() == typeof(decimal))
        {
            instance.Scale(8);
            instance.Precision(20);
        }
    }
}

However, I found out that .Sum() rounds up the numbers in 5 decimal places:

var opasSum = opasForThisIp.Sum(x => x.Amount); // Amount is a decimal

In the above statement opaSum equals to 2.46914 while it should be 2.46913578 (calculated directly on MySQL). opasForThisIp is of type IQueryable<OutgoingPaymentAssembly>.

I need all the Linq calculations to handle 8 decimal places when it comes to decimals.

Any ideas of how to fix this?

Edit 1: I have found var opasSum = Enumerable.Sum(opasForThisIp, opa => opa.Amount); to produce the correct result, however the question remains, why .Sum() rounds up the result and how can we fix it?

Edit 2: The produced SQL seems to be problematic:

select cast(sum(outgoingpa0_.Amount) as DECIMAL(19,5)) as col_0_0_ 
from `OutgoingPaymentAssembly` outgoingpa0_ 
where outgoingpa0_.IncomingPayment_id=?p0 
and (outgoingpa0_.OutgoingPaymentTransaction_id is not null);
?p0 = 24 [Type: UInt64 (0)]

Edit 3: var opasSum = opasForThisIp.ToList().Sum(x => x.Amount); also produces the correct result.

Edit 4: Converting the IQueryable<OutgoingPaymentAssembly> to an IList<OutgoingPaymentAssembly> made the original query: var opasSum = opasForThisIp.Sum(x => x.Amount); to work.

Answer 1

x.Amount is being converted to a low precision minimum type from "LINQ-to-SQL" conversion, because your collection is IQueryable.

There are several workarounds, the easiest of which is to change the type of your collection to IList, or call ToList() on your collection, forcing the linq query to run as LINQ-to-Objects.

var opasSum = opasForThisIp.ToList().Sum(x => x.Amount);

Note: If you don't want to lose deferred execution by moving away from the IQueryable, you could try casting the Amount to a decimal inside of the linq query.

From MSDN decimal and numeric (Transact-SQL):

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

Edit (to include great explanation of different .NET collection types:

Taken from the answer to this SO question.

IQueryable is intended to allow a query provider (for example, an ORM like LINQ to SQL or the Entity Framework) to use the expressions contained in a query to translate the request into another format. In other words, LINQ-to-SQL looks at the properties on the entities that you're using along with the comparisons you're making and actually creates a SQL statement to express (hopefully) an equivalent request.

IEnumerable is more generic than IQueryable (though all instances of IQueryable implement IEnumerable) and only defines a sequence. However, there are extension methods available within the Enumerable class that define some query-type operators on that interface and use ordinary code to evaluate these conditions.

List is just an output format, and while it implements IEnumerable, is not directly related to querying.

In other words, when you're using IQueryable, you're defining and expression that gets translated into something else. Even though you're writing code, that code never gets executed, it only gets inspected and turned into something else, like an actual SQL query. Because of this, only certain things are valid within these expressions. For instance, you cannot call an ordinary function that you define from within these expressions, since LINQ-to-SQL doesn't know how to turn your call into a SQL statement. Most of these restrictions are only evaluated at runtime, unfortunately.

When you use IEnumerable for querying, you're using LINQ-to-Objects, which means you are writing the actual code that is used for evaluating your query or transforming the results, so there are, in general, no restrictions on what you can do. You can call other functions from within these expressions freely.