c# - Linq Sum() 精度

Question

在我的项目中，我一直在使用 Linq's Sum()很多。它由 MySQL 上的 NHibernate 提供支持。在我的 Session Factory当涉及到 decimals 时，我已经明确要求 NHibernate 处理精确的小数点后 8 位。 :

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

但是，我发现 .Sum()将数字四舍五入到小数点后 5 位:

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

在上面的语句中opaSum等于 2.46914 而它应该是 2.46913578 (直接在 MySQL 上计算)。 opasForThisIp类型为 IQueryable<OutgoingPaymentAssembly> .

当涉及到 decimals 时，我需要所有 Linq 计算来处理小数点后 8 位.

关于如何解决这个问题有什么想法吗？

编辑 1:我找到了 var opasSum = Enumerable.Sum(opasForThisIp, opa => opa.Amount);产生正确的结果，但问题仍然存在，为什么 .Sum()对结果进行四舍五入，我们该如何解决？

编辑 2:生成的 SQL 似乎有问题:

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)]

编辑 3:var opasSum = opasForThisIp.ToList().Sum(x => x.Amount);也会产生正确的结果。

编辑 4:转换 IQueryable<OutgoingPaymentAssembly>到IList<OutgoingPaymentAssembly>进行了原始查询:var opasSum = opasForThisIp.Sum(x => x.Amount);去工作。

Answer 1

x.Amount 正在从“LINQ-to-SQL”转换转换为低精度最小类型，因为您的集合是 IQueryable。

有几种解决方法，其中最简单的方法是将集合的类型更改为 IList，或者对集合调用 ToList()，强制 linq 查询作为 LINQ-to-Objects 运行。

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

注意:如果您不想通过离开 IQueryable 而失去延迟执行，您可以尝试在 linq 查询中将 Amount 强制转换为小数。

来自 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.

编辑(包括对不同 .NET 集合类型的精彩解释:

摘自对 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.