C# Getting wrong decimal value from EF6 Code-First DB with MySQL

Question

We have a code-first database in our project, with a decimal in one of our models. We set the decimal precision to (6, 2), which we checked in our mysql database's structure. The collation of the db is utf8_unicode_ci.

We saved a value of 4.00 to the database from a view, which was correct. However, now that we try to get the value back from the db, in stead of getting 4.00 or 4.00m, we get 400M. The comma is just gone.

All help appreciated.

(But please, don't suggest dividing by 100. That's ugly fixing and you know it.)

EDIT Model:

public class RSU
{
    public int Id { get; set; }
    public virtual RSUSlot SlotRef { get; set; }
    public decimal? Length { get; set; }
}

INSERT code:

using(dbCOntext db = new dbCOntext())
{
    db.Add(new RSU{ Length = 4.00m });
    db.SaveChanges();
}

GET:

public static CabinetConfig GetCabinet(M4CDbContext db, int id)
{
    return db.CabinetConfig
        .Include(m => m.RSURef)
        .Where(m => m.Id == id)
        .FirstOrDefault();
}

Cannot replicate this problem. Can show us your problem through an **sql fiddle**? — 1000111, Aug 29 '16 at 09:29
Also, please provide the code of the `INSERT` and the `SELECT` of the `decimal`. — Smartis has left SO again, Aug 29 '16 at 09:34
Afraid I can't reproduce in a fiddle. If we run `select * from rsu` in our local database we get a list with values `4.00`. It's a MVC project with EF6 — Victoria S., Aug 29 '16 at 09:40
Where did you set the Decimal Precision in Code? And which kind of EF your are using? — Smartis has left SO again, Aug 29 '16 at 09:51
In the DbCOntext we set `modelBuilder.Entity().Property(x => x.Length).HasPrecision(6, 2);`. We're using EF6 (MySql) — Victoria S., Aug 29 '16 at 09:54
Are you sure you provide correct `Id` at filter `.Where(m => m.Id == id)`? — Adil Mammadov, Aug 29 '16 at 10:16
yes, 100% sure. We get the right fields, just the wrong value for the decimal. — Victoria S., Aug 29 '16 at 10:37
What does this syntax mean? `4.00m` ? It is not a valid literal in any language I know. — Rick James, Aug 29 '16 at 16:33
`EF6` = `Entity Framework 6`. and `4.00m` is specific for decimals. Don't know if it's messing things up. We've just tested another model and can retrieve the decimal values just fine. It's a mystery as to why this specific one won't work. — Victoria S., Aug 30 '16 at 06:12

Smartis has left SO again · Answer 1 · 2016-08-29T10:33:33.090

0

~~I'm not sure why this isn't working with MySQL but an idea would be:~~

Use the Fluent API approach and create EntityTypeConfiguration for your class:

public class RSUConfiguration : EntityTypeConfiguration<RSU>
{
    public RSUConfiguration()
    {
        Property(x => x.Length)
            .HasPrecision(6, 2);
    }
}

And add it to your modelBuilder:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<RSU>().Property(x => x.Length).HasPrecision(6, 2);    
    modelBuilder.Configurations.Add(new RSUConfiguration());

    base.OnModelCreating(modelBuilder);
}

edited Aug 29 '16 at 10:33

answered Aug 29 '16 at 10:23

Smartis has left SO again

5,025
4
31
45

We used your method, but nothing changed. We still have `4.00` in the database, and see `400M` returned to our model. – Victoria S. Aug 29 '16 at 11:26
Have you recreated the database after changing to this approach? – Smartis has left SO again Aug 29 '16 at 11:39
Yes. We've executed a migration. – Victoria S. Aug 29 '16 at 11:43
Which kind of `MySqlConnector` do you use? Is the Database encoding set in the `Connection String`? – Smartis has left SO again Aug 29 '16 at 11:51
In our web.config we have ``. We don't set database encoding anywhere – Victoria S. Aug 29 '16 at 11:55
Add `CharSet=utf8;` to the connection string. Could be changing the resultset. – Smartis has left SO again Aug 29 '16 at 11:59
Doesn't change anything either. We have found another model in the meanwhile, where getting the decimal value does work. We're comparing code from there but find no differences so far. – Victoria S. Aug 29 '16 at 13:16
I'm working on a project where I faced the same situation and I'm thinking that this is a bug because it only happens when you use Include, if you query using the context directly it will work. – Ariel Moraes May 29 '18 at 20:14

C# Getting wrong decimal value from EF6 Code-First DB with MySQL

1 Answers1