Problem
I’m trying out this code-first method, however I’ve discovered that a property of type System is required. Decimal is mapped to a decimal-type sql column (18, 0).
How do I adjust the database column’s precision?
Asked by Dave Van den Eynde
Solution #1
Dave Van den Eynde’s response is no longer valid. There are two significant changes in EF 4.1 and later: the ModelBuilder class is renamed DbModelBuilder, and a DecimalPropertyConfiguration has been added. The signature of the HasPrecision method is:
public DecimalPropertyConfiguration HasPrecision(
byte precision,
byte scale )
where accuracy is the total amount of digits stored by the database, regardless of where the decimal point is located, and scale is the number of decimal places stored by the database.
As a result, instead of iterating through attributes as illustrated, the can simply be invoked 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);
}
}
Answered by AlexC
Solution #2
Replace the default DecimalPropertyConvention convention in the DbModelBuilder with the following if you wish to set the precision for all decimals in EF6:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<DecimalPropertyConvention>();
modelBuilder.Conventions.Add(new DecimalPropertyConvention(38, 18));
}
In EF6, the default DecimalPropertyConvention corresponds to decimal(18,2) columns.
You can set the precision for the entity’s property on the DbModelBuilder if you just want individual properties to have a specific precision.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<MyEntity>().Property(e => e.Value).HasPrecision(38, 18);
}
Alternatively, for the entity, add an EntityTypeConfiguration> that 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);
}
}
Answered by kjbartel
Solution #3
For this, I had a lot of fun building a Custom Attribute:
[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; }
With some reflection, the magic happens at model development.
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 step is to collect all of the model’s classes (my custom attribute is defined in that assembly so I used that to get the assembly with the model)
The second foreach retrieves all properties in that class that include the custom attribute, as well as the attribute itself, so that I can obtain precision and scale data.
I have to call after that.
modelBuilder.Entity<MODEL_CLASS>().Property(c=> c.PROPERTY_NAME).HasPrecision(PRECISION,SCALE);
As a result, I invoke the modelBuilder. I build the lambda expression “c => c.PROPERTY NAME” by reflection and save it in the entityConfig variable.
If the decimal is nullable, I then call the
Property(Expression<Func<TStructuralType, decimal?>> propertyExpression)
method (I call this by the array’s position, which isn’t ideal, but any help would be greatly appreciated)
I call the nullable if it’s not nullable.
Property(Expression<Func<TStructuralType, decimal>> propertyExpression)
method.
I use the HasPrecision technique with the DecimalPropertyConfiguration.
Answered by KinSlayerUY
Solution #4
In EF6, you may use KinSlayerUY’s DecimalPrecisonAttribute to establish a convention that will handle individual properties with 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 add the following to your DbContext:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Add(new DecimalPrecisionAttributeConvention());
}
Answered by kjbartel
Solution #5
The DbContext, it appears, can be overridden. Set the precision in the OnModelCreating() method as follows:
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, because this is a tiresome code to write when you have to do it for all of your price-related attributes, I devised the following:
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);
}
Even if the base implementation does nothing, it’s good practice to call the base method when overriding a method.
This article was also really useful.
Answered by Dave Van den Eynde
Post is based on https://stackoverflow.com/questions/3504660/decimal-precision-and-scale-in-ef-code-first