Replacing SQL Server IDENTITY Due to Autogenerated Issue

Question

I have an Invoice Database that contains an ID IDENTITY that SQL Server is autogenerating by an increment of one (+1) each time a new record is created by a LINQ Insert.

The code that I am currently using to create a new record is posted below and the Incremental ID is autogenerated by SQL Server.

public async Task<IActionResult> Create([Bind(
                "PurchaseOrder,InvDate,DelDate,PaidDate,AgentName,FullName,FirstName, LastName,CustId,CompanyName,ClientRole,Email,Phone,Address," +
            "City,State,Zip,Country,ProdCode,Description,Quantity,UnitPrice,LineTotal,OrderTotal,Tax,Discount,Credit," +
            "Shipping,GrandTotal,Deposit,AmtDue,DiscAmt,Notes,Published,Year,Expenses,ProjectDescription,ClientProvision")]
            CreateNewOrderViewModel cnq)
        {
            int invId;

            try
            {
                await _context.AddAsync(cnq);
                await _context.SaveChangesAsync();
            }
            catch (InvalidCastException e)
            {
                ViewBag.Result = $"Database insert failed with: {e}";
                return View();
            }

        }

My issue is with the SQL Server ID IDENTITY. Every time the server is rebooted, my ID IDENTITY value increases by a factor of 1000 instead of the default value of 1, which for example, changes/increases the next record that I created by a factor of 1000. Hence, if my last record was 1001, the next record that is created will be 2001, instead of 1002. This behavior continues every time the server is updated and needs to be rebooted. I searched for an answer and discovered that the issue is a SQL Server bug that is based on the Cached protocol that remembers the latest ID values.

Since I am on a Shared Hosting Server and do not have full control of the Database, I only have DBO to my own database. I was wondering if there was a way for me to use LINQ to generate the incremental value for a new InvID column that I can then use as the record ID, instead of the SQL Server generated value.

Something is not right here, rebooting should not have anything to do with Id sequence. Can you post your table definition? — mxmissile, Mar 13 '19 at 17:34
@mxmissile OP is correct, restarting the service will impact identity sequences. https://stackoverflow.com/questions/14162648/sql-server-2012-column-identity-increment-jumping-from-6-to-1000-on-7th-entry — Derrick Moeller, Mar 13 '19 at 17:54
@DerrickMoeller very interesting, unfortunately I'm still using 2008, this was introduced in 2012. Thanks for the heads up. — mxmissile, Mar 13 '19 at 18:55
OP what does it matter if your ID's are not ++ in sequence? Database ID's should *never* be used for business logic. If your using this for an invoice # or something, your better coming up with your own numbering scheme, let the database do its thing. — mxmissile, Mar 13 '19 at 19:01
@mxmissile, I agree and decided to reseed the database before each new entry, as I posted below. — Johnny, Mar 13 '19 at 19:03

Amir Molaei · Accepted Answer · 2019-03-13T18:18:01.800

0

If you don't have control over database to handle sql server identity gap issue, you need to manually read the largest Id from the table and increment it by 1 for the new record.
Sample code to retrieve the last Id:

int lastId = _context.TableA.OrderByDescending(t => t.Id).FirstOrDefault().Id;
int newId = lastId + 1;
// Add the new record with newId

edited Mar 13 '19 at 18:18

answered Mar 13 '19 at 17:42

Amir Molaei

3,700
1
17
20

Yes, that would work and I can do it if I was using a Stored Procedure to create new records. However, I am not too familiar with LINQ so I do not know how to do this. If anyone has any ideas on how to use LINQ, as I am doing above to create the new records and insert a new InvID from a variable value, please post it. Tks. – Johnny Mar 13 '19 at 18:07
I do not think I can add a new ID when SQL Server has the ID column as the IDENTITY column already, for it is autogenerated. – Johnny Mar 13 '19 at 18:55
Is this atomic? – mxmissile Mar 13 '19 at 19:03
You can add the following attribute to the model property to disable autogenerated identity: `[System.ComponentModel.DataAnnotations.DatabaseGenerated(System.ComponentModel.DataAnnotations.DatabaseGeneratedOption.None)]` – Amir Molaei Mar 13 '19 at 19:05

score 0 · Answer 2 · answered Mar 13 '19 at 19:01

Reading several recommendations, I decided to check and reseed the ID before running the LINQ query.

        using (var connection = new SqlConnection(_data.DATA))
        {
            connection.Open();

            try
            {
                var seed = new SqlCommand("CreateIDSeed", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };
                seed.ExecuteNonQuery();

                await _context.AddAsync(cnq);
                await _context.SaveChangesAsync();
            }
            catch (InvalidCastException e)
            {
                ViewBag.Result = $"Database insert failed with: {e}";
                return View();
            }
        }

Where the CreateIDSeed looks like this:

CREATE PROCEDURE [dbo].[CreateIDSeed] 
AS
BEGIN
    SET NOCOUNT ON;
        declare @newId int
        select @newId = max(ID) from dbo.CreateNewOrderViewModel
        DBCC CheckIdent('dbo.CreateNewOrderViewModel', RESEED, @newId)  
END
GO

I tried inserting a few test records and it seems to be working, but I will know more when the Server is rebooted.

Replacing SQL Server IDENTITY Due to Autogenerated Issue

2 Answers2