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?
This question is related to
c#
.net
entity-framework
ef-code-first
decimal
Actual for EntityFrameworkCore 3.1.3:
some solution in OnModelCreating:
var fixDecimalDatas = new List<Tuple<Type, Type, string>>();
foreach (var entityType in builder.Model.GetEntityTypes())
{
foreach (var property in entityType.GetProperties())
{
if (Type.GetTypeCode(property.ClrType) == TypeCode.Decimal)
{
fixDecimalDatas.Add(new Tuple<Type, Type, string>(entityType.ClrType, property.ClrType, property.GetColumnName()));
}
}
}
foreach (var item in fixDecimalDatas)
{
builder.Entity(item.Item1).Property(item.Item2, item.Item3).HasColumnType("decimal(18,4)");
}
//custom decimal nullable:
builder.Entity<SomePerfectEntity>().Property(x => x.IsBeautiful).HasColumnType("decimal(18,4)");
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.
Using
System.ComponentModel.DataAnnotations;
You can simply put that attribute in your model :
[DataType("decimal(18,5)")]
this code line could be a simpler way to acomplish the same:
public class ProductConfiguration : EntityTypeConfiguration<Product>
{
public ProductConfiguration()
{
this.Property(m => m.Price).HasPrecision(10, 2);
}
}
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());
}
- FOR EF CORE - with using System.ComponentModel.DataAnnotations;
use [Column(TypeName = "decimal(precision, scale)")]
Precision = Total number of characters used
Scale = Total number after the dot. (easy to get confused)
Example:
public class Blog
{
public int BlogId { get; set; }
[Column(TypeName = "varchar(200)")]
public string Url { get; set; }
[Column(TypeName = "decimal(5, 2)")]
public decimal Rating { get; set; }
}
More details here: https://docs.microsoft.com/en-us/ef/core/modeling/relational/data-types
In EF6
modelBuilder.Properties()
.Where(x => x.GetCustomAttributes(false).OfType<DecimalPrecisionAttribute>().Any())
.Configure(c => {
var attr = (DecimalPrecisionAttribute)c.ClrPropertyInfo.GetCustomAttributes(typeof (DecimalPrecisionAttribute), true).FirstOrDefault();
c.HasPrecision(attr.Precision, attr.Scale);
});
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);
}
}
You can found more information on MSDN - facet of Entity Data Model. http://msdn.microsoft.com/en-us/library/ee382834.aspx Full recommended.
You can always tell EF to do this with conventions in the Context class in the OnModelCreating function as follows:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
// <... other configurations ...>
// modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
// modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
// modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
// Configure Decimal to always have a precision of 18 and a scale of 4
modelBuilder.Conventions.Remove<DecimalPropertyConvention>();
modelBuilder.Conventions.Add(new DecimalPropertyConvention(18, 4));
base.OnModelCreating(modelBuilder);
}
This only applies to Code First EF fyi and applies to all decimal types mapped to the db.
KinSlayerUY's custom attribute worked nicely for me but I had issues with ComplexTypes. They were being mapped as entities in the attribute code so couldn't then be mapped as a ComplexType.
I therefore extended the code to allow for this:
public static void OnModelCreating(DbModelBuilder modelBuilder)
{
foreach (Type classType in from t in Assembly.GetAssembly(typeof(DecimalPrecisionAttribute)).GetTypes()
where t.IsClass && t.Namespace == "FA.f1rstval.Data"
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) }))
{
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;
int MethodNum;
if (propAttr.prop.PropertyType.IsGenericType && propAttr.prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
MethodNum = 7;
}
else
{
MethodNum = 6;
}
//check if complextype
if (classType.GetCustomAttribute<ComplexTypeAttribute>() != null)
{
var complexConfig = modelBuilder.GetType().GetMethod("ComplexType").MakeGenericMethod(classType).Invoke(modelBuilder, null);
MethodInfo methodInfo = complexConfig.GetType().GetMethods().Where(p => p.Name == "Property").ToList()[MethodNum];
decimalConfig = methodInfo.Invoke(complexConfig, new[] { lambdaExpression }) as DecimalPropertyConfiguration;
}
else
{
var entityConfig = modelBuilder.GetType().GetMethod("Entity").MakeGenericMethod(classType).Invoke(modelBuilder, null);
MethodInfo methodInfo = entityConfig.GetType().GetMethods().Where(p => p.Name == "Property").ToList()[MethodNum];
decimalConfig = methodInfo.Invoke(entityConfig, new[] { lambdaExpression }) as DecimalPropertyConfiguration;
}
decimalConfig.HasPrecision(propAttr.attr.Precision, propAttr.attr.Scale);
}
}
}
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.
Entity Framework Ver 6 (Alpha, rc1) has something called Custom Conventions. To set decimal precision:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Properties<decimal>().Configure(config => config.HasPrecision(18, 4));
}
Reference:
[Column(TypeName = "decimal(18,2)")]
this will work with EF Core code first migrations as described here.
@Mark007, I have changed the type selection criteria to ride of the DbSet<> properties of the DbContext. I think this is safer because there are times when you have classes in the given namespace that shouldn't be part of the model definition or they are but are not entities. Or your entities could reside in separate namespaces or separate assemblies and be pulled together into once Context.
Also, even though unlikely, I do not think it's safe to rely on ordering of method definitions, so it's better to pull them out with by Parameter list. (.GetTypeMethods() is an extension method I built to work with the new TypeInfo paradigm and can flatten class hierarchies when looking for methods).
Do note that OnModelCreating delegates to this method:
private void OnModelCreatingSetDecimalPrecisionFromAttribute(DbModelBuilder modelBuilder)
{
foreach (var iSetProp in this.GetType().GetTypeProperties(true))
{
if (iSetProp.PropertyType.IsGenericType
&& (iSetProp.PropertyType.GetGenericTypeDefinition() == typeof(IDbSet<>) || iSetProp.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>)))
{
var entityType = iSetProp.PropertyType.GetGenericArguments()[0];
foreach (var propAttr in entityType
.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Select(p => new { prop = p, attr = p.GetCustomAttribute<DecimalPrecisionAttribute>(true) })
.Where(propAttr => propAttr.attr != null))
{
var entityTypeConfigMethod = modelBuilder.GetType().GetTypeInfo().DeclaredMethods.First(m => m.Name == "Entity");
var entityTypeConfig = entityTypeConfigMethod.MakeGenericMethod(entityType).Invoke(modelBuilder, null);
var param = ParameterExpression.Parameter(entityType, "c");
var lambdaExpression = Expression.Lambda(Expression.Property(param, propAttr.prop.Name), true, new ParameterExpression[] { param });
var propertyConfigMethod =
entityTypeConfig.GetType()
.GetTypeMethods(true, false)
.First(m =>
{
if (m.Name != "Property")
return false;
var methodParams = m.GetParameters();
return methodParams.Length == 1 && methodParams[0].ParameterType == lambdaExpression.GetType();
}
);
var decimalConfig = propertyConfigMethod.Invoke(entityTypeConfig, new[] { lambdaExpression }) as DecimalPropertyConfiguration;
decimalConfig.HasPrecision(propAttr.attr.Precision, propAttr.attr.Scale);
}
}
}
}
public static IEnumerable<MethodInfo> GetTypeMethods(this Type typeToQuery, bool flattenHierarchy, bool? staticMembers)
{
var typeInfo = typeToQuery.GetTypeInfo();
foreach (var iField in typeInfo.DeclaredMethods.Where(fi => staticMembers == null || fi.IsStatic == staticMembers))
yield return iField;
//this bit is just for StaticFields so we pass flag to flattenHierarchy and for the purpose of recursion, restrictStatic = false
if (flattenHierarchy == true)
{
var baseType = typeInfo.BaseType;
if ((baseType != null) && (baseType != typeof(object)))
{
foreach (var iField in baseType.GetTypeMethods(true, staticMembers))
yield return iField;
}
}
}
Source: Stackoverflow.com