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

Answer 3

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.

Answer 4

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);
}

Answer 5

first the money class

public class Money
{

    public decimal Amount { get; set; }
    public string Currency { get; set; }

    public Money()
    {
    }


    public Money(decimal amount, string currency)
    {
        Amount = amount;
        Currency = currency;
    }
}

moneyvalueconverter

public class MoneyValueConverter : ValueConverter<Money, string>
{
    private static readonly JsonSerializerOptions _jsonOptions = new JsonSerializerOptions
    {
        PropertyNamingPolicy = JsonNamingPolicy.CamelCase,
        WriteIndented = false
    };

    public MoneyValueConverter() : base(
        v => ConvertToString(v),
        v => ConvertToMoney(v))
    {
    }

    private static string ConvertToString(Money value)
    {
        // Convert the Money value to a JSON string representation
        // that can be stored in the database
        return JsonSerializer.Serialize(value, _jsonOptions);
    }

private static Money ConvertToMoney(string value)
{
    // Convert the JSON string value retrieved from the database
    // back to the Money object
    return JsonSerializer.Deserialize<Money>(value, new JsonSerializerOptions
    {
        PropertyNamingPolicy = JsonNamingPolicy.CamelCase
    });
}

ModelBuilderExtensions

public static class ModelBuilderExtensions
{
    public static void ApplyMoneyValueConverter(this ModelBuilder modelBuilder)
    {
        var entityTypeList = modelBuilder.Model.GetEntityTypes().ToList();

        foreach (var entityType in entityTypeList)
        {
            var properties = entityType.ClrType.GetProperties()
                .Where(p => p.PropertyType == typeof(Money));

            foreach (var property in properties)
            {
                var entityBuilder = modelBuilder.Entity(entityType.ClrType);
                var propertyBuilder = entityBuilder.Property(property.PropertyType, property.Name);

                propertyBuilder.HasConversion(new MoneyValueConverter())
                               .HasColumnType("jsonb");
            }
        }
    }
}

Then in your DbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.ApplyMoneyValueConverter();
}

In your entity you can have a property like this:

public Money HourlyRate {get; set;}

in your database there is a field called HourlyRate that has a jsonb

e.g. {"amount": 30.00, "currency": "EUR"}