Autonumber seeding in SQL Server

Question

I have inherited an application and have made alterations that use Entity Framework.

The alterations were creating a CRUD for an existing table. I've used the MVC scaffolding for creating an controller and views. All works fine except Create.

On investigation I found that the table I'm creating the CRUD code for has three columns an autonumbered primary column (ccID), a nvarchar(50) column (ClosureCode) and a bit column (ccRedundant - default 0). The ID column has a seed of 1 and increment of 1 yet it has two records with values:

ccID  ClosureCode       ccRedundant
-----------------------------------
-1    Not Set           False
 0    Not Applicable    False

followed by other records.

The scaffolding code passes 0 as the ID for a new record and as such ModelState.IsValid is always False and no new records are saved. Edit, Update and Delete work fine.

I've tried setting the value of ID to -2 and Integer.MaxValue before posting but the save still fails at the ModelState.IsValid which is never true.

The scaffolding code is as follows:

    ' POST: ClosureCodes/Create
    'To protect from over-posting attacks, please enable the specific properties you want to bind to, for 
    'more details see https://go.microsoft.com/fwlink/?LinkId=317598.
    <HttpPost()>
    <ValidateAntiForgeryToken()>
    Function Create(<Bind(Include:="ccID,ClosureCode,ccRedundant")> ByVal tblClosureCode As tblClosureCode) As ActionResult

        
        If ModelState.IsValid Then

            Try

                db.tblClosureCodes.Add(tbClosureCode)
                db.SaveChanges()

                Return RedirectToAction("Index")

            Catch ex As Exception

                ErrorLogging.LogExceptionToLogFile(thisClass, "Create", ex)

                ViewData("Message") = ErrorLogging.ErrorString(thisClass, "Create", ex, ex.InnerException)

                Return RedirectToAction("Error")

            End Try

        End If

        Return View(tblClosureCode)

    End Function

The only changes I made to the generated code was to add a try catch around the save bit with a method to log any exceptions and redirect to a custom error page with the error in viewData.

Am I right in thinking that the presence of a record with ccID 0 is the reason why the Created record will not save? Is there a way around this.

UPDATE

Properties for ccID column in tblClosureCodes as requested:

Can you post the property definition of the auto-increment field from your model. As far as I know, EF should no how to handle this and no value needs to be set. Is tblClosureCode.ccrID decorated with a DatabaseGenerated attribute? — Ross Bush, Apr 08 '20 at 17:00
That field is not defined as an IDENTITY in your database schema. — Ross Bush, Apr 08 '20 at 17:22
@RossBush Sorry I must have double clicked the column and it set to No... I've updated the image... the field is defined as IDENTITY. I suspect that at some stage it was not and the -1 and 0 records were added then the IDENTITY was set with Seed 1 and Increment 1... I can't think of any other way those records were created. — Mych, Apr 08 '20 at 17:31
EF should be able to handle the auto-increment fields. Can you post the EF generated code definition of this field. Do you just want to remove the records with 0 and -1? — Ross Bush, Apr 08 '20 at 17:37
@RossBush … No those records are required. I suspect that those records were added before making the field an IDENTITY and because of this confusing the Create code and the new record is not created when the form is posted. That's all I can think of. I've used this method of creating CRUD code and it's never failed. In the tables I've created the records always start at the Seed value. — Mych, Apr 08 '20 at 19:03
You shouldn't have to set the value, if EF knows the fields maps to an auto-increment database field it is handled in the plumbing code. The auto-increment part is not handled by EF, it is handled by SQL Server during insert attempts. I am confused about the setting it to zero part. See if this answer helps at all --> https://stackoverflow.com/questions/33946212/how-to-mark-identity-column-properly-with-entity-framework-6-1 — Ross Bush, Apr 08 '20 at 19:15

score 0 · Accepted Answer · answered Jul 08 '20 at 10:19

0

It seems the IDENTITY (Seed and Increment) was set at a later date. I removed the table from the EF Diagram and then used Update Model from Database to reinstate the table in the diagram. This fixed my issue.

answered Jul 08 '20 at 10:19

Mych

2,527
4
36
65

Autonumber seeding in SQL Server

1 Answers1