How to auto-increment Key named other than Id

Question

I'm using EF Code First and have a table that does not have a Key named Id. For this table it made sense to name the Key SelectorId. Problem is when I add a new row I am getting an error:

Cannot insert the value NULL into column 'SelectorId', table 'dbProgramData.dbo.Selectors'; column does not allow nulls. INSERT fails. The statement has been terminated.

Of course it doesn't allow nulls - it's the Primary Key for this table, but I am assuming the Key is auto-increment. I have the model defined like this:

public class Selector
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int SelectorId { get; set; }

    public string ProgramId { get; set; }

    ....

}

I am trying to add a row with this code (which works fine using VS on my local PC, but fails with the above error on the remote VPS after being published:

ProgramDbContext dbProgramData = new ProgramDbContext();

Selector selector = new Selector();

selector.ProgramId = programId;

dbProgramData.Selectors.Add(selector)
dbProgramData.SaveChanges();

In the ProgramDbContext class I have:

public DbSet<Selector> Selectors { get; set; }

I suspect this problem is resolved if I were to rename SelectorId to Id? After running Add Migration, the up/down script shows:

CreateTable(
    "dbo.Selectors",
    c => new
        {
            SelectorId = c.Int(nullable: false, identity: true),
            ProgramId = c.String(),

    ....

Can anyone tell me what I'm doing wrong? I have this same problem with two tables I've created and both of them have a Pk named other than Id.

UPDATE

As suggested I went into SSMS and Design View for this table on the remote server. I see that Is Identity was set to No. I double checked my local database table and Is Identity is set to Yes as it should be.

The way I am getting the database tables to the remote VPS server is with SSMS by right-clicking the database, then on Tasks -> Import Data. I point to my local .\SQLEXPRESS and perform the import. It appears that the import succeeds, but apparently not. Prior to performing the import I am deleting all tables in the remote database.

Shouldn't this method create the exact schema along with importing the data?

Answer 1

To make things clear:

• EF uses a definition of an schema independent of the physical database (the Entity Data Model, aka EDM)
• the database has its own schema, independent of the EDM

To get things to work, both schemas must be consistent. So, if you have an identity column in the database, that column must be correctly defined in the EDM. As you said, the EDM column must have the DatabaseGenerated property equals to DatabaseGeneratedOption.Identity.

In your development machine I don't know if you're using Database First, or Code First, or drawing an EDM schema to generate the database, but it's clear that the schema is consistent with the database, so it works fine.

If you try to use an EDM schemathat doesn't much the database you'll get errors.

This is how an identity column works:

• in SQL Server, when you add the identity property to a column of a table, when you insert anew row you cannot specify the value of that column: SQL Servers automatically generates a sequential number, and inserts the row.
• if an EDM has a column marked as DatabaseGenerated, when you use it to insert a new row, the generated SQL:
  • doesn't include the column in the insert statement
  • right after inserting it selects the scope_identity() function to get the value generated by the server.

So it's normal that if the EDM and the Databasedoesn't have consistent information, the insertion fails.

In this case the problem is clearly a deployment problem. You can try alternative ways to do it:

• backup the database in your development machine, and restore it in the VPS SQL Server
• using SSMS find the files of the database (viewing the DB properties. Once you know where the files are, detach the DB (in SSMS, right click the database and you'll find this option under the Tasks menu option). Detach the DB in the VPS (if it exists). Now you can copy the files from the development machine to the server, and attach it again, both in the dev machine and in the server.

This two methods are completely fail-safe. If you use the import/export or any other action to copy the DB it's possible that you run into trouble because there are some options to configure.

By the way, if you're using EF Code First, unless you modify it using attributes, by default any Id or TableNameId of integer type, is treated as a DatabaseGenerated column. (This is configuration by convention). But, if you don't follow the convention, you can always use attributes (or the Fluent API) to configure the column, marking it as Key and specifying where it's generated.

Answer 2

in sql server, goto design view of the table in question. click on the column you want incremented, and in the column properties panel, scroll down to Identity Specification. set it to yes, and then you should be able to set it to auto increment by 1.