EF使用Identity列对每个插入进行选择 [英] EF performing a select on every insert with Identity column

问题描述

我注意到，当我使用EF插入时，它执行选择以查找下一个PK。

我有一个PK字段，具有标识集和自动增量启用

这是查询

  SELECT [ackId] 
 FROM [dbo]。[Acks] 
 WHERE @@ ROWCOUNT> 0 AND [ackId] = scope_identity（）
  

我碰巧注意到它是在SQL Manger Studio中的最近的昂贵查询列表。找到PK的查询比实际插入的代价更贵吗？

这是正常的行为吗？或者是由实体框架导致的这种行为？

另一个可以想到的问题。如果EF正在选择获取该值，如果有多个连接写入数据库，会发生什么？那么select可以返回相同的值吗？

解决方案

这是一个常见的模式， 支持数据库生成的身份密钥的ORM。身份是实体的一个关键概念。例如，具有相同名称的两个客户端仍然是两个不同的客户端。一个代理键，如 ClientId 是唯一的方法来告诉他们。

ORM需要知道这个代理键通过直接查询 scope_identity（），数据库中的值和插入数据时明确获取明确方式的唯一方法。

这不会导致竞争条件，因为当插入发生时（始终不会回滚），标识列总是递增，而 scope_identity（）总是返回在 INSERT 语句范围内生成的身份值。

唯一的摆脱这种昂贵模式的方法是在代码中生成键值，并将主键属性设置为 DatabaseGeneratedOption.None 。但是，生成和插入不带并发问题的唯一主键值并不是微不足道的。

我想这是你必须要做的事情。 ORM从来不打算做批量插入，还有其他的方式来做这些。

I have noticed that when I insert with EF, it performs a select to find the next PK.

I have a PK field with Identity set and auto-increment enabled.

Here is the Query

SELECT [ackId]
FROM [dbo].[Acks]
WHERE @@ROWCOUNT > 0 AND [ackId] = scope_identity()

I happened to notice it as it was at the top of the Recent Expensive Queries List in SQL Manger Studio. It doesn't quite make sense that the query to find the PK is more expensive than the actual insert?

Is this normal behaviour? Or is this behaviour causef by entity framework?

Another issue I can think of. If EF is doing a select to get the value, what happens if there are several connections writing to the db? Can there not be a case when the select returns the same value?

解决方案

This is a common pattern found in every ORM that supports database-generated identity keys. Identity is a key concept of entities. For example, two clients with the same name are still two distinct clients. A surrogate key like ClientId is the only way to tell them apart.

An ORM needs to know this surrogate key value in the database and the only way to get it unambiguously when inserting data is by querying scope_identity() directly.

This never causes race conditions, because an identity column is always incremented when an insert happens (it never rolls back) and scope_identity() always returns the identity value that's generated within the scope of the INSERT statement.

The only way to get rid of this expensive pattern is to generate key values in code and set the primary key property to DatabaseGeneratedOption.None. But generating and inserting unique primary key values without concurrency problems is not trivial.

I guess it's something you have to live with. ORMs were never meant to do bulk inserts, there are other ways to do these.

这篇关于EF使用Identity列对每个插入进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！