ASP.NET MVC SaveChangesAsync throwing DbUpdateException

Question

I'm new to Entityframework in my practice app I'm trying to initialize my newly created database with some sample data and using SaveChangesAsync() but its giving internal server error. When I migrated my DbContext there was no problem and the database created successfully.

EDITED: after debugging I found SqlException: Cannot insert explicit value for identity column in table 'Packages' when IDENTITY_INSERT is set to OFF what should I do?

DbInitializer

public class DbContextInitializer
{
    private AppDbContext _context;
    private Data data = new Data();

    public DbContextInitializer(AppDbContext context)
    {
        _context = context;
    } 

    public async Task SeedData()
    {
        if (!_context.Packages.Any())
        {
            var pack1 = data.get(1);
            _context.Packages.Add(pack1);

            var pack2 = data.get(2);
            _context.Packages.Add(pack2);

            var pack3 = data.get(3);
            _context.Packages.Add(pack3);

            await _context.SaveChangesAsync();
        }
    }

}

DbContext

 public class AppDbContext : DbContext
  {
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {

    }

    public DbSet<TableModel> Packages { get; set; }
  }

Configure in Startup.cs

public void Configure(IApplicationBuilder app,
        IHostingEnvironment env,
        DbContextInitializer seed)
    {
        if (env.IsDevelopment())
        {
            app.UseDeveloperExceptionPage();
            app.UseBrowserLink();
        }
        else
        {
            app.UseExceptionHandler("/Home/Error");
        }

        app.UseStaticFiles();

        app.UseMvc(routes =>
        {
            routes.MapRoute(
                name: "default",
                template: "{controller=App}/{action=Index}/{id?}");
        });

        seed.SeedData().Wait();
    }

ConfigureServices in Startup.cs

    public void ConfigureServices(IServiceCollection services)
    {

        services.AddDbContext<AppDbContext>(options => options.UseSqlServer(_configurationroot.GetConnectionString("ConString")));
        services.AddMvc();
        services.AddTransient<ITableData, MockTableData>();
        services.AddTransient<DbContextInitializer>();
    }

Error

because I want to initialize my database on startup, I did it before there was no problem but this time I don't know where is the error — Sabir Hossain, Nov 16 '17 at 14:22
Are you sure the primary keys in the seed data doesn't already exist in the database tables? Are those tables always blank at the start of the application? — J. D., Nov 16 '17 at 14:38
`An exception of type 'System.AggregateException' occurred in System.Private.CoreLib.dll but was not handled in user code` any Idea? — Sabir Hossain, Nov 16 '17 at 15:24
`SqlException: Cannot insert explicit value for identity column in table 'Packages' when IDENTITY_INSERT is set to OFF.` what should I do ? — Sabir Hossain, Nov 16 '17 at 15:53

score 0 · Answer 1 · answered Nov 16 '17 at 20:40

I'm sorry I cannot see your data but I do know this. If your primary key is an identity column then you cannot insert data into that column. SQL has control of the data that goes into that column based on your seed and increment value it will start at the seed and increment the value by what you told it to. Identity (1,1) will get you 1,2,3,4 and so on. Don't mean to lecture if you already knew.

So you have two options.

Go into your dummy data and remove one column from all the rows. the first column would be a preset ID that you are trying to force into your Identity column. remove that field and it will go right in there without a hitch.

values (1, milk), (2, bread), (3, vodka) becomes values (milk), (bread), (vodka)

SQL will handle the IDs

or you can turn on Identity insert which tells SQL I want to insert stuff into your Identity column.

here is a link to a Stack exchange post on how to do that for entity framework

How can I force entity framework to insert identity columns?

Of course you could also change your database design to not use an identity column for the primary key which I guess would be option number three.