Precision scaling using Entity Framework and SQL Server

Question

There’s some time I’ve been noticing, something strange when saving values double in the SQL Server database with the Entity Framework.

I noticed that when reporting a value ex: 12.23 after recording the information is breaking the decimals in more houses ex: 12.22501.

This does not occur frequently and not every time a value is recorded, and also has no impact on calculations (as I do not use round).

My doubts are:

• Why does this happen?
• Has anyone been there? How did you solve?
• It is configuration or depends on the type of database?

The structure I use is informed below:

public class MyClass
{
    public double MyClass { get; set; };
}

public class MyContext : DbContext
{
    public DbSet<MyClass> MyClass;
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyClass>().Property(x => x.ValorTeste).HasPrecision(16, 5);
    }
}

Answer 1

Why does this happen?

Because the value recorded in double is used for scientific calculations where it needs performance and the processor has specialized instructions that make the calculations very quickly. Because it’s all done binary and not decimal, as we’re used to, it achieves speed and great accuracy, but it doesn’t get accuracy in many situations. For science, computer graphics and the like, this is usually no problem. For financial use is a huge problem.

Has anyone been through it? how did it resolve? It is configuration or depends on the type of database?

Every programmer who used double where it could not already gone through it and had losses. The solution is to use the right type for the need, or decimal (existing on . NET and SQL Server) or money (only existing in SQL Server needing a mapping done by the Entity Framework).

With the right type nothing needs to be configured.

Note that what will be displayed still depends on specific formatting, but presentation is different from the value itself. Calculations may still need rounding and adaptations, but they will all work correctly with pennies or other decimal parts of a number.

The performance may not be great, but it’s still great for this type of application. Nor compared, for example, to a text that is absurdly slower in most situations.

No solution while using double. Any other attempt to do the double Behaving exactly will fail. The worst thing is that in some cases it might work and the programmer thinks it worked. Many programmer strongly believes it is possible to use double with accuracy, although it is well proven that.

More information on another question here on the site. And see What is the correct way to use the float, double and decimal types?.