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
You can either use the ROUND Function to limit decimal places in Excel, or you can use cell formatting to limit the number of decimal places displayed. The ROUND Function amends the original value; using cell formatting retains the exact original value.
What is truncation? In simplest terms, truncation means to chop off the decimal portion of a number. This means: Truncating 3.3 returns 3.
To truncate a number to 1 decimal place, miss off all the digits after the first decimal place. To truncate a number to 2 decimal places, miss off all the digits after the second decimal place.
Truncation is a method of approximating numbers. It is easier than rounding, but does not always give the best approximation to the original number. Truncation is used in computing when division is done with integers and the answer must be an integer. Sometimes a number is approximated by just ignoring digits.
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
Storing spatial data I would recommend DbGeography
class that is made for that type of data.
https://docs.microsoft.com/en-us/dotnet/api/system.data.entity.spatial.dbgeography?view=entity-framework-6.2.0
As already mentioned the truncate issue can be solved with SqlProviderServices.TruncateDecimalsToScale = false;
as @AdrianTarnowski pointed out. I would however like to show why this is happening and why Entity Framework 6.X truncates decimal values instead of rounding by default.
To test I'm using 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);
}
}
Default it looks like this: SqlProviderServices.TruncateDecimalsToScale = true;
. This is to prevent breaking 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;
) an insert from entity framework looks like this in the 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 that is sent is 0 for every value from 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
Changing to SqlProviderServices.TruncateDecimalsToScale = false;
the Database.Log
from DbContext
looks like this instead:
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 have 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
Note that EntityFrameworkCore is not affected by this. Here rounding is default.
POC:
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);
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With