OData / OData/AspNetCoreOData

Decimal is rounded to two digits

Open
#461 2 comments 0 reactions 1 assignee View on GitHub

@corranrogue9 is already working on this.

Since Feb 1, 2022.

investigating
Dominant language
C#
Stars
505
Forks
186
PR merge metrics
No merged PRs in 30d

Description

Hi,

There's an issue with decimal being rounded to two digits during aggregation ignoring database mapping settings.

Sample project: https://github.com/pfedotovsky/OData.Test
Sample request: http://localhost:5000/api/accounts?$apply=groupby((AccountId), aggregate(TotalAmount with sum as TotalAmount))

Database mapping (note the .HasPrecision(18, 8))

    internal sealed class AmountMap : IEntityTypeConfiguration<Amount>
    {
        public void Configure(EntityTypeBuilder<Amount> builder)
        {
            builder
                .ToTable("TransactionAmounts", "dbo");

            builder
                .HasKey(e => e.AmountId);

            builder
                .Property(e => e.AmountId)
                .HasColumnName("AmountId")
                .ValueGeneratedOnAdd();

            builder
                .Property(e => e.OrderId)
                .HasColumnName("OrderId")
                .IsRequired();

            builder
                .Property(e => e.AccountId)
                .IsRequired();

            builder
                .Property(e => e.TotalAmount)
                .HasColumnName("Amount")
                .HasPrecision(18, 8)
                .IsRequired();

            builder
                .Property(e => e.TaxAmount)
                .HasPrecision(18, 8)
                .IsRequired();
        }
    }

The resulting SQL query is

SELECT [t0].[Name], [t0].[AccountType] AS [Value], N'TotalAmount' AS [Name],
COALESCE(SUM(CAST([t0].[Amount] AS **decimal(18,2)**)), 0.0) AS [Value]
FROM (SELECT [t].[AccountType], [t].[Amount], N'AccountType' AS [Name] FROM [dbo].[TransactionAmounts] AS [t] ) AS [t0]
GROUP BY [t0].[Name], [t0].[AccountType]

So, essentially, the precision got lost.
The workaround is to set default precision globally via

using Microsoft.EntityFrameworkCore.Storage;

namespace ODataRoutingSample.Converters
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Usage", "EF1001:Internal EF Core API usage.", Justification = "<replace decimal(18,2)>")]
    internal class SqlDecimalServerTypeMappingSource : Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerTypeMappingSource
    {
        private static readonly RelationalTypeMappingInfo defaultDecimalMappingInfo = new RelationalTypeMappingInfo("decimal(18, 8)", "decimal", null, null, 18, 8);

        public SqlDecimalServerTypeMappingSource(TypeMappingSourceDependencies dependencies, RelationalTypeMappingSourceDependencies relationalDependencies)
                : base(dependencies, relationalDependencies)
        {
        }

        protected override RelationalTypeMapping FindMapping(in RelationalTypeMappingInfo mappingInfo)
        {
            if (mappingInfo.ClrType == typeof(decimal) && !mappingInfo.Scale.HasValue)
            {
                return base.FindMapping(defaultDecimalMappingInfo);
            }

            return base.FindMapping(mappingInfo);
        }
    }
}

But, it'd be great to avoid such a workaround.

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.