Databases
StackOverflow2013
Postsdbo
POAdvice Please: SQL Server Identity vs Unique Identifier keys when using Entity Framework
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAdvice Please: SQL Server Identity vs Unique Identifier keys when using Entity Framework
    text
    copied!<p>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.</p> <p>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.)</p> <p>So here's the problem:</p> <p>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.</p> <p>However, it turns out that the current version of Entity Framework has a <a href="http://blogs.msdn.com/dsimmons/pages/ef-faq-entity-services.aspx#Section_17" rel="nofollow noreferrer">very strange limitation</a>: 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.</p> <p>So here's the debate:</p> <ol> <li>abandon Entity Framework and use another ORM: <ul> <li>use NHibernate and give up LiNQ support</li> <li>use linq2sql and give up future support (not to mention get bound to SQL Server on DB)</li> </ul></li> <li>abandon GUIDs and go with another PK strategy</li> <li>devise a method to generate sequential GUIDs (COMBs?) at the application layer</li> </ol> <p>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.</p> <p>Any insight or opinion would be greatly appreciated.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload