实体框架6和SQL Server序列 [英] Entity Framework 6 and SQL Server Sequences
问题描述
在表上，标识列的默认值设置为： / p>
（NEXT VALUE FOR [ExhibitIdentity]）
这是因为我们有两个表可以存储不同部门的展示信息，但是我们需要这两个表格中的标识是唯一的，因为它们被用作很多其他的参考共享共享表。
我的问题是在Entity Framework中使用这个，我已经google了，但是找不到有关EF6是否支持它们的很多信息。我已经尝试在 EFdesigner 中将 StoreGeneratedPatttern 设置为身份，但是保存时抱怨零行被影响，因为它使用 scope_identity 来查看插入是否成功，但是我们使用的序列返回为空。
将其设置为计算的会抛出一个错误，指出我应该将其设置为身份并将其设置为无，使其不能插入0作为id值并失败。
我需要调用函数/过程才能获得下一个序列，然后在保存记录之前将其分配给id值？
任何帮助都非常感激。
很明显，你不能通过玩$ $逃脱这个catch-22 c $ c> DatabaseGeneratedOption s。
如您所建议的，最好的选项是设置 DatabaseGeneratedOption.None 并从序列中获取下一个值（例如，在此问题）。然后将其分配给Id值，并保存。这是并发安全的，因为您将是唯一一个绘制序列中的特定值（我们假设没有人重置序列）。
但是，有一个可能的黑客...
一个坏的，我应该停在这里。 ..
EF 6引入了命令拦截器API 。它允许您在执行命令之前和之后操纵EF的SQL命令及其结果。当然，我们不应该篡改这些命令，我们应该如何？
嗯，如果我们看看当$ DatabaseGeneratedOption .Identity 被设置，我们看到如下：
INSERT [dbo]。[Person]（[Name]）VALUES（@ 0）
SELECT [Id]
FROM [dbo]。[Person]
WHERE @@ ROWCOUNT> 0 AND [Id] = scope_identity（）
SELECT 命令用于从数据库中获取生成的主键值，并将新对象的identity属性设置为此值。这使EF能够在后续的插入语句中使用此值，该语句在同一个事务中通过外键引用此新对象。
当主键由默认生成时从序列中获取其值（如你所知），显然没有 scope_identity（）。然而，有一个序列的当前值，可以通过命令如
SELECT current_value FROM sys.sequences WHERE name ='PersonSequence'
如果只有我们可以使EF执行此命令，插入而不是 scope_identity（）！
嗯，我们可以。
首先，我们必须创建一个实现 IDbCommandInterceptor 的类，或从默认实现继承 DbCommandInterceptor ：
使用System.Data.Entity.Infrastructure。拦截;
class SequenceReadCommandInterceptor：DbCommandInterceptor
{
public override void ReaderExecuting（DbCommand命令
，DbCommandInterceptionContext< DbDataReader> interceptionContext）
{
}
}
我们通过命令将这个类添加到拦截上下文
DbInterception.Add（new SequenceReadCommandInterceptor（））;
ReaderExecuting 命令运行在<$执行c $ c>命令。如果这是具有标识列的 INSERT 命令，其文本看起来像上面的命令。现在我们可以替换 scope_identity（）部分获取当前序列值的查询：
command.CommandText = command.CommandText
.Replace（scope_identity（），
（SELECT current_value FROM sys。序列
WHERE name ='PersonSequence'））;
现在命令将如下
INSERT [dbo]。[Person]（[Name]）VALUES（@ 0）
SELECT [Id]
FROM [dbo]。[Person]
WHERE @@ ROWCOUNT> 0 AND [Id] =
（SELECT current_value FROM sys.sequences
WHERE name ='PersonSequence'）
如果我们运行这个，有趣的是：它有效。在 SaveChanges 命令之后，新对象已收到其持久的Id值。
我真的不认为这是生产就绪的。您必须在插入命令时修改命令，根据插入的实体选择正确的序列，所有这些都是通过脏字符串操作在一个相当模糊的地方。 我不知道如果重并发，你总是会得到正确的顺序值。但是谁知道，也许下一个版本的EF将支持开箱即用。
I am using EF6 with a database first project. We have a requirement to use sequences which was a feature introduced in SQL server 2012 (I believe).
On the table the identity column has a default value set using:
(NEXT VALUE FOR [ExhibitIdentity])
This is used as we have two tables which store exhibit information for separate departments but we need the identity to be unique across both of the tables as it is then used as a reference in lots of other shared common tables.
My problem is using this within the Entity Framework, I have googled but couldn't find much information in relation to whether EF6 supports them. I have tried setting StoreGeneratedPatttern in the EFdesigner to Identity but when saving this complains that zero rows were affected as it is using scope_identity to see if the insert succeeded but as we are using sequences this comes back as null.
Setting it to computed throws an error saying I should set it to identity and setting it to none causes it to insert 0 as the id value and fail.
Do I need to call a function/procedure in order to get the next sequence and then assign it to the id value before saving the record?
Any help is much appreciated.
It's clear that you can't escape from this catch-22 by playing with DatabaseGeneratedOptions.
The best option, as you suggested, is to set DatabaseGeneratedOption.None and get the next value from the sequence (e.g. as in this question) right before you save a new record. Then assign it to the Id value, and save. This is concurrency-safe, because you will be the only one drawing that specific value from the sequence (let's assume no one resets the sequence).
However, there is a possible hack...
A bad one, and I should stop here...
EF 6 introduced the command interceptor API. It allows you to manipulate EF's SQL commands and their results before and after the commands are executed. Of course we should not tamper with these commands, should we?
Well... if we look at an insert command that is executed when DatabaseGeneratedOption.Identity is set, we see something like this:
INSERT [dbo].[Person]([Name]) VALUES (@0)
SELECT [Id]
FROM [dbo].[Person]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
The SELECT command is used to fetch the generated primary key value from the database and set the new object's identity property to this value. This enables EF to use this value in subsequent insert statements that refer to this new object by a foreign key in the same transaction.
When the primary key is generated by a default taking its value from a sequence (as you do) it is evident that there is no scope_identity(). There is however a current value of the sequence, which can be found by a command like
SELECT current_value FROM sys.sequences WHERE name = 'PersonSequence'
If only we could make EF execute this command after the insert instead of scope_identity()!
Well, we can.
First, we have to create a class that implements IDbCommandInterceptor, or inherits from the default implementation DbCommandInterceptor:
using System.Data.Entity.Infrastructure.Interception;
class SequenceReadCommandInterceptor : DbCommandInterceptor
{
public override void ReaderExecuting(DbCommand command
, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
}
}
We add this class to the interception context by the command
DbInterception.Add(new SequenceReadCommandInterceptor());
The ReaderExecuting command runs just before command is executed. If this is an INSERT command with an identity column, its text looks like the command above. Now we could replace the scope_identity() part by the query getting the current sequence value:
command.CommandText = command.CommandText
.Replace("scope_identity()",
"(SELECT current_value FROM sys.sequences
WHERE name = 'PersonSequence')");
Now the command will look like
INSERT [dbo].[Person]([Name]) VALUES (@0)
SELECT [Id]
FROM [dbo].[Person]
WHERE @@ROWCOUNT > 0 AND [Id] =
(SELECT current_value FROM sys.sequences
WHERE name = 'PersonSequence')
And if we run this, the funny thing is: it works. Right after the SaveChanges command the new object has received its persisted Id value.
I really don't think this is production-ready. You'd have to modify the command when it's an insert command, choose the right sequence based on the inserted entity, all by dirty string manipulation in a rather obscure place. And I don't know if with heavy concurrency you will always get the right sequence value back. But who knows, maybe a next version of EF will support this out of the box.
这篇关于实体框架6和SQL Server序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
