LINQ Inserts without IDENTITY column

Question

Hello

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

Can this work?

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?

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?

Cheers Duncan

Answer 1

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

• wrap the access to the underlying SystemValues table in the "GetIDValue.....()" function in a TRANSACTION (and not with the READUNCOMMITTED isolation level!), then one and only one user can access that table at any given time and you should be able to safely distribute ID's
• call that stored proc from LINQ just before saving your entity and store the ID if you're dealing with a new entity (if the ID hasn't been set yet)
• store your entity in the database

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.

Marc

UPDATE:

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

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.

Answer 2

You are very justified in your concern. If two users try to insert at the sametime, both might be given the same number unless you do as described by marc_s and put the thing in a transaction. However, if the transaction doesn't wrap around your whole insert as well as the table that contains the id values, you may still have gaps if the outer insert fails (It got a value but then for some other reason didn't insert a record). Since most people do this to avoid gaps (something that is in most cases an unnecessary requirement) it makes life more complicated and still may not achieve the result. Using an identity field is almost always a better choice.