请注意：使用实体框架时，SQL Server身份与唯一标识符密钥 [英] Advice Please: SQL Server Identity vs Unique Identifier keys when using Entity Framework

问题描述

我正在设计一个相当复杂的系统。我们的主要关注之一是支持SQL Server对等复制。这个想法是支持几个地理上分开的节点。

第二个问题是在中间层使用了现代的ORM。我们的第一选择一直是实体框架，主要是因为开发人员喜欢使用它。 （他们喜欢LiNQ支持。）

所以这里是问题：

通过对等复制记住，我为每个表的主键使用了uniqueidentifier，默认值为newsequentialid（）。这似乎在避免关键冲突和减少索引碎片之间提供了一个很好的平衡。

但是，事实证明，当前版本的Entity Framework有一个非常奇怪的限制：如果一个实体的关键列是一个唯一的标识符（ GUID），则无法将其配置为使用数据库提供的默认值（newsequentialid（））。应用层必须生成GUID并填充密钥值。

所以这里是辩论：

1. 放弃实体框架并使用另一个ORM：
   
   
   
   
   • 使用NHibernate并放弃LiNQ支持
   
   
   • 使用linq2sql并放弃将来的支持（更不用说绑定到DB上的SQL Server）
   
   


2. 放弃GUID， / li>
   
3. 设计一个在应用程序层生成顺序GUID（COMBs？）的方法

使用linq2sql（我的开发人员真的喜欢linq2 [东西]）和3，倾向于选项1。这主要是因为我对某些支持我们目标的复制方案的替代关键策略感到无知，同时也保持开发人员

任何见解或意见将不胜感激。

解决方案

另一个选项（不可用这是发布）是升级到EF 4，它支持服务器生成的GUID。

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:

1. 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)
2. abandon GUIDs and go with another PK strategy
3. 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.

解决方案

Another option (not available when this was posted) is to upgrade to EF 4, which supports server-generated GUIDs.

这篇关于请注意：使用实体框架时，SQL Server身份与唯一标识符密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！