How can I make a c# decimal match a SQL decimal for EF change tracking?

Question

To avoid touching changeless records in EF it's important that original and current entity values match. I am seeing a problem with decimals where the EF entity has SQL representation and that is being compared to c# decimal.

This is debug output from entities with changes detected. This shows the problem pretty clearly. Even though both the entity and the source data are in of type decimal the values are considered difference even though they are equal.

How can I ensure that original and current values match when using c# decimal?

Maybe there is a way to turn the c# decimal into an entity (SQL) decimal before the update?

Another Example

I would expect the truncation to ignore the fact that the incoming precision is higher than the SQL scale

Temporarily I am having to compare before I set entity properties. Its just a pain — jwrightmail, Feb 14 '18 at 02:41
I am using DB first. The SQL fields are defined as decimal(8,2). So, in the screenshot I posted, you can see that EF is comparing 0.00 to 0. A decimal in c# is not stored at 0.00 but in SQL it is. The problem is that EF is using the DB representation instead of the c# struct. — jwrightmail, Feb 15 '18 at 03:04
I think this may be a bug in EF actually. EF's change tracking logic needs to take these facts into account. Although, the last line in the screenshot shows an interesting problem. SQL will truncate 0.087 because of the precision. But, will it round, or will it simply truncate? In that case are they the same or are they different? If they are the same due to truncation then I think EF needs to fix their tracking logic to ignore cases where the decimals are truly equal after truncation. — jwrightmail, Feb 15 '18 at 03:05
You've set the precision in your model correctly? See [here](https://stackoverflow.com/questions/5551790/why-does-my-entity-framework-turn-2-87-into-just-2-decimal-field) — Steve Greene, Feb 15 '18 at 15:22
The precision is correct at (8,2). I want to be able and capture 2 decimal places. The problem is that in EntityFramework if you set the value from 0.00 to 0 EF notices this as a change even though it is not. The problem is that EF is comparing a SQL decimal to a c# decimal. SQL forces the decimal places holders. — jwrightmail, Feb 15 '18 at 19:40
In code first we can do [this](https://www.andrewcbancroft.com/2017/03/29/entity-framework-tip-specifying-decimal-precision/). Not sure if it works for model first. — Steve Greene, Feb 15 '18 at 21:43
No, that would be readily identifiable. So I don't see in your comments - you have specifically checked the precision in the EDMX model as shown in [this link](https://stackoverflow.com/questions/5551790/why-does-my-entity-framework-turn-2-87-into-just-2-decimal-field)? — Steve Greene, Feb 17 '18 at 01:03

score 3 · Accepted Answer · answered Feb 23 '18 at 06:39

You could implement a proxy-property which handles the conversion from code-precision to db-precision:

public class MoneyClass
{
    [Column("Money")]
    public decimal MyDbValue { get; set; } // You existing db-property

    [NotMapped]
    public decimal MyCodeValue // some property to access within you code
    {
        get
        {
            return this.MyDbValue;
        }
        set
        {
            decimal newDbValue = decimal.Round(value, 2);
            if (this.MyDbValue != newDbValue)
            {
                Console.WriteLine("Change! Old: {0}, New: {1}, Input: {2}", this.MyDbValue, newDbValue, value);
                this.MyDbValue = newDbValue;
            }
        }
    }
}

static void Main(params string[] args)
{
    MoneyClass dbObj = new MoneyClass()
    {
        MyCodeValue = 123.456M
    };

    Console.WriteLine(dbObj.MyDbValue);
    dbObj.MyCodeValue = 123.457M; // won't change anything
    Console.WriteLine(dbObj.MyDbValue);
    dbObj.MyCodeValue = 123.454M; // will change because of 3rd decimal value 4
    Console.WriteLine(dbObj.MyDbValue);
    dbObj.MyCodeValue = 123.46M; // will change
    Console.WriteLine(dbObj.MyDbValue);
}

I think this might be a more elegant way of doing what i am already doing by pre-checking before I change values. I suppose this would be considered the best work around. I am unsure how to implement a proxy class though. My Entities are generated from DB first code generation. Do you have any tips on how to implement this using DB first code generated entities? — jwrightmail, Feb 23 '18 at 19:53
I'd suggest you inherit the auto-generated class and implement the proxy-properties in the child-class. Is this possible in your environment? — kara, Mar 05 '18 at 06:57

score 2 · Answer 2 · answered Feb 21 '18 at 16:24

This answer is not supposed to fix exactly the issue you have, but to go around it.

I suggest to code the logic that decides whether an objects needs to be saved or not on a higher application layer (in that respect I consider the EF generated classes as low level objects).

The code which retrieves and stores data could be implemented in a repository class, i.e. a class that manages your data access logic. So what you application uses would be this repository class and not the EF code. Whether the repository class internally uses EF or something else would not be important anymore for you application. If you define an interface for you repository class you could even replace it easily with some or technology to save and retrieve data.

See here for an article from microsoft about the repository pattern. This is an info from a question here at stackoverflow.

I generally would not recommend to use the EF generated classes in normal application code. It might be tempting a first, but also cause problems later as in your case.

This is what I've ended up having to do. With text fields and int's this feature works great as there is no need to check, just simply set the value and move forward. EF doesn't touch records that don't have changes so its a wonderful feature. In this case however all of the sudden all this extra plumbing on my part is required to prevent unnecessary updates. — jwrightmail, Feb 21 '18 at 20:07

How can I make a c# decimal match a SQL decimal for EF change tracking?

2 Answers2