Decimal precision and scale in EF Code First

Question

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

User · Answer

Entity Framework Ver 6  Alpha  rc1  has something called Custom Conventions  To set decimal precision   protected override void OnModelCreating DbModelBuilder modelBuilder        modelBuilder Properties lt decimal gt    Configure config   gt  config HasPrecision 18  4        Reference    http   entityframework codeplex com wikipage title Custom 20Conventions http   weblogs asp net scottgu archive 2012 12 11 entity-framework-6-alpha2-now-available aspx

User · Answer

Using   System ComponentModel DataAnnotations    You can simply put that attribute in your model     DataType  decimal 18 5

User · Answer

Mark007  I have changed the type selection criteria to ride of the DbSet lt   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                      amp  amp   iSetProp PropertyType GetGenericTypeDefinition      typeof IDbSet lt  gt      iSetProp PropertyType GetGenericTypeDefinition      typeof DbSet lt  gt                                   var entityType   iSetProp PropertyType GetGenericArguments   0                    foreach  var propAttr in entityType                                          GetProperties BindingFlags Public   BindingFlags Instance                                           Select p   gt  new   prop   p  attr   p GetCustomAttribute lt DecimalPrecisionAttribute gt  true                                              Where propAttr   gt  propAttr attr    null                                         var entityTypeConfigMethod   modelBuilder GetType   GetTypeInfo   DeclaredMethods First m   gt  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   gt                                                                if  m Name     Property                                       return false                                   var methodParams   m GetParameters                                     return methodParams Length    1  amp  amp  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 lt MethodInfo gt  GetTypeMethods this Type typeToQuery  bool flattenHierarchy  bool  staticMembers                var typeInfo   typeToQuery GetTypeInfo             foreach  var iField in typeInfo DeclaredMethods Where fi   gt  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   amp  amp   baseType    typeof object                                  foreach  var iField in baseType GetTypeMethods true  staticMembers                       yield return iField

User · Answer

You can found more information on MSDN - facet of Entity Data Model  http   msdn microsoft com en-us library ee382834 aspx Full recommended

User · Answer

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 lt Class gt    Property object   gt  object property  HasPrecision 12  10           base OnModelCreating modelBuilder

User · Answer

- 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

User · Answer

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 lt decimal gt  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  amp  amp  t Namespace     YOURMODELNAMESPACE                                     select t                  foreach  var propAttr in classType GetProperties BindingFlags Public   BindingFlags Instance  Where p   gt  p GetCustomAttribute lt DecimalPrecisionAttribute gt       null  Select                  p   gt  new   prop   p  attr   p GetCustomAttribute lt DecimalPrecisionAttribute gt  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  amp  amp  propAttr prop PropertyType GetGenericTypeDefinition      typeof Nullable lt  gt                                    MethodInfo methodInfo   entityConfig GetType   GetMethods   Where p   gt  p Name     Property   ToList   7                    decimalConfig   methodInfo Invoke entityConfig  new     lambdaExpression    as DecimalPropertyConfiguration                              else                                 MethodInfo methodInfo   entityConfig GetType   GetMethods   Where p   gt  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 lt MODEL CLASS gt    Property c  gt  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 lt Func lt TStructuralType  decimal  gt  gt  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 lt Func lt TStructuralType  decimal gt  gt  propertyExpression    method   Having the DecimalPropertyConfiguration i call the HasPrecision method

User · Answer

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 lt Product gt    Property product   gt  product Price  Precision   10      modelBuilder Entity lt Product gt    Property product   gt  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 lt Product gt    Property product   gt  product Price               modelBuilder Entity lt Order gt    Property order   gt  order OrderTotal               modelBuilder Entity lt OrderDetail gt    Property detail   gt  detail Total               modelBuilder Entity lt Option gt    Property option   gt  option Price                      properties ToList   ForEach property   gt                        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

