Entity Framework Decimal Truncation Issue

Question

The project I'm working on uses Entity Framework 6.0 Code-First.

One of my co-workers, due to his lack of experience with EF, manually changed the field type of a field on the database to being decimal(28,10), instead of doing it the correct way in the OnModelCreating method of DbContext. Entity Framework allowed him to do so, never throwing an error upon any future migrations.

Last week, another co-worker was running into a problem with a process that clones records from that table, where the decimal values in the new records were being truncated to 2 decimal places (with no rounding occurring).

The code for that cloning resembles the following (using the Repository pattern on top of EF):

public void CloneAccounts(List<Account> accounts, int newQuarterID)
{
    var newAccounts = new List<Account>();
    accounts.ForEach(account =>
    {
        var clonedAccount = new Account
        {
            QuarterID = newQuarterID
            AccountName = account.AccountName,
            AccountNumber = account.AccountNumber,
            Amount = account.Amount
        };

        newAccounts.Add(clonedAccount);
    });

    AccountRepository.AddMany(newAccounts);
    AccountRepository.Save();
}

When I pointed out, as a side-point, that the declaration of the Amount field being decimal(28,10) should really be in OnModelCreating, he went ahead and did that, and added a migration. Doing that, interestingly enough, ended up solving the issue with the code above.

My question is two-fold:

1. Why did that issue not affect the creation of the original records as well, and only upon cloning?
2. Why did adding that line in OnModelCreating fix it?

Thanks!

Answer 1

When using codeFirst with EF it creates the model in code (C# or VB), then it replicates the model to your DB.

To answer your questions I could say that:

1. The issue did't affect at first because when you created your model all the changes were made directly to your DB, then you had your tables exactly the same as in the model.

Remember that Entity Framework is an ORM (Object Relational Mapper) it creates, for you and with little effort, a set of entities (classes) based on a domain model -- this domain model can exists it three different flavours: code-first, model-first, and database-first.

• Code-first means that you start your project by creating a set of classes (aKa Entities) which will represent your relational model in your data base. (your source will be your classes and the target your database).

• Model-first means that you start your project by using a visual tool, basically drag and drop, connecting points and so on, which lets you create a model which will represent your relational model in your data base. (your source will be your model and the target your database).

• Database-first means that you start your project by selecting the model from a data source (usually a database), this approach will create for you a set of classes (Entities) in your visual studio project. (your source will be your database and the target your code).

So, whatever change you make in any of the above scenarios must be replicated from source to target via Entity Framework.

What happened here was a mistake from your co-worker, who made a change directly in your database, but it must had been done from your EF project (code first).

When you invoked ClonneAccounts EF made all the magic (connect to DB, execute a query, gets data, cast it to your entity classes, and then retrieve them or visceversa) --> this is when your app crashed because of an InvalidCastException.

2. EF does not check consistency, scheme or structure of your tables every time you invoke ClonneAccounts, it just retrieves data between Entities and Database and insert/update data. If you want to update/check consistency and replicate changes you have to do it manually. That is why until you fixed your model in the "code side" and ran the tool it replicated all changes to your db.

Hope it helps

Answer 2

If you didn't originally have any precision set, the default convention for code-first is to create decimal columns with precision of 18 and scale of 2 (so only two decimal places). I think it's possible the records had originally been truncated in the first place.

Also, by default, the SQL Server provider's SQL generator sets SqlParameter.Scale property to the scale defined in the model, unless you had TruncateDecimalsToScale set to false, which would affect database updates and inserts. I'm not sure how those records with additional decimal places ended up in the database, though.