C# LINQ to SQL 插入顺序 GUID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/753003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LINQ to SQL Insert Sequential GUID
提问by Refracted Paladin
I have a database that is part of a Merge Replication scheme that has a GUID as it's PK. Specifically the Data Type is uniqueidentifier, Default Value (newsequentialid()), RowGUID is set to Yes. When I do a InsertOnSubmit(CaseNote) I thought I would be able to leave CaseNoteID alone and the database would input the next Sequential GUID like it does if you manually enter a new row in MSSMS. Instead it sends 00000000-0000-0000-0000-000000000000. If I add CaseNoteID = Guid.NewGuid(),the I get a GUID but not a Sequential one (I'm pretty sure).
我有一个数据库，它是合并复制方案的一部分，它有一个 GUID，因为它是 PK。具体来说，数据类型是uniqueidentifier，默认值(newsequentialid ())， RowGUID 设置为Yes。当我执行 InsertOnSubmit(CaseNote) 时，我认为我可以单独保留 CaseNoteID，数据库将输入下一个 Sequential GUID，就像在 MSSMS 中手动输入新行一样。相反，它发送00000000-0000-0000-0000-000000000000。如果我添加CaseNoteID = Guid.NewGuid(),，我会得到一个 GUID 但不是顺序的（我很确定）。
Is there a way to let SQL create the next sequential id on a LINQ InsertOnSubmit()?
有没有办法让 SQL 在 LINQ InsertOnSubmit() 上创建下一个顺序 ID？
For reference below is the code I am using to insert a new record into the database.
下面是我用来将新记录插入数据库的代码，供参考。
CaseNote caseNote = new CaseNote
{
CaseNoteID = Guid.NewGuid(),
TimeSpentUnits = Convert.ToDecimal(tbxTimeSpentUnits.Text),
IsCaseLog = chkIsCaseLog.Checked,
ContactDate = Convert.ToDateTime(datContactDate.Text),
ContactDetails = memContactDetails.Text
};
caseNotesDB.CaseNotes.InsertOnSubmit(caseNote);
caseNotesDB.SubmitChanges();
Based on one of the suggestions below I enabled the Autogenerated in LINQ for that column and now I get the following error --> The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.Ideas?
根据以下建议之一，我为该列启用了 LINQ 中的自动生成，现在出现以下错误 -->如果 DML 语句的目标表包含没有 INTO 子句的 OUTPUT 子句，则该语句不能有任何已启用的触发器。想法？
采纳答案by Keltex
In the Linq to Sql designer, set the Auto Generated Value property to true for that column.
在 Linq to Sql 设计器中，将该列的 Auto Generated Value 属性设置为 true。
This is equivalent to the IsDbGenerated propertyfor a column. The only limitation is that you can't update the value using Linq.
这等效于列的IsDbGenerated 属性。唯一的限制是您不能使用 Linq 更新该值。
回答by Michael Meadows
From the top of the "Related" box on the right:
从右侧“相关”框的顶部：
Sequential GUID in Linq-to-Sql?
If you really want the "next" value, use an int64 instead of GUID. COMB guid will ensure that the GUIDs are ordered.
如果您真的想要“下一个”值，请使用 int64 而不是 GUID。COMB guid 将确保 GUID 是有序的。
回答by Michael Meadows
In regards to your "The target table of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause", check out this MS KB article, it appears to be a bug in LINQ:
关于您的“如果语句包含没有 INTO 子句的 OUTPUT 子句，则 DML 语句的目标表不能有任何已启用的触发器”，请查看此 MS KB 文章，它似乎是 LINQ 中的错误：
回答by Nicholas
There is a bug in Linq2Sql when using an auto-generated (guid/sequential guid) primary key and having a trigger on the table.. that is what is causing your error. There is a hotfix for the problem:
当使用自动生成的（guid/sequential guid）主键并在表上有触发器时，Linq2Sql 中存在一个错误。这就是导致您出错的原因。该问题有一个修补程序：
http://support.microsoft.com/default.aspx?scid=kb;en-us;961073&sd=rss&spid=2855
http://support.microsoft.com/default.aspx?scid=kb;en-us;961073&sd=rss&spid=2855
回答by C Tierney
You really needed to do a couple of things.
你真的需要做几件事。
- Remove any assignment to the GUID type property
- Change the column to autogenerated
- Create a constraint in the database to default the column to NEWSEQUENTIALID()
- Do insert on submit just like you were before.
- 删除对 GUID 类型属性的任何分配
- 将列更改为自动生成
- 在数据库中创建约束，将列默认为 NEWSEQUENTIALID()
- 像以前一样在提交时插入。
On the insert into the table the ID will be created and will be sequential. Performance comparison of NEWSEQUENTIALID() vs. other methods
在插入到表中时，ID 将被创建并且是连续的。 NEWSEQUENTIALID() 与其他方法的性能比较
回答by C Tierney
Masstransit uses a combguid :
Masstransit 使用 combguid ：
https://github.com/MassTransit/MassTransit/blob/master/src/MassTransit/NewId/NewId.cs
https://github.com/MassTransit/MassTransit/blob/master/src/MassTransit/NewId/NewId.cs
is this what you're looking for?
这是你要找的吗？
from wikipedia:
来自维基百科：
Sequential algorithms
GUIDs are commonly used as the primary key of database tables, and with that, often the table has a clustered index on that attribute. This presents a performance issue when inserting records because a fully random GUID means the record may need to be inserted anywhere within the table rather than merely appended near the end of it. As a way of mitigating this issue while still providing enough randomness to effectively prevent duplicate number collisions, several algorithms have been used to generate sequential GUIDs. The first technique, described by Jimmy Nilsson in August 2002[7] and referred to as a "COMB" ("combined guid/timestamp"), replaces the last 6 bytes of Data4 with the least-significant 6 bytes of the current system date/time. While this can result in GUIDs that are generated out of order within the same fraction of a second, his tests showed this had little real-world impact on insertion. One side effect of this approach is that the date and time of insertion can be easily extracted from the value later, if desired. Starting with Microsoft SQL Server version 2005, Microsoft added a function to the Transact-SQL language called NEWSEQUENTIALID(),[8] which generates GUIDs that are guaranteed to increase in value, but may start with a lower number (still guaranteed unique) when the server restarts. This reduces the number of database table pages where insertions can occur, but does not guarantee that the values will always increase in value. The values returned by this function can be easily predicted, so this algorithm is not well-suited for generating obscure numbers for security or hashing purposes. In 2006, a programmer found that the SYS_GUID function provided by Oracle was returning sequential GUIDs on some platforms, but this appears to be a bug rather than a feature.[9]
顺序算法
GUID 通常用作数据库表的主键，因此，该表通常在该属性上具有聚集索引。这会在插入记录时出现性能问题，因为完全随机的 GUID 意味着可能需要将记录插入表中的任何位置，而不仅仅是附加在表的末尾附近。作为缓解此问题同时仍提供足够的随机性以有效防止重复数字冲突的一种方法，已使用多种算法来生成顺序 GUID。第一种技术，由 Jimmy Nilsson 在 2002 年 8 月 [7] 描述并称为“COMB”（“组合 guid/时间戳”），用当前系统日期的最低有效 6 个字节替换 Data4 的最后 6 个字节/时间。虽然这可能导致 GUID 在相同的几分之一秒内无序生成，但他的测试表明这对插入几乎没有实际影响。这种方法的一个副作用是，如果需要，稍后可以轻松地从值中提取插入的日期和时间。从 Microsoft SQL Server 2005 版开始，Microsoft 向 Transact-SQL 语言添加了一个名为 NEWSEQUENTIALID(),[8] 的函数，该函数生成的 GUID 保证会增加值，但在以下情况下可能以较低的数字开头（仍保证唯一）服务器重新启动。这减少了可能发生插入的数据库表页的数量，但并不能保证值的值总是增加。这个函数返回的值可以很容易地预测，所以这个算法不太适合为安全或散列目的生成晦涩的数字。2006 年，一位程序员发现 Oracle 提供的 SYS_GUID 函数在某些平台上返回顺序 GUID，但这似乎是一个错误而不是一个功能。 [9]
回答by RonaldPaguay
You must handle OnCreated() method
您必须处理 OnCreated() 方法
Partial Class CaseNote
Sub OnCreated()
id = Guid.NewGuid()
End Sub
End Class
