实体框架6和SQL Server序列 [英] Entity Framework 6 and SQL Server Sequences
问题描述
我使用EF6与数据库的第一个项目。我们必须使用这是在SQL Server 2012中（我相信）引入的功能序列的要求
在表中的身份列使用默认值设置：
（NEXT VALUE FOR [ExhibitIdentity]）
此用作我们有两个表为独立的部门哪个店铺展览品的信息，但我们所需要的同一性的是在这两个表的独特的，因为它随后在大量的其他用作参考共同的表。
我的问题是实体框架内利用这一点，我用Google搜索，但没有找到有关EF6是否支持他们的信息。我已经尝试设置 StoreGeneratedPatttern 在 EFdesigner 来的身份的，但保存时，这个抱怨为零因为它是使用 SCOPE_IDENTITY 来看看如果插入成功，但由于我们使用序列这回来为空行受到影响。
将其设置为计算的抛出一个错误，说我应该把它设置为身份并将其设置为none使其插入0作为id的值和失败。
我需要打电话，以获得下一个序列，然后将其保存记录之前，分配给的id值的函数/程序？
任何帮助深表感谢。
很显然，你不能用<$打从这22条军规逃生C $ C> DatabaseGeneratedOption 秒。
最好的选择，如你所说，是设置 DatabaseGeneratedOption.None ，并从该序列的下一个值（例如，作为在这个问题）在保存前右新纪录。然后将其分配到的ID值，并保存。这是并发安全，因为你是唯一一个从序列绘图特定值（假设没有一个复位序列）。
不过，有一个可能的黑客...
有一个坏女人，我应该在这里停。 ..
EF 6推出的命令拦截API 的。它允许和命令执行后，然后操纵EF的SQL命令及其结果。当然，我们不应该与这些命令乱动，我们应该？
呃......如果我们看一下在执行插入命令时， DatabaseGeneratedOption .Identity 设置，我们看到的是这样的：
<预类=郎-SQL prettyprint-覆盖 >
INSERT [DBO]。[人]（[名称]）VALUES（@ 0）
选择[ID]
从[DBO]。[人]
WHERE @@ ROWCOUNT> 0 AND [ID] = SCOPE_IDENTITY（）
的
SELECT 命令用于从数据库读取所产生的主键值与新的对象的身份属性设置为这个值。这使EF使用中，通过在同一事务外键参考这个新的对象随后的INSERT语句，此值为
当被默认生成的主键从序列采取它的值（你这样）很显然，没有 SCOPE_IDENTITY（）。然而，有序列的当前值，可以通过如下命令
<预类=郎-SQL prettyprint-覆盖>
SELECT current_value FROM sys.sequences其中name ='PersonSequence'
如果我们能做出EF后执行此命令插入而不是 SCOPE_IDENTITY（）！
好了，就可以了。
首先，我们必须创建一个类实现 IDbCommandInterceptor ，或从默认实现继承 DbCommandInterceptor ：
<预类=郎CSHARP prettyprint-覆盖>
使用System.Data.Entity.Infrastructure。拦截;
类SequenceReadCommandInterceptor：DbCommandInterceptor
{
公共覆盖无效ReaderExecuting（的DbCommand命令
，DbCommandInterceptionContext< DbDataReader> interceptionContext）
{
}
}
我们的命令添加这个类来拦截的上下文
<预类=郎CSHARP prettyprint-覆盖>
DbInterception.Add（新SequenceReadCommandInterceptor（））;
在刚<$的 ReaderExecuting 命令运行C $ C>命令被执行。如果这是一个标识列的插入命令，它的文本看起来像上面的命令。现在，我们的可能的替换 SCOPE_IDENTITY（）通过查询获取当前的序列值的部分：
<预类=郎CSHARP prettyprint-覆盖>
command.CommandText = command.CommandText
.Replace（SCOPE_IDENTITY（），
（SELECT current_value FROM SYS。序列
WHERE name ='PersonSequence'））;
现在命令看起来像
<预类=郎-SQL prettyprint-覆盖>
INSERT [DBO]。[人]（[名称]）VALUES（@ 0）
选择[ID]
FROM [DBO] [人]
，其中@@ 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屋！
