dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.83k stars 3.2k forks source link

Low Precision on double column #31064

Open Zetanova opened 1 year ago

Zetanova commented 1 year ago

The mapping of a double property in code-first with low precision works as expected.

[Precision(3)]
public double Price { get; set; }

Produces the expected column price real(24)

The value 1.03 is handled properly by the sql-server and sql management studio and shown always without floating point precision.

The issue is that the loaded data are not properly floated point converted. 1.03 is loaded as 1.0299999713897705

and setting the property again to 1.03 will let the ChangeTracker flag the entity as modified.

The expected result is that EF core is handling the real the same way as "sql management studio", loads it directly as double with 1.03 and do not trigger an "modified" event when it does not realy change.

Do I miss some converter or something else?

I don't really have the problem with the floating point, only that it triggers a costly modified update.

EF Core version: 7.0.5 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: ubuntu focal

Zetanova commented 1 year ago

I added now a ValueConverter to the column,

protected override void OnModelCreating(ModelBuilder modelBuilder) 
{
          modelBuilder.Entity<MyEntity>()
                .Property(o => o.Price)
                .HasConversion(v => v, v => Math.Round((double)v, 3));
}

It feels only that the conversion from real => double with precision should be supported.

ajcvickers commented 1 year ago

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

Zetanova commented 1 year ago

EF core is handling the [Precision(3)] double property as a real(24), most likely using an c# float to load the value after and simple cast it double. This produces the well know floating point error.

var value = 1.03d;
var f1 = (float)value;
var d1 = (double)f1;

Assert.NotEqual(1.03d, d1);

var d2 = Math.Round(d1, 2, MidpointRounding.AwayFromZero);

Assert.Equal(value, d2);

If the precision is known to EF, it could support a better conversion.

ajcvickers commented 1 year ago

@Zetanova EF Core Migrations creates a database column of type float(3), which then is not able to store the full precision. I don't see anything happening on the EF side where a float is used instead of a double. If you are seeing this, then please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

Zetanova commented 1 year ago

@ajcvickers Here is the demo project

Zetanova commented 1 year ago

@ajcvickers I found some cast issue/bug in a projection and added one query into test project.

There is an issue to query a [Precision(2)] double? Value field in a projection. It is working fine without a default value, but if a default value is set, then it throws an Single to double cast exception

//is throwing
var items = await context.Tests.AsNoTracking()
                    .Select(n => new TestDto
                    {
                        Value = n.Value2 ?? 0
                    })
                    .ToListAsync(cts.Token);

//is working
var items = await context.Tests.AsNoTracking()
                    .Select(n => new TestDto
                    {
                        Value = n.Value2
                    })
                    .ToListAsync(cts.Token);
ajcvickers commented 1 year ago

Notes from triage: We have code that gets value from SqlClient as a float. This was added because otherwise SqlClient failed if the column was really a float. We should investigate if SqlClient can be made to return a double in this situation without causing a parameter mismatch.