Decimal precision and scale in EF Code First

I'm experimenting with this code-first approach, but I'm find out now that a property of type System.Decimal gets mapped to a sql column of type decimal(18, 0).

How do I set the precision of the database column?

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

Decimal precision and scale in EF Code First

Related

Recent Posts