Error solution for identity insert is set off

Question

I'm using EF code first DomesticTask class looks like

 public class DomesticTask
    {
        public int Id { get; set; }

        [Required]
        public ApplicationUser DomesticClient { get; set; }
        public DateTime DateTime { get; set; }

        [Required]
        [StringLength(500)]
        public string Description { get; set; }

        [Required]
        public Category Category { get; set; }
    }

Category Class is following

{
    public int Id { get; set; }

    [Required]
    [StringLength(255)]
    public string Name { get; set; }
}

DB context is following

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public DbSet<DomesticTask> DomesticTasks { get; set; }
    public DbSet<Category> Categories { get; set; }

and my migration class looks like this

public partial class PopulateCategoriesTable : DbMigration
{
    public override void Up()
    {
        Sql("INSERT INTO Categories (Id, Name) VALUES (1, 'Plumber')");
        Sql("INSERT INTO Categories (Id, Name) VALUES (2, 'Mason')");
        Sql("INSERT INTO Categories (Id, Name) VALUES (3, 'Labour')");
        Sql("INSERT INTO Categories (Id, Name) VALUES (4, 'Milk-man')");
        Sql("INSERT INTO Categories (Id, Name) VALUES (5, 'Electrician')");
    }

    public override void Down()
    {
        Sql("DELETE FROM Categories WHERE Id IN (1, 2, 3, 4, 5)");
    }
}

When I do update-database, it shows following error.

Error Number:544,State:1,Class:16 Cannot insert explicit value for identity column in table 'Categories' when IDENTITY_INSERT is set to OFF.

How do I resolve the error?

Possible duplicate of [Cannot insert explicit value for identity column in table 'table' when IDENTITY\_INSERT is set to OFF](https://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity) — Igor, Nov 06 '17 at 20:21
If you search/google the error message you will find plenty of answer links. — Igor, Nov 06 '17 at 20:21

score 0 · Answer 1 · answered Nov 06 '17 at 20:37

0

You're inserting values for Id of category that is an identity column, wich is supposed to be inserted by sql server.

answered Nov 06 '17 at 20:37

Oleksandr Khytrov

135
2
11

score 0 · Answer 2 · answered Nov 08 '17 at 16:21

You should update your migration script to allow you to set the identity column values while inserting. I am assuming of course that you are using SQL Server as your data store, as the following code is using T-SQL not ANSI-92 SQL.

public partial class Seed_Categories : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            SET IDENTITY_INSERT [dbo].[Categories] ON;

            INSERT INTO [dbo].[Categories] ([ID], [NAME], [CODE])
                VALUES
                    (1, 'Science Fiction',      'SCIFI'),
                    (2, 'Historical Fiction',   'HISFI'),
                    (3, 'Historical',           'HISTO'),
                    (4, 'English Literature',   'ENGLT'),
                    (5, 'Spanish Literature',   'SPALT');

            SET IDENTITY_INSERT [dbo].[Categories] OFF;
            ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }
}

Error solution for identity insert is set off

2 Answers2