Third decimal changed to 0 on save

Question

I'm trying to store a value with three decimals in an MsSQL database, but for some reason the third decimal is always changed to a 0. I can't figure out why.

The project was created by somebody else with Entity Framework code first v4.0.30319 and it created a column in the database [Amount] [decimal](18, 2) NULL. I have manually changed the database to [Amount] [decimal](18, 3) NULL

In the code:

var _yourbid = form["YourBid"]; // value = 10.123
decimal yourbid;

if (decimal.TryParse(_yourbid, out yourbid))
{
    Bid b = new Bid();
    b.Amount = yourbid;

    //yourbid  = 10.123
    //b.Amount = 10.123

    Db.Bids.Add(b);
    Db.Save();

    //in database is 10.120
}

Now I expected that the code first somewhere declared the decimal to have a scale of 2, but I couldn't find anything. I checked the options listed in Decimal precision and scale in EF Code First but it's not used.

There isn't a trigger on the database that might be changing it either. I can put in the right value directly from SQL

I must be missing something obvious, but I hope you can point me in the right direction.

Answer 1

This is likely because your Entity Framework model and the underlying database no longer match. For an Entity Framework Code First project, you should update your model first and then use the migration feature to propagate the change to the database. Before this you should change the Amount field on the DB table to have a precision of 2 again so the difference can be detected.

To update your model, see this SO answer on how to customise the precision of a decimal field:

Decimal precision and scale in EF Code First

(Things are easier in later versions of EF, so you may want to consider upgrading at some point in the future.)

You should then add a migration, which will record the SQL actions to apply to the database. Use Add-Migration in the Package Manager Console to do this scaffolding step. Finally, you should also Update-Database to execute the change on the target database.

There's more background information on the web, including this tutorial from Microsoft: https://msdn.microsoft.com/en-gb/data/jj591621.aspx

Edit:

Here's some migration code which will perform the precision change. It may be required to create this manually if you're running an older version of EF and can't use HasPrecision:

public partial class PrecisionChange : DbMigration
{
    public override void Up()
    {
        AlterColumn("dbo.SomeTable", "Amount", (c => c.Decimal(false, 18, 3)));
    }

    public override void Down()
    {
        AlterColumn("dbo.SomeTable", "Amount", (c => c.Decimal(false, 18, 2)));
    }
}