Entity Framework Code First truncating my decimals

decimal ef-code-first entity-framework-6 geospatial rounding-error

Question

I am using Entity Framework 6.x using the Code First approach on an MVC 5 application. In this particular situation my model (among other things) contains two properties named Latitude and Longitude:

[Required, Range(-90, +90)]
public decimal Latitude { get; set; }

[Required, Range(-180, +180)]
public decimal Longitude { get; set; }

And when I performed the migration I got something like this

CreateTable("ResProperty"), c => new {
        :
    Latitude = c.Decimal(nullable: false, precision: 10, scale: 8),
    Longitude = c.Decimal(nullable: false, precision: 11, scale: 8),
        :
})
... other stuff

so both latitude and longitude have 8 decimal digits. The former with 2 whole numbers (max 90) and the latter with 3 whole numbers (max 180).

After performing the Update-Database command my table's columns are shown as:

Latitude decimal(10,8)
Longitude decimal(11,8)

that seems good to me. Now In my view I have a map and Javascript code that allows the user to reposition the marker. That works fine too. When the marker is repositioned the Latitude and Longitude fields are populated with the updated value which (Javascript) has more than 12 decimal digits. That does not matter AFAIK because my scale is 8 decimals.

After the submit button is pressed and either the Create or Edit POST method is invoked I examine the model instance and I confirmed that the actual values passed in the model to the controller are correct, they have more than enough decimal digits (those that Javascript code place). So the value is correct.

Now... the problem being that after db.SaveChanges() is performed the database gets updated -and I have confirmed that an actual write/update has taken place- but somehow internally the EF disregards my actual values and writes truncated latitude/longitude rounded to ONLY TWO decimal digits, so my Latitude shows in the DB as 09.500000000 all other decimal digits are zeroed because a rounding seems to have taken place.

// Prior to SaveChanges()
Latitude = 9.08521879
Longitude = -79.51658792
// After SaveChanges()
Latitude = 9.08000000
Longitude = -79.51000000

Why is it rounding it if I have given the correct scale and precision and the column has the correct scale and precision as well? why is SaveChanges altering my values?

I found this post (http://weiding331.blogspot.com/2014/01/entity-framework-decimal-value.html) which is the same issue but I don't know how I can fix that (if it does) because I have already performed several migrations and data additions after the table in question was "migrated".

Summarizing

  • The model data type is correct (decimal)
  • The database migration code has the correct precion/scale (lat 10/8 lon 11/8)
  • The SQL database columns have the correct precision/scale (lat 10/8, long 11/8)
  • The values passed in the model have at least 8 decimal digits for both latitude and longitude
  • the actual writing/updating of the value takes place in the database without error, but...
  • The values recorded on the database for these two columns are truncated to TWO decimal digits and show the other least significant decimal digits as zero (0)
1
13
10/15/2014 10:07:12 PM

Popular Answer

EF has a special property for SqlProviderServices (implementation for the SqlClient provider for SQL Server) - TruncateDecimalsToScale. The default value is true so maybe you can change it to false value. For example:

public class DbContextConfiguration : DbConfiguration
    {
        public DbContextConfiguration()
        {
            var now = SqlProviderServices.Instance;
            SqlProviderServices.TruncateDecimalsToScale = false;
            this.SetProviderServices(SqlProviderServices.ProviderInvariantName, SqlProviderServices.Instance);
        }
    }

    [DbConfigurationType(typeof(DbContextConfiguration))]
    public class MyContext : DbContext
    { ... }

More info about that: https://msdn.microsoft.com/en-us/library/system.data.entity.sqlserver.sqlproviderservices.truncatedecimalstoscale%28v=vs.113%29.aspx

18
8/23/2016 9:45:27 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow