LINQ插入(无身份)列 [英] LINQ Inserts without IDENTITY column

问题描述

我正在使用LINQ，但是我的数据库表没有IDENTITY列(尽管它们使用的是替代主键ID列)

I'm using LINQ, but my database tables do not have an IDENTITY column (although they are using a surrogate Primary Key ID column)

这行得通吗?

要获取表的标识值，有一个称为GetIDValueForOrangeTable()的存储过程，该存储过程查看SystemValues表并在其中递增ID. 有什么办法可以让LINQ从插入的SystemValues表中获取ID值，而不是内置的IDENTITY?

To get the identity values for a table, there is a stored procedure called GetIDValueForOrangeTable(), which looks at a SystemValues table and increments the ID therein. Is there any way I can get LINQ to get the ID value from this SystemValues table on an insert, rather than the built in IDENTITY?

顺便说一句，我认为这不是一个好主意，尤其是对于Web应用程序而言.我想由于这个SystemValues查找，将会有很多并发冲突.我对自己的担心有道理吗?

As an aside, I don't think this is a very good idea, especially not for a web application. I imagine there will be a lot of concurrency conflicts because of this SystemValues lookup. Am I justified in my concern?

欢呼 邓肯

推荐答案

当然，您也可以安全地使用LINQ进行这项工作:

Sure you can make this work with LINQ, and safely, too:

• 在TRANSACTION中包装对"GetIDValue .....()"函数中基础SystemValues表的访问权限(而不是具有READUNCOMMITTED隔离级别！)，那么只有一个用户可以在任何位置访问该表给定时间，您应该可以安全地分发ID的
• 在保存实体之前从LINQ调用存储的proc，如果要处理新实体(如果尚未设置ID)，则存储ID.
• 将您的实体存储在数据库中

那应该工作-不知道它是否比让数据库处理工作更快，更高效-但它应该工作并且安全.

That should work - not sure if it's any faster and any more efficient than letting the database handle the work - but it should work - and safely.

马克

更新:

这样的事情(适应您的需求)将可以安全地工作:

Something like this (adapt to your needs) will work safely:

CREATE PROCEDURE dbo.GetNextTableID(@TableID INT OUTPUT)
AS BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    BEGIN TRANSACTION 

    UPDATE SystemTables
    SET MaxTableID = MaxTableID + 1
    WHERE ........ 

    SELECT
        @TableID = MaxTableID 
    FROM    
        dbo.SystemTables

    COMMIT TRANSACTION
END

关于性能-只要您有合理数量(可能少于50个)的并发用户，并且只要此SystemTables表未用于其他用途，那么它就可以执行.

As for performance - as long as you have a reasonable number (less than 50 maybe) of concurrent users, and as long as this SystemTables tables isn't used for much else, then it should perform OK.

这篇关于LINQ插入(无身份)列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！