Entity Framework Element Code Decimal Truncation First

Question

I am using Entity Framework 6.x, using the Code First approach in 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 migrated, 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 

therefore, both latitude and longitude have 8 decimal digits. The first has 2 integers (maximum 90), and the second has 3 integers (max. 180).

After running the Update-Database command, my table columns are displayed as:

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

which seems good to me. Now, in my opinion, I have a map code and Javascript that allows the user to move the marker. It works well. When the marker moves, the Latitude and Longitude fields are filled with an updated value, which (Javascript) has more than 12 decimal digits. It doesn’t matter for AFAIK, because my scale is 8 decimal places.

After clicking the submit button and calling the Create or Edit POST method, 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 decimal digits (this is the place of the Javascript code). Therefore, the value is correct.

Now ... the problem is that after db.SaveChanges () is executed, the database is updated - and I confirmed that the actual write / update occurred, but somehow inside EF ignores my actual values ​​and writes truncated latitude / longitude rounded up to ONLY TWO decimal digits, so my Latitude shows in the database as 09.500000000 all other decimal digits are reset, because rounding seems to have taken place.

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

Why does he round it if I give the correct scale and accuracy, and the column has the correct scale and accuracy? Why does SaveChanges change my values?

I found this post ( http://weiding331.blogspot.com/2014/01/entity-framework-decimal-value.html ) which is the same problem, but I do not know how I can fix it (if it is) , because I already performed several migrations and adding data after the table was "migrated".

Summarizing

• Model data type correct (decimal)
• Database migration code has correct accuracy / scale (lat 10/8 lon 11/8)
• SQL database columns have the correct precision / scale (lat 10/8, long 11/8)
• Values ​​transferred to the model have at least 8 decimal digits for latitude and longitude.
• the actual recording / updating of the value occurs in the database without errors, but ...
• The values ​​recorded in the database for these two columns are truncated to TWO decimal digits and show the other least significant decimal digits as zero (0)

Answer 1

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. 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 about this: https://msdn.microsoft.com/en-us/library/system.data.entity.sqlserver.sqlproviderservices.truncatedecimalstoscale%28v=vs.113%29.aspx

Answer 2

For storing spatial data, I would recommend the DbGeography class, designed for data of this type.

https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.spatial.dbgeography?view=entity-framework-6.2.0

---

As already mentioned, the truncation problem can be solved with SqlProviderServices.TruncateDecimalsToScale = false; as @AdrianTarnowski pointed out. However, I would like to show why this happens and why Entity Framework 6.X truncates decimal values ​​instead of the default rounding.

For testing, I use a basic program like this:

 class Program { static void Main(string[] args) { var dbContext = new ApplicationDbContext(); dbContext.TestValues.Add(new TestValue() { Value = 0.0005m }); dbContext.TestValues.Add(new TestValue() { Value = 0.0001m }); dbContext.TestValues.Add(new TestValue() { Value = 0.0007m }); dbContext.SaveChanges(); } } public class TestValue { public int Id { get; set; } public decimal Value { get; set; } } public class DbContextConfiguration : DbConfiguration { public DbContextConfiguration() { var providerInstance = SqlProviderServices.Instance; SqlProviderServices.TruncateDecimalsToScale = true; this.SetProviderServices(SqlProviderServices.ProviderInvariantName, SqlProviderServices.Instance); } } [DbConfigurationType(typeof(DbContextConfiguration))] public class ApplicationDbContext : DbContext { public ApplicationDbContext() : base("ApplicationContext") { Database.Log = s => Debug.WriteLine(s); } public DbSet<TestValue> TestValues { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<TestValue>().Property(x => x.Value).HasPrecision(18, 3); base.OnModelCreating(modelBuilder); } } 

By default, it looks like this: SqlProviderServices.TruncateDecimalsToScale = true; . This is to prevent hacking into existing applications that depend on this behavior.

https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.sqlserver.sqlproviderservices.truncatedecimalstoscale?redirectedfrom=MSDN&view=entity-framework-6.2.0#overloads

When TruncateDecimalsToScale is normal ( TruncateDecimalsToScale = true; ), the insertion from the entity structure looks like this in Database.Log from DbContext :

 INSERT [dbo].[TestValues]([Value]) VALUES (@0) SELECT [Id] FROM [dbo].[TestValues] WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity() -- @0: '0,0005' (Type = Decimal, Precision = 18, Scale = 3) 

However, looking at SQL Server Profiler , the actual data sent is 0 0 for each value above.

 exec sp_executesql N'INSERT [dbo].[TestValues]([Value]) VALUES (@0) SELECT [Id] FROM [dbo].[TestValues] WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()',N'@0 decimal(18,3)',@0=0 

When switching to SqlProviderServices.TruncateDecimalsToScale = false; Database.Log from DbContext as follows:

 INSERT [dbo].[TestValues]([Value]) VALUES (@0) SELECT [Id] FROM [dbo].[TestValues] WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity() -- @0: '0,0005' (Type = Decimal) 

Now SQL Server Profiler looks better and has the correct values:

 exec sp_executesql N'INSERT [dbo].[TestValues]([Value]) VALUES (@0) SELECT [Id] FROM [dbo].[TestValues] WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()',N'@0 decimal(4,4)',@0=5 

Please note that this is not affected by EntityFrameworkCore. Here is the default rounding.

ROS:

 class Program { static void Main(string[] args) { using (var dbContext = new ApplicationDbContext()) { dbContext.TestValues.Add(new TestValue() { Value = 0.0005m }); dbContext.TestValues.Add(new TestValue() { Value = 0.0001m }); dbContext.TestValues.Add(new TestValue() { Value = 0.0007m }); dbContext.SaveChanges(); } } } public class TestValue { public int Id { get; set; } public decimal Value { get; set; } } public class ApplicationDbContext : DbContext { public DbSet<TestValue> TestValues { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder .UseSqlServer("data source=localhost;initial catalog=;persist security info=True;User Id=;Password=;", providerOptions => providerOptions.CommandTimeout(60)); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<TestValue>().Property(x => x.Value).HasColumnType("decimal(18, 3)"); base.OnModelCreating(modelBuilder); } }