In our projects with OData services, we have a lot of models with common fields. When changing these field to a baseclass, Migrations does want to update your database.
Introduction
When refactoring our ASP.NET WEBAPI Odata services, we found four fields return in each model. One of them is the ModificationDate
property which is a TimeStamp
field. So when removing these four fields from your model and you add them via a baseclass to your model, Migrations does something very stupid. It creates an AlterColumn
statement for your TimeStamp
field. When you run the Update-Database command from your Package Manager Console, you will see a fine error:
Cannot alter column 'ModificationDate' to be data type timestamp.
But the documentation of Microsoft SQL server is very clear.
ALTER COLUMN
Specifies that the named column is to be changed or altered. For more information, see sp_dbcmptlevel (Transact-SQL).
The modified column cannot be any one of the following:
This is a BUG in Migrations!
Background
The ModificationDate
property in the baseclass is setup as:
[Timestamp]
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public byte[] ModificationDate { get; set; }
How to Solve
In your created Add-Migration <YourPickedName>
, you have to do the following.
Your line in the Up()
method will be:
AlterColumn("dbo.YourTable", "ModificationDate",
c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
You change that into:
DropColumn("dbo.YourTable", "ModificationDate");
AddColumn("dbo.YourTable", "ModificationDate",
c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));
So a hard drop and create is the solution to this.
The next problem is that we had to change it for over 300 tables so we created code in our service that when creating a migration, both the drop and add columns are created if we have ModificationDate
as fieldname
.
Unfortunately, System.Data.Entity.Core.Metadata.Edm.PrimitiveTypeKind
has Byte
but not TimeStamp
or RowVersion
available. Otherwise, you could fix this over its DataType
(alterColumnOperation.Column.Type
).
How to Automate This
In your project, you have a Migrations folder in which you add a class MyCodeGenerator
:
using System.Data.Entity.Migrations.Design;
using System.Data.Entity.Migrations.Model;
using System.Data.Entity.Migrations.Utilities;
namespace MyHappyService.Migrations
{
internal class MyCodeGenerator : CSharpMigrationCodeGenerator
{
protected override void Generate
(AlterColumnOperation alterColumnOperation, IndentedTextWriter writer)
{
if (alterColumnOperation.Column.Name == "ModificationDate")
{
DropColumnOperation dropColumnOperation = new DropColumnOperation
(alterColumnOperation.Table, alterColumnOperation.Column.Name);
AddColumnOperation addColumnOperation = new AddColumnOperation
(alterColumnOperation.Table, alterColumnOperation.Column);
base.Generate(dropColumnOperation, writer);
base.Generate(addColumnOperation, writer);
}
else
base.Generate(alterColumnOperation, writer);
}
}
}
In your Configuration.cs, you add in the constructor:
CodeGenerator = new MyCodeGenerator();
Next time you add a new migration, this issue is fixed for you.
Happy coding!
History
- 2nd November, 2021: Init v0.1: First write about this issue