Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Unwanted Decimal Truncation

My Model:

public class Product {     ...         public decimal Fineness { get; set; }     ... } 

Seeding the Database:

new List<Product>         {             new Product { ..., Fineness = 0.757M, ... },             new Product { ..., Fineness = 0.674M, ... },             new Product { ..., Fineness = 0.475M, ... }         }.ForEach(p => context.Products.Add(p)); 

Querying the Database to test seeding:

var products = db.Products.ToList(); foreach (var p in products) {     S.D.Debug.WriteLine("ProductList: {0}, {1}", p.Name, p.Fineness); } 

Console Output:

ProductList: Test Product, 0.75  ProductList: Test Product, 0.67  ProductList: Test Product, 0.47     

Am I doing something really silly or something??? Everything is being truncated to 2 decimal places.

Solution - Thanks to Patrick:

protected override void OnModelCreating(DbModelBuilder modelBuilder) {     modelBuilder.Entity<Product>().Property(x => x.Fineness).HasPrecision(10, 5); } 
like image 838
Gravy Avatar asked Jun 04 '13 23:06

Gravy


2 Answers

So you have your standard entity models defined, here is product with id and decimal, along with anything else you require etc.

public class Product {     public int Id { get; set; }     public decimal Fineness { get; set; } } 

So I've defined an initlizer, in which case the database will drop and re-create any seeded information I've provided, each time I run and execute my application, this will be called.

public class Initializer : DropCreateDatabaseAlways<Context> {     protected override void Seed(Context context)     {          // note how I am specifying it here as 4 digits after the decimal point         // and for the second one, 3 digits         // this is where EF precision must be configured so you can expect         // the values you tell EF to save to the db         context.Products.Add(new Product() {Id = 1, Fineness = 145.2442m});         context.Products.Add(new Product() {Id = 2, Fineness = 12.341m});     } }  public class Context : DbContext {     public IDbSet<Product> Products { get; set; }      public Context()     {         // I always explicitly define how my EF should run, but this is not needed for the answer I am providing you         Configuration.AutoDetectChangesEnabled = true;         Configuration.ProxyCreationEnabled = true;         Configuration.LazyLoadingEnabled = true;         Configuration.ValidateOnSaveEnabled = true;     }      protected override void OnModelCreating(DbModelBuilder modelBuilder)     {         // so here, I am override the model configuration which is what          // EF can use in order to set-up the behaviour of how everything          // is configured in the database, from associations between         // multiple entities and property validation, Null-able, Precision, required fields etc         modelBuilder.Configurations.Add(new ProductConfiguration());     } }  public class ProductConfiguration : EntityTypeConfiguration<Product> {     public ProductConfiguration()     {         ToTable("Product");         HasKey(x => x.Id).Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);          // HAS PRECISION.          // Enforces how the value is to be stored in the database         // Here you can see I set a scale of 3, that's 3 digits after         // the decimal. Notice how in my seed method, I gave a product 4 digits!         // That means it will NOT save the product with the other trailing digits.         Property(x => x.Fineness).HasPrecision(precision: 10, scale: 3);     } } 

With SQL Server Object Explorer, I can view my localdb Example product I made to see how EF configured my Database.

enter image description here

[TestFixture] public class Tests {     [Test]     public void Test()     {         Database.SetInitializer(new Initializer());          using (var ctx = new Context())         {             // assert our findings that it is indeed not what we actually specified in the seed method, because of our Entity configuration with HasPrecision.             Product product1 = ctx.Products.Find(1);             Assert.AreEqual(145.244m, product1.Fineness);              Product product2 = ctx.Products.Find(2);             Assert.AreEqual(12.341m, product2.Fineness);         }              } } 

Unit Test has run, seeded the db and we asserted our assumption

So we need to ensure the database knows how it should store our decimal value, by configuring our entity using the model builder configuration of the Entity Framework, by using the FluentApi, we can setup property traits through the EntityTypeConfiguration<T>.

like image 52
Patrick Magee Avatar answered Sep 24 '22 09:09

Patrick Magee


You do not need a EntityTypeConfiguration, you could simply do it like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder) {     modelBuilder.Entity<Product>().Property(x => x.Fineness).HasPrecision(10, 3);      base.OnModelCreating(modelBuilder); } 

If you want to change precision and scale for every decimal you can do it like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder) {     modelBuilder.Conventions.Remove<DecimalPropertyConvention>();     modelBuilder.Conventions.Add(new DecimalPropertyConvention(10, 3)); } 

If you want a Decimal (10,3) to round Fineness = 0.7577m into 0.758 instead of saving 0.757 to the database have a look at the answer below. It also explains why Entity Framework 6.X truncates decimal values instead of rounds by default.

https://stackoverflow.com/a/57095584/3850405

like image 31
Ogglas Avatar answered Sep 24 '22 09:09

Ogglas