Set explicit value for ID column in Entity Framework core

I’ve had to build a few API’s in the past year or so, all of them in .Net Core. This particular one had a SQL database and we used Entity Framework core with a code first approach.

As is the standard approach, each of the database tables included an Id column. Most of them were perfectly ok for the Id to be auto generated by SQL. However, the design of one of our tables needed to include the ability for us to specify the Id manually.

This post shows how to do set the ID manually on a SQL database column when using the Entity Framework code first approach..

Why might you need to specify a custom Id column in a SQL database

Let’s say we’re building a database of holidays. Each holiday would be assigned to a category, such as Ski, Sun, or Spa.

Each one of those categories would have an Id, perhaps 1, 2 and 3. You database contains a list of holidays each of which relate to one of those categories.

Now imagine adding a few categories, deleting a category, and adding a few more. Throw in a database rebuild in the mix… and you might end up with the same table of categories, but the Id of which could have changed.

And then your data relationships are broken.

No one likes a broken relationship….

But what if we can design the database so that we manually specify the unique Id for each? Then we can make sure that no matter the order of the database table, or how it’s built, that our holiday categories will always relate to the correct category Id.

Build the database table with an explicit Id

If you’ve already tried to write some code to create some data including the Id column for each row, and run your Update-Database command, you may well have got an error message a bit like this one:

SqlException: Cannot insert explicit value for identity column in table 'Category' when IDENTITY_INSERT is set to OFF.

It’s telling you that you can’t set the SQL database column Id value manually when the setting Identity_Insert is set to OFF – and it is set to OFF by default.

So how do we solve this problem?

Use DatabaseGeneratedOption on Model property

In order to do this when using the code first approach and Entity Framework Core, you need to specify that the Id column does not need to be generated by the database, by using the [DatabaseGenerated(DatabaseGeneratedOption.None)] against the relevant property.

This example shows part of a class which does just this :

	public class Category
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int Id { get; set; }
        [MaxLength(50)]
        public string Name { get; set; }
        public bool Ordered { get; set; }

		...
    }

That would mean when we need to create a new Category we could run some code like this:

	...
				
		Category Fishing = new Category(){
                        Id=124,
                        Name="Fishing",
                        Ordered = true
               };

Use Fluent API to set the Column ID Manually

We could also have used FluentAPI to make the same change by using this code:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Category>()
        .Property(b => b.Id)
        .ValueGeneratedNever();
}

Either way does the trick and allows you to specify exactly what you want the Primary Key value to be.

Useful further reading

Using EF Core code first approach
More about DatabaseGeneratedOption