EF Core Failing on IDENTITY_INSERT is set to OFF[Answered] RSS

• • Reply
  tvb2727

  Participant

  1038 Points

  2817 Posts

  EF Core Failing on IDENTITY_INSERT is set to OFF

  Jul 02, 2020 05:47 AM|tvb2727|LINK

  Hello,

  I am using EF Core and I have table that is being problem with inserting new records into one of my tables.

  All other tables have worked fine throughout my MVC web application:

  Error:

  Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'MyTable' when IDENTITY_INSERT is set to OFF.
     at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__180_0(Task`1 result)
     at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
     at System.Threading.Tasks.Task.Execute()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)
     at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.<ExecuteAsync>d__17.MoveNext()
  --- End of stack trace from previous location where exception was thrown ---
     at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
     at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
     at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.<ExecuteAsync

  Here is the setup in the snapshot (I don't think this matters -> just for migration)

                      b.Property<int>("EventResourceId")
                          .ValueGeneratedOnAdd()
                          .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

  Here is what I did in my actual class:

          /// <summary>
          /// Gets or sets the event resource identifier.
          /// </summary>
          /// <value>
          /// The event resource identifier.
          /// </value>
          [Key]
          [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
          public int EventResourceId { get; set; }

  I tried running this on my SQL server (which is Azure)

  SET IDENTITY_INSERT dbo.EventResources ON

  Here is on model creating:

                  entity.HasKey(e => e.EventResourceId);
  
                  entity.HasIndex(e => new { e.Name, e.EventResourceTypeId }).IsUnique().HasName("UQ_EventResources_EventResourceTypeId_Name");

  I tried this in my Repo:

              await BIContext.Database.ExecuteSqlCommandAsync("SET IDENTITY_INSERT [dbo].[EventResources] On").ConfigureAwait(false);

          public async Task<int> CreateEventResource(EventResourceCreateDTO eventResourceCreate)
          {
              if (eventResourceCreate == null)
              {
                  throw new ArgumentNullException(nameof(eventResourceCreate));
              }
  
              await BIContext.Database.ExecuteSqlCommandAsync("SET IDENTITY_INSERT [dbo].[EventResources] On").ConfigureAwait(false);
  
              var newEventResource = new EventResources
              {
                  Name = eventResourceCreate.Name,
                  Description = eventResourceCreate.Description,
                  EventResourceId = eventResourceCreate.EventResourceTypeId,
                  CreatedByUserId = BIContext.UserRequestingId,
                  CreatedDate = DateTime.UtcNow,
                  ModifiedByUserId = BIContext.UserRequestingId,
                  ModifiedDate = DateTime.UtcNow
              };
  
              BIContext.EventResources.Add(newEventResource);
  // fails here...
              await SaveChangesAsync().ConfigureAwait(false);
  
              return newEventResource.EventResourceId;
          }

  What else can I try? I have like 6 other tables and all of them are working fine!

• • Reply
  asteranup

  All-Star

  20953 Points

  4984 Posts

  Re: EF Core Failing on IDENTITY_INSERT is set to OFF

  Jul 02, 2020 06:14 AM|asteranup|LINK

  tvb2727

  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

  Can you try with DatabaseGeneratedOption.None

  Thanks,
  Anup Das Gupta
  Visit My Blog
  You can also connect with me in LinkedIn
• • Reply
  tvb2727

  Participant

  1038 Points

  2817 Posts

  Re: EF Core Failing on IDENTITY_INSERT is set to OFF

  Jul 02, 2020 06:19 AM|tvb2727|LINK

  asteranup

  tvb2727

  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

  Can you try with DatabaseGeneratedOption.None

  I'm embarrassed!

  VERIFY proper columns are being updated!

  I overlooked another value where the TypeId was being set from the incoming model, but I was actually setting that TypeId to the Primary Key. 

  Not sure how I overlooked it. I removed the primary key from the NEW model and set it to the TypeId on the db and everything worked!

  It was giving me an error that was pointing to the primary key, but in a way I was understanding it wrong!

  EventResourceId is the PK. 

              var newEventResource = new EventResources
              {
                  Name = eventResourceCreate.Name,
                  Description = eventResourceCreate.Description,
                  EventResourceId = eventResourceCreate.EventResourceTypeId,
                  CreatedByUserId = BIContext.UserRequestingId,
                  CreatedDate = DateTime.UtcNow,
                  ModifiedByUserId = BIContext.UserRequestingId,
                  ModifiedDate = DateTime.UtcNow
              };

      EventResourceId = eventResourceCreate.EventResourceTypeId,

  Should be:
  
  

      EventResourceTypeId = eventResourceCreate.EventResourceTypeId,