User · Answer

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 lt DecimalPropertyConvention gt         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 lt MyEntity gt    Property e   gt  e Value  HasPrecision 38  18       Or  add an EntityTypeConfiguration lt  gt  for the entity which specifies the precision   protected override void OnModelCreating DbModelBuilder modelBuilder        modelBuilder Configurations Add new MyEntityConfiguration        internal class MyEntityConfiguration   EntityTypeConfiguration lt MyEntity gt        internal MyEntityConfiguration                 this Property e   gt  e Value  HasPrecision 38  18

User · Answer

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            lt     other configurations     gt         modelBuilder Conventions Remove lt PluralizingTableNameConvention gt            modelBuilder Conventions Remove lt ManyToManyCascadeDeleteConvention gt            modelBuilder Conventions Remove lt OneToManyCascadeDeleteConvention gt             Configure Decimal to always have a precision of 18 and a scale of 4     modelBuilder Conventions Remove lt DecimalPropertyConvention gt         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

User · Answer

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  amp  amp  t Namespace     FA f1rstval Data                                     select t                        foreach  var propAttr in classType GetProperties BindingFlags Public   BindingFlags Instance  Where p   gt  p GetCustomAttribute lt DecimalPrecisionAttribute gt       null  Select                     p   gt  new   prop   p  attr   p GetCustomAttribute lt DecimalPrecisionAttribute gt  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  amp  amp  propAttr prop PropertyType GetGenericTypeDefinition      typeof Nullable lt  gt                                          MethodNum   7                                    else                                       MethodNum   6                                       check if complextype                 if  classType GetCustomAttribute lt ComplexTypeAttribute gt       null                                        var complexConfig   modelBuilder GetType   GetMethod  ComplexType   MakeGenericMethod classType  Invoke modelBuilder  null                       MethodInfo methodInfo   complexConfig GetType   GetMethods   Where p   gt  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   gt  p Name     Property   ToList   MethodNum                       decimalConfig   methodInfo Invoke entityConfig  new     lambdaExpression    as DecimalPropertyConfiguration                                     decimalConfig HasPrecision propAttr attr Precision  propAttr attr Scale

User · Answer

this code line could be a simpler way to acomplish the same    public class ProductConfiguration   EntityTypeConfiguration lt Product gt                public ProductConfiguration                         this Property m   gt  m Price  HasPrecision 10  2

User · Answer

In EF6  modelBuilder Properties        Where x   gt  x GetCustomAttributes false  OfType lt DecimalPrecisionAttribute gt    Any         Configure c   gt            var attr    DecimalPrecisionAttribute c ClrPropertyInfo GetCustomAttributes typeof  DecimalPrecisionAttribute   true  FirstOrDefault             c HasPrecision attr Precision  attr Scale

User · Answer

Column TypeName    decimal 18 2       this will work with EF Core code first migrations as described here

User · Answer

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 lt DecimalPrecisionAttribute gt        public override void Apply ConventionPrimitivePropertyConfiguration configuration  DecimalPrecisionAttribute attribute                if  attribute Precision  lt  1    attribute Precision  gt  38                        throw new InvalidOperationException  Precision must be between 1 and 38                        if  attribute Scale  gt  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

User · Answer

Actual for EntityFrameworkCore 3 1 3   some solution in OnModelCreating   var fixDecimalDatas   new List lt Tuple lt Type  Type  string gt  gt     foreach  var entityType in builder Model GetEntityTypes          foreach  var property in entityType GetProperties                  if  Type GetTypeCode property ClrType     TypeCode Decimal                        fixDecimalDatas Add new Tuple lt Type  Type  string gt  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 lt SomePerfectEntity gt    Property x   gt  x IsBeautiful  HasColumnType  decimal 18 4

[c#] Decimal precision and scale in EF Code First

Examples related to c#

Examples related to .net

Examples related to entity-framework

Examples related to ef-code-first

Examples related to decimal