Click here to Skip to main content
15,919,479 members
Articles / Database Development / SQL Server

EF6 Migrations and TimeStamp Bug with CodeFirst

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
2 Nov 2021CPOL2 min read 6.2K   1   4
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.


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.

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!


The ModificationDate property in the baseclass is setup as:

/// <summary>
/// Gets or sets the modification date.
/// </summary>
/// <value>
/// The modification date.
/// </value>
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
    /// <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);
                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!


  • 2nd November, 2021: Init v0.1: First write about this issue


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

Written By
Software Developer (Senior)
Netherlands Netherlands
I started in the eightees with Basic and QuickBasic. When the ninetees started dBase was my thing. Close to the millenium years I swapped to C++ and since 2003 it is only programming in c#.

There is one thing about me.
Not that kind of OO guy and definately not an ORM-fan. I know a database to well to give the importance of data out in the hands of a tool.

Comments and Discussions

QuestionMaybe generalizing? Pin
Master DJon21-Apr-22 11:53
Master DJon21-Apr-22 11:53 
AnswerRe: Maybe generalizing? Pin
Herman<T>.Instance21-Apr-22 23:30
Herman<T>.Instance21-Apr-22 23:30 
SuggestionRe: Maybe generalizing? Pin
Master DJon22-Apr-22 8:31
Master DJon22-Apr-22 8:31 
Ideas on how to generalize:

1- Don't use the column name, but attribute TimeStamp on the class property.
2- Using my MigrationsSqlGenerator, add a method call in Generate that would:
2.1- Detect AlterColumnOperation operations that the property underneath has an attribute TimeStamp
2.2- Remove those
2.3- Replace by the two operations: DropColumnOperation and AddColumnOperation

If you would like to try it and, would be so great, add it to the project I talked to you about, fork the project, create a branch, do the modifications and make a pull request.
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA2-Nov-21 10:18
professionalȘtefan-Mihai MOGA2-Nov-21 10:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.