Entity Framework Code First Using Guid as Identity with another Identity Column

Question

a.k.a How can we create multiple identity columns in Code First?

Because of clustering performance, a common recommendation is to use an autoincremented integer column instead of a GUID created with newid().

In order to declare a column as autoincrement, you have to specify it with the Annotation [DatabaseGenerated(DatabaseGeneratedOption.Identity)].

But, you can only have one identity in a table.

So starting with a base model like:

public abstract class ModelBase {
    // the primary key
    public virtual Guid Id { get; set; }

    // a unique autoincrementing key
    public virtual int ClusterId { get; set; }
}

how do we set it up so that:

1. Guid is automatically generated by the database, not code
2. ClusterId is autoincremented
3. Entity Framework Code First doesn't throw all sorts of errors like:
   • Modifications to tables where a primary key column has property 'StoreGeneratedPattern' set to 'Computed' are not supported. Use 'Identity' pattern instead.

---

FYI, if you do want to automatically generate it in code, you could skip the annotation on the Id field and do something like:

public abstract class AbstractContext : DbContext {

  /// <summary>
  /// Custom processing when saving entities in changetracker
  /// </summary>
  /// <returns></returns>
  public override int SaveChanges()
  {
      // recommended to explicitly set New Guid for appropriate entities -- http://msdn.microsoft.com/en-us/library/dd283139.aspx
      foreach (var entry in ChangeTracker.Entries<ModelBase>().Where(e => e.State == EntityState.Added) ) {

          // only generate if property isn't identity...
          Type t = entry.Entity.GetType();
          var info = t.GetProperty("Id").GetCustomAttributes(
              typeof(DatabaseGeneratedAttribute), true).Cast<DatabaseGeneratedAttribute>().Single();

          if (info.DatabaseGeneratedOption != DatabaseGeneratedOption.Identity) {
              entry.Entity.Id = Guid.NewGuid(); // now we make it
          }
      }
      return base.SaveChanges();
  }

}

Solution

This ended up working for me, Entity Framework 5.

1. Turn off automatic migrations
2. Migrate to create the initial table, no frills

3. Declare the ClusterId as Identity (annotation)

   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public override int ClusterId { get; set; }
   

4. Migrate

5. Declare the pk property Id as Identity after the other one has been updated

   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public override Guid Id { get; set; }
   

   • bonus: EF seems to assume Id is primary key, so you don't need [Key, Required]

6. Create the migration code like add-migration TrickEfIntoAutogeneratingMultipleColumns

7. In the Up() method, in the AlterColumn statement, tell the database to autogenerate the GUID by declaring the defaultSqlValue
   • AlterColumn(theTable, "Id", c => c.Guid(nullable: false, identity: true, defaultValueSql: "newid()"));
8. Migrate

This seems to "trick" EF, in the sense that it assumes both columns are identities and reacts accordingly. During migration, it tries to make another column an identity, but seemingly doesn't care when that silently fails -- you end up with one marked as Identity and the other with a default value.

During normal code operation, when EF goes through the SaveChanges/ChangeTracking steps, because it sees the Id property as an Identity it does it's whole "assign temporary key" thing, so that it's not trying to use the default 0000000... value, and instead lets the database generate it using the default value function you specified.

(I would have thought annotating this field as Computed would have accomplished the same thing, but...the errors I mentioned in the question...boo...)

And, because the ClusterId field is also an Identity in code, and really is an Identity in the database, it autoincrements as well.

Comments

[user510101]

should you use the SQL function newsequentialid() instead of newid(), for your PK in the database? It may help with indexes (less fragmentation), and will improve INSERTs.

[drzaus]

@ThiagoSilva maybe, but that depends on how you're planning on using the data -- if you're using a guid to help obfuscate (i.e. make it harder to guess) then newsequentialid wouldn't be right. it's good to point it out, and i'm not saying you're incorrect but it's not actually relevant to the q/a. for a little more reading - stackoverflow.com/questions/1587185/…

[Allan]

I might be wrong here, the fragmentation issue is only an issue if you leave the Guid Primary Key as the clustered index. It would make more sense to change the clustered index to your business key or something that made more sense (e.g. Order Date, Surname, etc)