If you’re using Entity Framework Code First, be careful with your Money!

After recently updating my database from the old model-first approach that relied on EDMX files to code first, I discovered a rather nasty little problem with regard to precision. In my work environment, I am using the T-SQL money data type for any data column that actually relates to money. I understand there is a healthy debate about whether you should be using the money data type at all, but many of the databases I’m managing are over 10 years old and have been built with the money data type from the beginning (so I’m going to stick with it).

Now, the .Net CLR doesn’t have a “Money” type and instead relies on the decimal datatype to represent money. Therefore, if you setup a mapping in EntityFramework as such:

public class Item
{
    public int ID { get; set; }
    public string ModelNumber { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
}

public partial class MyDbContext : DbContext
{
    //constructer/initializer code removed for brevity
    public DbSet<Item> Items { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new ItemMap());
    }
}

public class ItemMap : EntityTypeConfiguration<Item>
{
    public ItemMap()
    {
        // Primary Key
        this.HasKey(x => x.Item_ID);

        // Properties
        this.Property(x => x.ModelNumber)
            .IsRequired()
            .HasMaxLength(50);

        this.Property(x => x.Description)
            .IsRequired()
            .HasMaxLength(300);

        this.ToTable("Items");
        this.Property(x => x.ID).HasColumnName("ID");
        this.Property(x => x.ModelNumber).HasColumnName("ModelNumber");
        this.Property(x => x.Description).HasColumnName("Description");
        this.Property(x => x.Price).HasColumnName("Price");
    }
}

What can happen is you can lose precision when persisting to the database. Here is the example that eventually caught my attention:

  • We had an object where the money column had 4 places of precision (for example, $10.0025).
  • When instantiating this item as a POCO, the price would correctly be set to $10.0025.
  • However, when saving the object to the database, the price would save as $10.00. To make sure the value was being sent to EF correctly, I ran the debugger and checked the value right before the call to persist was made. The debugger showed the price value as a decimal data type with value $10.0025.

The important piece that was missing in the above code was this restriction during mapping (see highlighted line):

public class ItemMap : EntityTypeConfiguration<Item>
{
    public ItemMap()
    {
        // Primary Key
        this.HasKey(x => x.Item_ID);

        // Properties
        this.Property(x => x.ModelNumber)
            .IsRequired()
            .HasMaxLength(50);

        this.Property(x => x.Description)
            .IsRequired()
            .HasMaxLength(300);

        this.Property(x => x.Price)
            .HasColumnType("money");

        this.ToTable("Items");
        this.Property(x => x.ID).HasColumnName("ID");
        this.Property(x => x.ModelNumber).HasColumnName("ModelNumber");
        this.Property(x => x.Description).HasColumnName("Description");
        this.Property(x => x.Price).HasColumnName("Price");
    }
}

Now, the values persist correctly.

In order to quickly identify all of the tables in a database that have the type of money, you can run the following SQL code against your database:

SELECT table_name, column_name
FROM information_schema.columns where data_type = 'money'
ORDER BY table_name, column_name

Then, you just need to add the constraints to your entity mappings.