My client has a standard of storing SQL Server decimals with a decimal(13,4) specification. As a result, in a very large and still-growing schema, I have nearly a hundred statements like these:
builder.Entity<MyObject>()
.Property(x => x.MyField1)
.ForSqlServerHasColumnType("decimal(13,4)");
builder.Entity<MyObject>()
.Property(x => x.MyField2)
.ForSqlServerHasColumnType("decimal(13,4)");
builder.Entity<MyObject2>()
.Property(x => x.MyField1)
.ForSqlServerHasColumnType("decimal(13,4)");
If there is a feature where I can tell EF directly that all decimals should be decimal(13,4) by default, I would like to use that. If not, can I use reflection to loop through every object/property in the model so I can do this in a couple statements?
Something like:
foreach(var efObj in EntityFrameWorkObjects)
{
foreach (var objProperty in efObj)
{
if (objProperty is decimal || objProperty is decimal?)
{
builder.Entity<efObj>()
.Property(x => x.efObj)
.ForSqlServerHasColumnType("decimal(13,4)");
}
}
}
Reflection seems like a great way to go, because then I can implement some of our other conventions where, if an object has a Name and Description, the Name is required and limited to 256 chars.
Update: I followed the link in Ivan's comment and adapted it to this, which works for me:
foreach (var p in builder.Model
.GetEntityTypes()
.SelectMany(t => t.GetProperties())
.Where(p =>
p.ClrType == typeof(decimal) ||
p.ClrType == typeof(decimal?)))
{
p.SqlServer().ColumnType = "decimal(13,4)";
}
Soon after, he provided a full answer, which I changed slightly to work with both decimal and nullable decimal:
foreach (var pb in builder.Model
.GetEntityTypes()
.SelectMany(t => t.GetProperties())
.Where(p =>
p.ClrType == typeof(decimal) ||
p.ClrType == typeof(decimal?))
.Select(p =>
builder.Entity(p.DeclaringEntityType.ClrType)
.Property(p.Name)))
{
pb.ForSqlServerHasColumnType("decimal(13,4)");
}
Both approaches work!
Update 2: I had to have my objects declared as DbSet<> in the context for the above to work. This didn't seem to be required when I was setting properties line by line.
In EF Core v1.1.0 you can use something like this:
foreach (var pb in modelBuilder.Model
.GetEntityTypes()
.SelectMany(t => t.GetProperties())
.Where(p => p.ClrType == typeof(decimal) || p.ClrType == typeof(decimal?))
.Select(p => modelBuilder.Entity(p.DeclaringEntityType.ClrType).Property(p.Name)))
{
pb.ForSqlServerHasColumnType("decimal(13,4)");
}
Update (EF Core 2.x): Starting from EF Core 2.0, the model is built separately for each database provider, so HasAbcXyz
methods are replaced with common HasXyz
. The updated code (which also skips the explicitly configured properties) looks like this:
foreach (var property in modelBuilder.Model.GetEntityTypes()
.SelectMany(t => t.GetProperties())
.Where(p => p.ClrType == typeof(decimal) || p.ClrType == typeof(decimal?)))
{
if (property.Relational().ColumnType == null)
property.Relational().ColumnType = "decimal(13,4)";
}
Update (EF Core 3.x): With EF Core 3.0 metadata API changes (Relational()
extensions removed, properties replaced with Get
/ Set
method pair), the code is as follows:
foreach (var property in modelBuilder.Model.GetEntityTypes()
.SelectMany(t => t.GetProperties())
.Where(p => p.ClrType == typeof(decimal) || p.ClrType == typeof(decimal?)))
{
if (property.GetColumnType() == null)
property.SetColumnType("decimal(13,4)");
}