Error in CodeFirst Seed with migrations : Modifying a column with the 'Identity' pattern is not supported. Column: 'CreatedAt'.

Question

I have activated migrations on my Azure Mobile Services project. I filled the new seed function Inside the Configuration.cs class of the migrations. If the tables are empty, the seed function is going without any problems. When my AddorUpdate tries to update the first object I get the error in the inner exception : "Modifying a column with the 'Identity' pattern is not supported. Column: 'CreatedAt'. Table: 'CodeFirstDatabaseSchema.Category'."

Part of my code is as follows:

context.categories.AddOrUpdate(
            new Category { Id="1", Code="GEN", Text="General"},
            new Category { Id="2", Code="POL", Text="Politics"},
            new Category { Id="3", Code="FAS", Text="Fashion"},
            new Category { Id="4", Code="PEO", Text="People"},
            new Category { Id="5", Code="TEC", Text="Technology"},
            new Category { Id="6", Code="SPO", Text="Sport"},
            new Category { Id="7", Code="LIV", Text="Living"}
        );

Answer 1

SAME PROBLEM AND SOLUTION We had the same problem and it was a trigger in another table that had impact on the table that we had the problem on.

DETAILS OF OUR DEVELOPMENT We developed a Xamarin App connected to an Azure Web Service. When we used the method PushAsync from iMobileServices, it gave us the error: Modifying a column with the 'Identity' pattern is not supported. Column: 'CreatedAt'.

It was strange for us as some tables did not have the problem with the web service

REASON It seems that the trigger update got in conflict with the pushasync from the mobile device.

We disabled the trigger, switched the responsibility to the front end and it worked fine. At least for us.

We hope this soltion helps someone.

Answer 2

Here's my generic implementation of Nikatlas' solution.

Short version of the answer: You can't modify CreatedAt with a null value, so you can use this function instead:

    private void AddOrUpdatePreservingCreatedAt<T> (DbSet<T> set, T item) where T : EntityData
    {        
        var existing = set.Where(i => i.Id == item.Id).FirstOrDefault();
        if (existing != null)
        {
            item.CreatedAt = existing.CreatedAt;             
        }
        set.AddOrUpdate(i => i.Id, item);
    }

Call it like this

AddOrUpdatePreservingCreatedAt(context.YourItems, itemToBeUpdatedOrAdded);

Answer 3

It seems i have found a solution to this problem.

The reason this error occurs is because of the AddOrUpdate Method. As stated in this post : http://thedatafarm.com/data-access/take-care-with-ef-4-3-addorupdate-method/

More importantly, if a match is found then the update will update all and null out any that weren’t in your AddOrUpdate.

What this means is that after the first seed, whenever your code runs it tries to update your entities correctly but it tries to pass the value null on CreatedAt field. If you look at EntityData class the CreateAt field has these attributes :

[DatabaseGenerated(DatabaseGeneratedOption.Identity)] //Here they mark this as IDENTITY        
[Index(IsClustered = true)] // Cluster index. i really dont know why ?         
[TableColumn(TableColumnType.CreatedAt)]         
public DateTimeOffset? CreatedAt { get; set; }

So the error occurs beacause you try to modify the CreatedAt column.

My solution was to Create a List, lookup to set CreatedAt to correct value and then addOrUpdate:

 // Create List       
 List<Permission> permissions = new List<Permission>(new Permission[]{             
   new Permission { Id = "ID1" , Name = "Send SMS"},           
   new Permission { Id = "ID2", Name = "Send Email"}            });
   // Iterate through list to set CreatedAt to correct value 
   foreach (Permission p in permissions){
      // Get the record from the db if it exists              
      var t = context.PermissionSet.Where(s => s.Id == p.Id).FirstOrDefault();
      if (t != null){
         p.CreatedAt = t.CreatedAt; //SET CreatedAt to correct Value if the record already exists                
      }
      context.PermissionSet.AddOrUpdate(a => a.Id, p); // NOW I CAN UPDATE WITH NO PROBLEM
   } 

Hope this helps. :)

Answer 4

If you have a integer column named Id, then Entity Framework will assume that is the primary key and that it is database generated - so it is created as an IDENTITY column in the database.

You cannot specify the Id for IDENTITY columns, so you stop doing that by removing the Id = 1, Id = 2, etc

I am a bit thrown by the fact that the column you have a problem with is named "CreatedAt". It sounds like it should be a DateTime and might also be database generated, but it surely shouldn't be IDENTITY?

Anyways, the usage you probably want is the one where you specify the natural key of the entity, so that EF can identify any records that already exist. So, if CODE is the natural key then you should be writing the Seed like this:

context.categories.AddOrUpdate(
   x => x.Code,//the natural key is Code    
   new Category { Code="GEN", Text="General"},
   new Category { Code="POL", Text="Politics"},
   new Category { Code="FAS", Text="Fashion"},
   new Category { Code="PEO", Text="People"},
   new Category { Code="TEC", Text="Technology"},
   new Category { Code="SPO", Text="Sport"},
   new Category { Code="LIV", Text="Living"}

);

Reference: Take care with the AddOrUpdate method

Answer 5

This question, its answers and comments might help you a bit, but not much.

You can do Inserts on identity column using the solutions provided by the question. But you cannot update values of Identity Column. The only way to do updates on identity column is to mark it as not being identity. Probably by adding manual migrations.

This SO question and its answers may also be helpful.

Read here also on general MSSQL Server constraints on Updating Identity Column.