Use money type in Entity Framework model first

Question

I created a table that has a column called Amount and I set it as Decimal. I can't find where to set it as money or smallmoney and when I try to set a value like 0.2, it rounds to 0....

How can I use money with entity framework?

thanks!

Answer 1

My response is regarding E.F 6.0 After dealing with a similar issue and check with SQL Profiler: Entity framework translates the Decimal variable type as Decimal(18, 2) in SQL.

If you want save Money type I would recommend to use Data Annotations and add

public class Account
{
    public int AccountId { get; set; }                

    [Column(TypeName="money")]
    public decimal Amount { get; set; }
}

Or if you use Fluent API

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Account>()
                .Property(i => i.Amount)
                .HasColumnType("money");
}

• Money type will translated to (19, 4)

Answer 2

One of the annoying things about Entity Framework is that it translates the Decimal variable type as Decimal(18, 0) in SQL.

The number after the comma is the number of decimal places allowed on the number. So Decimal(18,0) will strip the numbers after the decimal point when it saves.

Go to SQL Server Management stuido, find your database, right click on the table and select "Design". Then select the column from the list and you should be able to change the datatype to Decimal(18, 2) just by typing in the box. Then save the changes to the table. It'll then save the number to the usual two decimal places used in western currency transactions. If you need more decimal places, just increase the second number inside the brackets accordingly.

I have never found a setting that changes this EF default so that it generates Decimal(18, 2) instead. But then I never looked: it's easy enough to change in the DB. But if someone knows of one, I'd be curious to know how it's done.

EDIT: More information - including configuration fix - in this answer: Decimal precision and scale in EF Code First