将其保存在实体框架中之前是否可以获得标识字段值 [英] Is it possible to get Identity Field value before saving it in entity framework

问题描述

我有一个客户和销售表

CUSTOMER
--------------
Id (int auto increment)
Name

SALES
---------------
Id (int auto increment)
CustomerId (int)
OrderTotal (decimal)

有了Guid，我可以做到这一点.

With Guid i can do this.

dbTransaction = dbContext.Database.BeginTransaction(isolationLevel);

var customer = new Customer()
{
  Id = Guid.NewGuid(),
  Name = "John Doe"
};

var sales = new Sales() 
{
  Id = Guid.NewGuid(),
  CustomerId = customer.Id,
  OrderTotal = 500
};

dbContext.SaveChanges();
dbTransaction.Commit();

如果我的主键是int(使用DatabaseGeneratedOption.Identity)，该怎么办?

How can i do this if my primary key is int (with DatabaseGeneratedOption.Identity)?

推荐答案

您不能. IDENTITY列中的ID是由数据库在插入时生成的，所有技巧"来规避该ID和自己确定ID可能都是有缺陷的.

You cannot. The ID that goes into a IDENTITY column is generated by the database upon insertion, and all "tricks" to circumvent that and determine the ID yourself are probably flawed.

简短的回答::如果要在保存之前生成ID时要说一句，请使用GUID(UNIQUEIDENTIFIER)或SEQUENCE(如果要使用SQL Server 2012，请使用UNIQUEIDENTIFIER)或更高版本).

Short answer: If you want some say in generating an ID before you save, use a GUID (UNIQUEIDENTIFIER), or a SEQUENCE (if you're working with SQL Server 2012 or newer).

甚至不考虑运行诸如context.Customers.Max(c => c.Id) + 1之类的查询作为可行的解决方案，因为您总是有并发数据库访问的可能性:在您访问数据库之后，另一个进程或线程可能会将新实体持久化到同一表中在存储实体之前，请先阅读下一个免费" ID.除非获得ID，对其进行某些处理以及使用ID存储实体的整个操作是原子的，否则计算下一个空闲ID容易产生冲突.这可能需要在数据库中使用表锁，这可能效率不高.

Don't even consider running a query such as context.Customers.Max(c => c.Id) + 1 as a viable solution, because there's always the possibility that you have concurrent database accesses: another process or thread might persist a new entity to the same table after you've read the next "free" ID but before you store your entity. Computing the next free ID will be prone to collisions, unless your whole operation of getting the ID, doing something with it, and storing the entity with that ID were atomic. This would likely require a table lock in the DB, which might be inefficient.

(即使您使用SQL Server 2012中引入的新功能SEQUENCE，也存在相同的问题.)(

(The same problem exists even when you use SEQUENCEs, a new feature introduced in SQL Server 2012.) (I was wrong; see end of answer.)

1. 如果在保存之前需要确定对象的ID，请不要使用IDENTITY列中的ID. 使用GUID ，因为您极不可能与它们发生任何冲突.

1. If you need to determine the ID of an object before you save it, then don't use the ID that goes in a IDENTITY column. Stay with a GUID, because you're extremely unlikely to get any collision with these.

没有必要在一个或另一个之间进行选择:您实际上可以吃蛋糕并将其吃掉！没有任何东西会阻止您拥有两个ID列，一个是在外部确定的(GUID)，另一个是在数据库内部的(IDENTITY列)；请参阅博客文章"CQS与服务器生成的ID"马克·塞曼(Mark Seemann)的详细介绍了这个想法.这是示例的总体思路:

There's no need to chose between one or the other: you can actually have your cake and eat it! Nothing stops you from having two ID columns, one that you determine externally (the GUID) and one that stays internal to the DB (the IDENTITY column); see the blog article "CQS vs. server generated IDs" by Mark Seemann for a more detailed look at this idea. Here's the general idea by example:

CREATE TABLE Foos
(
    FooId INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
 -- ^^^^^ assigned by the DBMS upon insertion. Mostly for DB-internal use.
    Id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT (NEWID()),
 -- ^^ can be dictated and seen by the users of your DB. Mostly for DB-external use.
    …
);

CREATE TABLE FooBars
(
    FooId INT NOT NULL FOREIGN KEY REFERENCES Foos (FooId),
 --   use DB-internal ID in foreign key constraints ^^^^^
    …
);

CREATE VIEW PublicFoos AS
SELECT Id, … FROM Foos;
--     ^^ publish the public ID for users of your DB

(请确保遵循一些约定来一致地命名内部和公共ID字段名称.)

(Make sure you adhere to some convention for consistently naming internal and public ID field names.)

SEQUENCE s 是代替IDENTITY列的一种可能的替代方法.它们会自动增加，并且使用 NEXT VALUE FOR SomeSequence .在MSDN上 提到的一种使用案例是:

SEQUENCEs, a feature introduced in SQL Server 2012, are a possible alternative to having an IDENTITY column. They are automatically increased and you are guaranteed a unique number when getting the next free ID using NEXT VALUE FOR SomeSequence. One of the use cases mentioned on MSDN are:

在以下情况下，请使用序列而不是标识列:[…]在插入表之前，应用程序需要一个数字.

Use sequences instead of identity columns in the following scenarios: […] The application requires a number before the insert into the table is made.

一些警告:

• 获取下一个序列值将需要额外的数据库往返.

• Getting the next sequence value will require an additional roundtrip to the database.

像标识列一样，可以重置/重新播种序列，因此存在ID冲突的理论可能性.如果可以的话，最好从不重新播种标识列和序列.

Like identity columns, sequences can be reset / re-seeded, so there is the theoretical possibility of ID collisions. Best to never re-seed identity columns and sequences if you can help it.

如果使用NEXT VALUE FOR获取下一个自由序列值，然后决定不使用它，则ID中将出现空白".显然，常规(非顺序)GUID不会发生间隙，因为它们没有固有的顺序.

If you fetch the next free sequence value using NEXT VALUE FOR, but then decide not to use it, this will result in a "gap" in your IDs. Gaps obviously cannot happen with regular (non-sequential) GUIDs because there is no inherent ordering to them.

这篇关于将其保存在实体框架中之前是否可以获得标识字段值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！