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.

Answer 1

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