Click here to Skip to main content
15,918,268 members
Articles / Database Development / SQL Server
Tip/Trick

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.

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)


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 
Hi!

I like people finding bugs and fixing them.

This solution is great, but would be greater if it would be generalized. Don't you think?

More over, the Configuration.cs file doesn't exist in my current project that is a console type project. So, again, a way to generalize this part also would be great.

I did myself circumvent another EF bug toward SQLite: Executing SQL Code within EntityFrameworkCore Migration in Correct Order (SQLite)[^].

If you want and can generalize your case, that would be great to add it to this really small project dedicated to EF bugs.

I am currently writing another thing ("much" bigger) to fix another issue from EF that will be added to this project.
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 
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.