Decimal precision and scale in EF Code First

Question

The answer from Dave Van den Eynde is now out of date. There are 2 important changes, from EF 4.1 onwards the ModelBuilder class is now DbModelBuilder and there is now a DecimalPropertyConfiguration.HasPrecision Method which has a signature of:

public DecimalPropertyConfiguration HasPrecision(
byte precision,
byte scale )

where precision is the total number of digits the db will store, regardless of where the decimal point falls and scale is the number of decimal places it will store.

Therefore there is no need to iterate through properties as shown but the can just be called from

public class EFDbContext : DbContext
{
   protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
   {
       modelBuilder.Entity<Class>().Property(object => object.property).HasPrecision(12, 10);

       base.OnModelCreating(modelBuilder);
   }
}

If you want to set the precision for all decimals in EF6 you could replace the default DecimalPropertyConvention convention used in the DbModelBuilder:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<DecimalPropertyConvention>();
    modelBuilder.Conventions.Add(new DecimalPropertyConvention(38, 18));
}

The default DecimalPropertyConvention in EF6 maps decimal properties to decimal(18,2) columns.

If you only want individual properties to have a specified precision then you can set the precision for the entity's property on the DbModelBuilder:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<MyEntity>().Property(e => e.Value).HasPrecision(38, 18);
}

Or, add an EntityTypeConfiguration<> for the entity which specifies the precision:

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

internal class MyEntityConfiguration : EntityTypeConfiguration<MyEntity>
{
    internal MyEntityConfiguration()
    {
        this.Property(e => e.Value).HasPrecision(38, 18);
    }
}

I had a nice time creating an Custom Attribute for this:

[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]
public sealed class DecimalPrecisionAttribute : Attribute
{
    public DecimalPrecisionAttribute(byte precision, byte scale)
    {
        Precision = precision;
        Scale = scale;

    }

    public byte Precision { get; set; }
    public byte Scale { get; set; }

}

using it like this

[DecimalPrecision(20,10)]
public Nullable<decimal> DeliveryPrice { get; set; }

and the magic happens at model creation with some reflection

protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
{
    foreach (Type classType in from t in Assembly.GetAssembly(typeof(DecimalPrecisionAttribute)).GetTypes()
                                   where t.IsClass && t.Namespace == "YOURMODELNAMESPACE"
                                   select t)
     {
         foreach (var propAttr in classType.GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(p => p.GetCustomAttribute<DecimalPrecisionAttribute>() != null).Select(
                p => new { prop = p, attr = p.GetCustomAttribute<DecimalPrecisionAttribute>(true) }))
         {

             var entityConfig = modelBuilder.GetType().GetMethod("Entity").MakeGenericMethod(classType).Invoke(modelBuilder, null);
             ParameterExpression param = ParameterExpression.Parameter(classType, "c");
             Expression property = Expression.Property(param, propAttr.prop.Name);
             LambdaExpression lambdaExpression = Expression.Lambda(property, true,
                                                                      new ParameterExpression[]
                                                                          {param});
             DecimalPropertyConfiguration decimalConfig;
             if (propAttr.prop.PropertyType.IsGenericType && propAttr.prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
             {
                 MethodInfo methodInfo = entityConfig.GetType().GetMethods().Where(p => p.Name == "Property").ToList()[7];
                 decimalConfig = methodInfo.Invoke(entityConfig, new[] { lambdaExpression }) as DecimalPropertyConfiguration;
             }
             else
             {
                 MethodInfo methodInfo = entityConfig.GetType().GetMethods().Where(p => p.Name == "Property").ToList()[6];
                 decimalConfig = methodInfo.Invoke(entityConfig, new[] { lambdaExpression }) as DecimalPropertyConfiguration;
             }

             decimalConfig.HasPrecision(propAttr.attr.Precision, propAttr.attr.Scale);
        }
    }
}

the first part is to get all classes in the model (my custom attribute is defined in that assembly so i used that to get the assembly with the model)

the second foreach gets all properties in that class with the custom attribute, and the attribute itself so i can get the precision and scale data

after that i have to call

modelBuilder.Entity<MODEL_CLASS>().Property(c=> c.PROPERTY_NAME).HasPrecision(PRECISION,SCALE);

so i call the modelBuilder.Entity() by reflection and store it in the entityConfig variable then i build the "c => c.PROPERTY_NAME" lambda expression

After that, if the decimal is nullable i call the

Property(Expression<Func<TStructuralType, decimal?>> propertyExpression) 

method (i call this by the position in the array, it's not ideal i know, any help will be much appreciated)

and if it's not nullable i call the

Property(Expression<Func<TStructuralType, decimal>> propertyExpression)

method.

Having the DecimalPropertyConfiguration i call the HasPrecision method.

Using the DecimalPrecisonAttribute from KinSlayerUY, in EF6 you can create a convention which will handle individual properties which have the attribute (as opposed to setting the DecimalPropertyConvention like in this answer which will affect all decimal properties).

[AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]
public sealed class DecimalPrecisionAttribute : Attribute
{
    public DecimalPrecisionAttribute(byte precision, byte scale)
    {
        Precision = precision;
        Scale = scale;
    }
    public byte Precision { get; set; }
    public byte Scale { get; set; }
}

public class DecimalPrecisionAttributeConvention
    : PrimitivePropertyAttributeConfigurationConvention<DecimalPrecisionAttribute>
{
    public override void Apply(ConventionPrimitivePropertyConfiguration configuration, DecimalPrecisionAttribute attribute)
    {
        if (attribute.Precision < 1 || attribute.Precision > 38)
        {
            throw new InvalidOperationException("Precision must be between 1 and 38.");
        }

        if (attribute.Scale > attribute.Precision)
        {
            throw new InvalidOperationException("Scale must be between 0 and the Precision value.");
        }

        configuration.HasPrecision(attribute.Precision, attribute.Scale);
    }
}

Then in your DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Add(new DecimalPrecisionAttributeConvention());
}

Apparently, you can override the DbContext.OnModelCreating() method and configure the precision like this:

protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>().Property(product => product.Price).Precision = 10;
    modelBuilder.Entity<Product>().Property(product => product.Price).Scale = 2;
}

But this is pretty tedious code when you have to do it with all your price-related properties, so I came up with this:

    protected override void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)
    {
        var properties = new[]
        {
            modelBuilder.Entity<Product>().Property(product => product.Price),
            modelBuilder.Entity<Order>().Property(order => order.OrderTotal),
            modelBuilder.Entity<OrderDetail>().Property(detail => detail.Total),
            modelBuilder.Entity<Option>().Property(option => option.Price)
        };

        properties.ToList().ForEach(property =>
        {
            property.Precision = 10;
            property.Scale = 2;
        });

        base.OnModelCreating(modelBuilder);
    }

It's good practice that you call the base method when you override a method, even though the base implementation does nothing.

Update: This article was also very helpful.