Use money type in Entity Framework model first

I created a table that has a column called Amount and I set it as Deci开发者_运维技巧mal. 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!

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)

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

So I think your question may have been for a Code first approach. Also, I'm not sure what version of SQL, you are using, my answer below is just for SQL Server.. But you'll basically do it the same way either for Oracle or MySQL.

Model first :

Using SQL Server, you simply will select either "money" or "smallmoney" for the field, depending on how large of a value you need to represent.

Code first :

I needed to express a money type up to 9999.99

Within the visual EDMX editor, you'll select the scalar property, go to Properties then set it to Type = Decimal, then set the Facets as Precision = 6 Scale = 2. You can use Data Annotations to ensure that the field is displayed & input as money.

This is similar to Decimal precision and scale in EF Code First . I use EF 6.1 and code first which for some reason defines smallmoney as precision 2 by default event though it is/should be 4. You need to add some Fluent code to fix this. I use something like:

public static void OnModelCreating(DbModelBuilder modelBuilder)
{
        modelBuilder.Entity<MYCLASSNAME>().Property(x => x.MYPROPNAME).HasPrecision(5+4, 4);
}