.NET实体框架使用增量主键添加新行

I have a table in a SQL Server database which I want to add a row to. The primary key is not auto increment and I don't want it to be. However, on this occassion, I do want to add a row with the next available value. Is there a simple way to do this? I know I could query the table for the max value and then increment it but is there an easier way when using EF?

我在SQL Server数据库中有一个表，我想在其中添加一行。主键不是自动增量，我不希望它是。然而，在这种情况下，我确实希望添加一个具有下一个可用值的行。有简单的方法吗?我知道我可以查询表的最大值然后对它进行递增但是在使用EF时有没有更简单的方法呢?

If a separate query is neccessary getting the MAX value how would you do this with LINQ?

如果一个单独的查询需要获取最大值，那么如何使用LINQ实现这一点呢?

2 个解决方案

#1

It would be necessary unless the server is identity or computed column, in such a case you could do something like below.

除非服务器是标识或计算列，否则这样做是必要的，在这种情况下，您可以执行如下操作。

 myRecord.ID = Context.Records.Max(record => record.ID) + 1;

#2

There is not easy way for that, especially if you insist on next available value (= no gaps). You should use auto increment value and it will make your application perform much better.

要做到这一点并不容易，尤其是如果您坚持使用下一个可用值(=没有间隔)。您应该使用自动增值值，它将使您的应用程序性能更好。

What is the problem?

这个问题是什么?

You need to get a value from the database
您需要从数据库中获取一个值
You need to assign it to your entity
您需要将它分配给您的实体
You need to save the entity to the database
您需要将实体保存到数据库中

Looks easy, isn't it? No it isn't if you have concurrent environment where more then one thread can do the step 1. concurrently before other threads reach the step 3. = all threads will insert record with the same value.

看起来很容易,不是吗?不，如果有并发环境，那么不止一个线程可以执行步骤1。同时在其他线程到达步骤3之前。=所有线程都将插入具有相同值的记录。

How to deal with it? One way is to use separate table with max value and atomic operation to get and increment the value. I think stored procedure doing something like this should be atomic:

如何应对?一种方法是使用具有最大值和原子操作的单独表来获取和增加值。我认为这样做的存储过程应该是原子的:

DECLARE @Result INT
UPDATE SequenceTable SET @Result = MaxValue = MaxValue + 1 WHERE SequnceName = '...'
RETURN @Result

The call to the stored procedure should be outside of any transaction to make best throughput.

对存储过程的调用应该位于任何事务之外，以获得最佳吞吐量。

This should hopefully allow you to generate unique numbers but not sequence without gaps. If you take the value by such operation and you will not use that value, it will be lost and your sequence will contain gap.

这应该允许您生成唯一的数字，而不是没有间隔的序列。如果您通过这样的操作获取值，并且不使用该值，那么它将丢失，并且您的序列将包含gap。

If you don't want gap you must put the stored procedure call and the operation into transaction so that record in sequence table is locked until data are committed.

如果您不想要间隙，则必须将存储过程调用和操作放入事务中，以便在数据提交之前锁定序列表中的记录。

#1