CODEWITHSUNDEEP
c#azure-sql-databasevisual-studio-2019.net-5database-project
Jon
Jon

Reputation: 69

Very odd C# sql db decimal issue

So i'm seeing some very odd behaviour that i can't work out what's going wrong and hoping someone can help. Originally i was seeing this in a bigger application with lots of values, but i've stripped it down to a simple example.

Test code:

[Test]
public void DecimalIssue()
{
    decimal decimalValueToSet = 0.00003110M;
    var modelToAdd = new TestModel { Value = decimalValueToSet };
    TestModel addedEntity = _db.Add(modelToAdd);
    int idToUse = addedEntity.Id;
    decimal? addedValue1 = addedEntity.Value;
    //int idToUse = 1;

    TestModel entity = _db.GetById(idToUse);
    decimal? value1 = entity.Value; // Sometimes value1 is 0.00000000 and sometimes it's 0.00003110
    decimal value2 = entity.Value.Value;
    decimal? value3 = Convert.ToDecimal(entity.Value);
    double value4 = Convert.ToDouble(entity.Value);
    decimal? value5 = decimalValueToSet;

    entity.Value = decimalValueToSet;
    TestModel updatedEntity = _db.Update(entity);
    decimal? updatedValue = updatedEntity.Value;

    TestModel newEntity = _db.GetById(idToUse);
    decimal? newValue = newEntity.Value;
}

Steps to reproduce issue:

  1. Start by adding the table through the SQL Database Project and VS schema comparison
  2. Run the test to add the value to the db. Everything looks correct when debugging the test
  3. Check the db table in SSMS. The value shows as 0.00000000
  4. Run the same test, but comment out the add section and uncomment idToUse = 1
  1. Directly update the DB value in SSMS to 0.00003110. Run the test again and now everything is correct in the test and the DB value in SSMS is correct afterwards
  2. Adding a new value acts the same as the first did
  3. Delete the table and create it again and everything is back to square 1

Environment details:

Not sure if this will help, but most values i add show as 0.00000000 in SSMS, but some values show differently. If for example i instead use 0.50516090M, then it shows in SSMS as 0.50000000.

Model:

[Table("Test")]
public class TestModel
{
    [Key]
    public int Id { get; set; }
    public decimal? Value { get; set; }
}

DB schema:

CREATE TABLE [dbo].[Test] (
    [Id]    INT              IDENTITY (1, 1) NOT NULL,
    [Value] DECIMAL (12, 8)  NULL,
    CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Upvotes: 1

Views: 406

Answers (1)

Jon
Jon

Reputation: 69

Thanks @JeroenMostert for kinda pointing me in the right direction. I did some more digging and found this page that has the answer i was looking for, which is to do the following:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<TestModel>().Property(x => x.Value).HasPrecision(12, 8);
}

I'm very surprised I've never come across this issue before!

Upvotes: 2

Related Questions