Wednesday, July 5, 2017

NEWSEQUENTIALID() with Entity Framework and SQL Azure

I was creating on a table in my database on SQL Azure which had a GUID column as the primary key. I wanted to use NEWSEQUENTIALID() instead of NEWID() as the default value for the column because that would generate a sequentially incrementing GUID instead of a random GUID and have better performance.
To do this in entity framework, I added [DatabaseGenerated(DatabaseGeneratedOption.Identity)] attribute on the on column.
I was also using code first migrations. To know more about code first migrations, see https://msdn.microsoft.com/en-us/data/jj591621.aspx
This generates this script
c.Guid(nullable: false, identity: true)
Now when the database would be created in SQL Azure, the column values were still random instead of being sequentially incrementing. But if the same database was created locally on SQL server, the values were sequentially incrementing.
The fix was to change the script to

c.Guid(nullable: false, identity: true, defaultValueSql: "newsequentialid()")