Entity Framework auto incrementing field, that isn't the Id

Question

I know this isn't the most ideal solution, but I need to add an auto incrementing field to one of my EF Code First objects. This column id NOT the Id, which is a guid.

Is there anyway for me to define the auto incrementing field in code, or would creating the column myself and defining in the DB that its auto incrementing work?

Dreiser

[DatabaseGenerated(DatabaseGeneratedOption.Identity)] – Centeno

Answer 1

You can annotate that property with DatabaseGenerated(DatabaseGeneratedOption.Identity). EF allows only single identity column per table.

public class Foo
{
    [Key]
    public Guid Id { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Bar { get; set; }
}

Doily

I had seen the DatabaseGeneratedOption.Identity but tbh I had been lazy and hadnt read into it, I just assumed that it made that column the PKey field. Thanks, I will give it a go. – Dreiser

Is this will still work with Multitenant Databases ? – Randolf

@Ahmad It will not work if each tenant has its own seed – Doily

I need both to be type int, but it doesn`t work with your code. The Primary key is always 0. Any ideas why this is? – Nicely

I have my Entity class same like this. If I try to update Foo Entity. I getting Error like Cannot Update Identity column Bar. Any idea about this? – Hersh

Answer 2

Old post thought I would share what I found with Entity Framework 6.1.3.

I created a simple data layer library using C# and .NET Framework 4.6.1, added a simple repository/service class, a code first context class and pointed my web.config file to a local SQL Express 2014 database.

In the entity class I added the following attribute constructor to the Id column:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }

Then I created a new migration by typing the following in Visual Studio 2015 Package Manager:

Add-Migration

Give the migration a name and then wait for the DbMigtation class to be created. Edit the class and add the following CreateTable operation:

CreateTable(
"dbo.Article",
    c => new
    {
        Id = c.Guid(nullable: false, identity: true),
        Title = c.String(),
        Content = c.String(),
        PublishedDate = c.DateTime(nullable: false),
        Author = c.String(),
        CreateDate = c.DateTime(nullable: false),
    })
    .PrimaryKey(t => t.Id);
}

The above table is an example the key point here is the following builder annotation:

nullable: false, identity: true

This tells EF to specifiy the column as not nullabe and you want to set it as an identity column to be seeded by EF.

Run the migration again with the following command:

update-database

This will run the migration class dropping the table first (Down() method) then creating the table (Up() method).

Run your unit tests and/or connect to the database and run a select query you should see your table in its new form, add some data excluding the Id column and you should see new Guid's (or whatever data type your choose) to be generated.

Farseeing

Answer 3

For those stumbling onto this question for EF Core, you can now create an auto-incrementing column with your model builder as follows:

builder.Entity<YourEntity>().Property(e => e.YourAutoIncrementProperty).UseNpgsqlIdentityAlwaysColumn();

Reference: https://www.npgsql.org/efcore/modeling/generated-properties.html

Hepner