Unwanted Decimal Truncation

Question

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);
}

Answer 1

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.

[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);
        }         
    }
}

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>.

Answer 2

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

Answer 3

Here is good tutorial for formatting decimal numbers

D.Debug.WriteLine("ProductList: {0}, {1:0.000}", p.Name, p.Fineness);

// just two decimal places
String.Format("{0:0.00}", 123.4567);      // "123.46"
String.Format("{0:0.00}", 123.4);         // "123.40"
String.Format("{0:0.00}", 123.0);         // "123.00"

// max. two decimal places
String.Format("{0:0.##}", 123.4567);      // "123.46"
String.Format("{0:0.##}", 123.4);         // "123.4"
String.Format("{0:0.##}", 123.0);         // "123"

// at least two digits before decimal point
String.Format("{0:00.0}", 123.4567);      // "123.5"
String.Format("{0:00.0}", 23.4567);       // "23.5"
String.Format("{0:00.0}", 3.4567);        // "03.5"
String.Format("{0:00.0}", -3.4567);       // "-03.5"

//Zero formatting
String.Format("{0:0.0}", 0.0);            // "0.0"
String.Format("{0:0.#}", 0.0);            // "0"
String.Format("{0:#.0}", 0.0);            // ".0"
String.Format("{0:#.#}", 0.0);            // ""