Open Closed

Cannot insert explicit value for identity column #5436


0
bilalhaidar created

Hi, I am running my application on a new laptop. I installed the database via update-database command and then tried to run the app. I get the following exception:

"Cannot insert explicit value for identity column in table 'XXX' when IDENTITY_INSERT is set to OFF."

The exception occurs on this line:

var parent = _context.SpecificNeeds.Add(new SpecificNeed("Child at risk")).Entity;
 _context.SaveChanges();

All was working fine on the previous machine.

Any idea what might be the problem?


13 Answer(s)
  • 0
    maliming created
    Support Team

    Can you share the error stack information?

  • 0
    alper created
    Support Team

    that doesn't seem to seen as the out of box code. if it's your code, be aware that you cannot insert an entity with Id field set. The field (Id) is auto increment and must be empty.

  • 0
    bilalhaidar created

    Hello @alper,

    This is the stack trace.

    at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader)
       at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
       at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(Tuple`2 parameters)
       at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.&lt;&gt;c__DisplayClass12_0`2.<Execute>b__0(DbContext c, TState s)
       at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
       at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func`2 operation, Func`2 verifySucceeded, TState state)
       at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
       at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IReadOnlyList`1 entries)
       at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
       at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
       at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
       at Abp.EntityFrameworkCore.AbpDbContext.SaveChanges()
       at Tekvention.DrcApp.Migrations.Seed.DefaultSpecificNeedsCreator._create()
       at Tekvention.DrcApp.Migrations.Seed.DefaultSpecificNeedsCreator.Create()
       at Tekvention.DrcApp.Migrations.Seed.DefaultDrcBuilder.CreateDefaultDrcEntities()
       at Tekvention.DrcApp.Migrations.Seed.DefaultDrcBuilder.Create()
       at Tekvention.DrcApp.Migrations.Seed.SeedHelper.SeedHostDb(DrcAppDbContext context)
       at Tekvention.DrcApp.Migrations.Seed.SeedHelper.WithDbContext[TDbContext](IIocResolver iocResolver, Action`1 contextAction)
       at Tekvention.DrcApp.Migrations.Seed.SeedHelper.SeedHostDb(IIocResolver iocResolver)
       at Tekvention.DrcApp.Entity.DrcAppEntityFrameworkCoreModule.PostInitialize()
       at Abp.Modules.AbpModuleManager.<>c.<StartModules>b__15_2(AbpModuleInfo module)
       at System.Collections.Generic.List`1.ForEach(Action`1 action)
       at Abp.Modules.AbpModuleManager.StartModules()
       at Abp.AbpBootstrapper.Initialize()
    

    Actually, I am not adding any value for the ID. This code was working before. Now on the new machine it is not.

    I am just seeding some values and that's where I am getting the exception.

    private void _create()
            {
                //Create if table is empty
                if (!_context.SpecificNeeds.Any())
                {
                    var parent = _context.SpecificNeeds.Add(new SpecificNeed("Child at risk")).Entity;
                    _context.SaveChanges(); // Exception here
    
  • 0
    ismcagdas created
    Support Team

    Hi @bilalhaidar,

    This might be related to Database. What is your database (SQL Server 201x ) ?

  • 0
    bilalhaidar created

    Hi @ismcagdas

    It is: SQL Server 2016

  • 0
    ismcagdas created
    Support Team

    Could you set Identity Insert On by running an SQL query manually to see if it works ? <a class="postlink" href="https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017">https://docs.microsoft.com/en-us/sql/t- ... erver-2017</a>

  • 0
    bilalhaidar created

    I run the script on the database you mean?

  • 0
    maliming created
    Support Team

    Execute the sql statement directly in the database of your problem.

    SET IDENTITY_INSERT yourtable ON
    INSERT INTO yourtable (id, name) VALUES (1, 'test 1.0')
    SET IDENTITY_INSERT yourtable OFF
    
  • 0
    bilalhaidar created

    I noticed the following.

    var item = new SpecificNeed("Child at risk");
    

    The object item has Id equal to 0.

    After running the line below:

    var parent = _context.SpecificNeeds.Add(item).Entity;
    

    Now parent object has Id with a value of -2147482647

    Isn't that weird?

  • 0
    alper created
    Support Team

    there's a bunch of comments about your case, could you check out these links if they are similar for your case? <a class="postlink" href="https://github.com/aspnet/EntityFrameworkCore/issues/10167">https://github.com/aspnet/EntityFramewo ... sues/10167</a> <a class="postlink" href="https://github.com/aspnet/EntityFrameworkCore/issues/7743">https://github.com/aspnet/EntityFramewo ... ssues/7743</a>

  • 0
    bilalhaidar created

    Hi, I finally made it work. In one of the entities, I need to provide a value for Id. However, Id is configured to be Identity Column.

    Therefore, I had to make use of the following:

    _context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.DrcYesNoDecline ON");
                    ...
                    _context.SaveChanges();
                    _context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.DrcYesNoDecline OFF");
    
  • 0
    alper created
    Support Team

    smart solution

  • 0
    bilalhaidar created

    Thanks :)