How to set an explicit value to id property in EF Core?

As you know, all the id properties are, by default, IDENTITY columns in the SQL Server where the values are generated by the database on INSERT statements. Here, you will learn how to insert an explicit value to id property.

Consider the following Student entity.

public class Student
{
    public int StudentId { get; set; }
    public string StudentName { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public decimal Height { get; set; }
    public float Weight { get; set; }     
}

EF will create an IDENTITY column for the StudentId property with seed value 1 and auto increment 1. So, on each INSERT, SQL Server will generate a value and insert it in the StudentId column. You cannot assign a value to StudentId manually.

To insert explicit values into a SQL Server IDENTITY column, you need to manually enable IDENTITY_INSERT before calling SaveChanges().

The following example demonstrates how to set an explicit value to an id property.

using (var context = new SchoolContext())
{
    var std1 = new Student() { StudentName = "Steve" };
    context.Students.Add(std1);
    context.SaveChanges(); // save Student with DB generated StudentId

    // std1.StudentID will be 1
    // provide an explicit value to StudentId
    var std2 = new Student() { StudentId = 100, StudentName = "Steve" };

    context.Database.OpenConnection();
    try
    {
        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Students ON");
        context.Add(std2);
        context.SaveChanges();
        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Students OFF");
    }
    finally
    {
        context.Database.CloseConnection();
    }
}

In the above example, we first saved a new Student with the DB-generated StudentId. Then, we executed the SQL command using the context.Database.ExecuteSqlCommand() method and set IDENTITY_INSERT ON. Then we called the SaveChanges() method. This will insert a new Student record where StudentId will be 100 in the database.

Thus, you can set an explicit value to the id property manually.