Advice Please: SQL Server Identity vs Unique Identifier keys when using Entity Framework
- by c.batt
I'm in the process of designing a fairly complex system. One of our primary concerns is supporting SQL Server peer-to-peer replication. The idea is to support several geographically separated nodes.
A secondary concern has been using a modern ORM in the middle tier. Our first choice has always been Entity Framework, mainly because the developers like to work with it. (They love the LiNQ support.)
So here's the problem:
With peer-to-peer replication in mind, I settled on using uniqueidentifier with a default value of newsequentialid() for the primary key of every table. This seemed to provide a good balance between avoiding key collisions and reducing index fragmentation.
However, it turns out that the current version of Entity Framework has a very strange limitation: if an entity's key column is a uniqueidentifier (GUID) then it cannot be configured to use the default value (newsequentialid()) provided by the database. The application layer must generate the GUID and populate the key value.
So here's the debate:
abandon Entity Framework and use another ORM:
use NHibernate and give up LiNQ support
use linq2sql and give up future support (not to mention get bound to SQL Server on DB)
abandon GUIDs and go with another PK strategy
devise a method to generate sequential GUIDs (COMBs?) at the application layer
I'm leaning towards option 1 with linq2sql (my developers really like linq2[stuff]) and 3. That's mainly because I'm somewhat ignorant of alternate key strategies that support the replication scheme we're aiming for while also keeping things sane from a developer's perspective.
Any insight or opinion would be greatly appreciated.
