Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

EF6 Migrations and TimeStamp Bug with CodeFirst

5.00/5 (3 votes)
2 Nov 2021CPOL2 min read 6.7K  
Migrations does not apply to T-SQL rules for ALTER TABLE ALTER COLUMN
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:

  • A column with a timestamp data type.

This is a BUG in Migrations!

Background

The ModificationDate property in the baseclass is setup as:

C#
/// <summary>
/// Gets or sets the modification date.
/// </summary>
/// <value>
/// The modification date.
/// </value>
[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:

C#
AlterColumn("dbo.YourTable", "ModificationDate", 
c => c.Binary(nullable: false, fixedLength: true, timestamp: true, storeType: "rowversion"));

You change that into:

C#
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:

C#
using System.Data.Entity.Migrations.Design;
using System.Data.Entity.Migrations.Model;
using System.Data.Entity.Migrations.Utilities;
 
namespace MyHappyService.Migrations
{
    /// <summary>
    /// 
    /// </summary>
    /// <seealso cref="System.Data.Entity.Migrations.Design.CSharpMigrationCodeGenerator" />
    internal class MyCodeGenerator : CSharpMigrationCodeGenerator
    {
        /// <summary>
        /// Generates the specified alter column operation.
        /// </summary>
        /// <param name="alterColumnOperation">The alter column operation.</param>
        /// <param name="writer">The writer.</param>
        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:

C#
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)