Entity Framework 6 和 SQL Server 序列

Entity Framework 6 and SQL Server Sequences(Entity Framework 6 和 SQL Server 序列)

问题描述

我将 EF6 与数据库优先项目一起使用.我们需要使用序列，这是 SQL Server 2012 中引入的一项功能(我相信).

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.

我的问题是在 Entity Framework 中使用它，我用谷歌搜索但找不到太多关于 EF6 是否支持它们的信息.我尝试将 EFdesigner 中的 StoreGeneratedPatttern 设置为 Identity 但保存时抱怨零行受到影响，因为它正在使用 scope_identity 查看插入是否成功，但由于我们使用的是序列，所以它返回为 null.

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.

将其设置为 computed 会引发错误，提示我应该将其设置为标识并将其设置为 none 会导致它插入 0 作为 id 值并失败.

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.

我是否需要调用函数/过程来获取下一个序列，然后在保存记录之前将其分配给 id 值?

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?

非常感谢任何帮助.

推荐答案

很明显，你无法通过玩 DatabaseGeneratedOptions 来逃避这个 catch-22.

It's clear that you can't escape from this catch-22 by playing with DatabaseGeneratedOptions.

正如您所建议的，最好的选择是设置 DatabaseGeneratedOption.None 并从序列中获取下一个值(例如，如 this question) 就在您保存新记录之前.然后将其分配给 Id 值，并保存.这是并发安全的，因为您将是唯一一个从序列中绘制特定值的人(假设没有人重置序列).

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 引入了命令拦截器 API.它允许您在执行命令之前和之后操作 EF 的 SQL 命令及其结果.当然我们不应该篡改这些命令，不是吗?

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?

嗯...如果我们查看在设置 DatabaseGeneratedOption.Identity 时执行的插入命令，我们会看到如下内容:

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()

SELECT 命令用于从数据库中获取生成的主键值，并将新对象的标识属性设置为该值.这使 EF 可以在后续插入语句中使用此值，这些语句通过同一事务中的外键引用此新对象.

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.

当默认生成主键时，从序列中获取其值(如您所做的那样)，显然没有 scope_identity().然而，序列有一个当前值，可以通过类似的命令找到它

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'

如果我们能让 EF 在插入之后执行这个命令而不是 scope_identity()！

If only we could make EF execute this command after the insert instead of scope_identity()!

好吧，我们可以.

首先，我们要创建一个实现IDbCommandInterceptor的类，或者继承自默认实现DbCommandInterceptor:

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());

ReaderExecuting 命令在 command 执行之前运行.如果这是一个带有标识列的 INSERT 命令，则其文本类似于上面的命令.现在我们可以将 scope_identity() 部分替换为获取当前序列值的查询:

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')");

现在命令看起来像

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 值.

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.

我真的不认为这是生产就绪的.当它是一个插入命令时，您必须修改该命令，根据插入的实体选择正确的序列，所有这些都通过在一个相当模糊的地方进行脏字符串操作.而且我不知道在高并发的情况下你是否总能得到正确的序列值.但谁知道呢，也许下一个版本的 EF 会开箱即用地支持这一点.

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.

这篇关于Entity Framework 6 和 SQL Server 序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！