4

I am using entity framework migrations and need to set the precision and scale of a decimal property in an entity. I only want to do this for this single decimal property not all decimal properties. I've already overridden the OnModelCreating method to set decimal to (18, 2) by default. I need this one property to be (22,5). So for example,

public class AdditionalCharge
{
  public decimal? Rate { get; set; }
}

gets created in the database as a "decimal (18,2) NULL" column. I need it to become a "decimal (22,5) NULL" column.

I can create an empty migration and hand code the change,

public override void Up()
{
  AlterColumn("dbo.AdditionalCharge", "Rate", c => c.Decimal(nullable: true, precision: 22, scale: 5));
}

but I'd rather just change the C# declaration and let migrations create the change.

Is there a way to do that?

Mike

D Stanley
  • 149,601
  • 11
  • 178
  • 240
Mike Lockhart
  • 143
  • 2
  • 4
  • 12
  • @DStanley Not 100% sure that this is a dupe as OP is asking for something slightly different. In this case it's probably either not possible or to add `Column` attribute with a specific `TypeName`. – DavidG May 20 '15 at 13:37
  • @DavidG I could be wrong but it seems like `modelBuilder.Entity().Property(o => o.Rate ).HasPrecision(22, 5);` should set the precision. Whether the migration tool will change _just_ the precision is unknown, so I'll agree to reopen it. – D Stanley May 20 '15 at 13:46
  • @DStanley Yeah, I'm really in 2 minds about this one too. I'm going to go play... – DavidG May 20 '15 at 13:48

2 Answers2

6

You can set the precision of the column in OnModelCreating as well:

modelBuilder.Entity<AdditionalCharge>().Property(o => o.Rate ).HasPrecision(22, 5);

But I don't know whether or not the change will be picked up in the migration.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • Thanks, but this doesn't address my issue. If I were creating the table it would be fine, but I have an existing table with data and my site is in production so I can't whack the tables/database and start from scratch at this point. I have to deal with the entity/table that exists. – Mike Lockhart May 20 '15 at 14:12
  • 1
    Aside from this 'new' information from OP, this does work so gets my vote. Interestingly, if you also try to do it using the `Column` and specify a `TypeName` of `DECIMAL(22, 5)` then migrations break completely, looks like a bug with EF. – DavidG May 20 '15 at 14:16
  • @MikeLockhart I didn't say you should start from scratch - I was hopeful the migration process would recognize the change and just change the type of the column (not drop/recreate it). If you add I can't speak as to what the migration SQL would look like for this change, so I'm sorry if it doesn't solve your problem. – D Stanley May 20 '15 at 14:29
  • A change of precision from 18 to 22 will result in an increase in the column size, however, so it may not be possible to change the size in-place. Meaning you may have to create a new column, move the data, and rename it, dropping the old column, which the migration tools may not do cleanly.. – D Stanley May 20 '15 at 14:33
  • @DStanley not a problem, just pointing out that the code snippet only gets run by OnModelCreating. I just coded the migration by hand and it worked fine. I was hoping to find a way to just add a decoration to the property declaration or something like that and have it alter the existing column precision and scale. I hadn't been able to find anything on the net saying I could do that. – Mike Lockhart May 20 '15 at 14:35
  • Migrations _should_ consider the customizations in `OnModelCreating` - I believe it compares the "new" model" to the current database structure and emits the code necessary to bring the current structure to the new structure. Like It said, though, it may not handle this particular change very cleanly (if at all). – D Stanley May 20 '15 at 14:39
0

I needed to do almost the same thing. I had datatypes in the database as decimal(18,2). That meant 18 total digits with a max of 2 after the decimal. I needed it to be decimal(18,5) and needed a migration to do this. To have Entity Framework 6 pick this up automatically, I changed the data annotation above my props in my model from [DataType(DataType.Currency)] to [Column(TypeName = "decimal(18,5)")] and Entity Framework picked this up and populated my migration file for me when I did 'add-migration'.

Old data types in my Model (ExampleForm.cs) before figuring this out:

[DataType(DataType.Currency)]
public decimal LaborCost { get; set; }

New data types in my Model (ExampleForm.cs) after figuring this out:

[Column(TypeName = "decimal(18,5)")]
public decimal LaborCost { get; set; }

I am working with dollar values, but I needed to allow some values entered to handle more than 2 digits after the decimal.

So, to specifically answer the question, above each prop that needs to be changed in the model, you should add

[Column(TypeName = "decimal(22,5)")]

Helpful Links: https://learn.microsoft.com/en-us/dotnet/api/system.componentmodel.dataannotations.datatype?view=net-7.0 https://learn.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql?view=sql-server-ver16

west
  • 36
  • 1
  • 8