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

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 2

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 3

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