Asked by:
Cannot insert explicit value for identity column in table 'X' when IDENTITY_INSERT is set to OFF?

Question
-
I'm using ASP.NET Core WEB API (v 1.1) with SQL Database, both hosted on Azure.
This error happened months ago twice and restarting the web app solved the problem. After that it hadn't occurred anymore until it showed up couple of weeks ago. It gets occurred almost every day almost at same time on adding new 'Customers' entity and the error continues until I don't restart the application. After that it works perfectly for a day and then the same happens.
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 'Customers' when IDENTITY_INSERT is set to OFF.
I made a little research and tried many things although the error still happens.
Heres the entity class:
public class Customer {
public int Id { get; set; }
[Required]
public string Email { get; set; }
public string Firstname { get; set; }
public string Lastname { get; set; }
public int? UserId { get; set; }
[ForeignKey("UserId")]
public User User { get; set; }
public int? CompanyId { get; set; }
[ForeignKey("CompanyId")]
public Company Company { get; set; }
public string StripeCustomerId { get; set; }
public virtual ICollection<Order> Orders { get; set; }
}
I added Key and Db generated attributes to Id but it didn't handle the problem.
In Db Context OnModelCreating method:
modelBuilder.Entity<Customer>(entity => {
entity.HasIndex(e => e.CompanyId).IsUnique();
entity.HasIndex(e => e.UserId);
});
I had the code below that I changed to above and none of them handled the problem.
modelBuilder.Entity<Customer>()
.Property(b => b.Id)
.ValueGeneratedOnAdd();
modelBuilder.Entity<Customer>()
.HasOne(c => c.Company)
.WithOne(c => c.Customer)
.HasForeignKey<Customer>(q => q.CompanyId);
Here's the repository method of adding the entity
public Models.Customer Create(Models.Customer customer){
var dm = Mapper.Map<Infrastructure.Customer>(customer);
context.Add(dm);
context.SaveChanges();
var createdCustomer = Mapper.Map<Models.Customer>(dm);
return createdCustomer;
}
This is how I call the repository method:
return customersRepository.Create(new Models.Customer{
UserId = userId,
Email = email,
Firstname = firstname,
Lastname = lastname
});- Moved by Gary Gallanes [HCL Technologies] Tuesday, May 29, 2018 11:22 PM Not Azure Stack Issue
- Moved by Swikruti Bose Thursday, May 31, 2018 9:35 AM Better suited here than API Management
- Moved by Wendy ZangMicrosoft contingent staff Thursday, June 7, 2018 12:51 AM related to asp.net
Tuesday, May 29, 2018 9:47 AM
All replies
-
To clarify: Have you hosted ASP.NET Core WEB API on Azure App Service?
Try setting IDENTITY_INSERT to OFF and see if it helps: https://stackoverflow.com/questions/7063501/how-to-turn-identity-insert-on-and-off-using-sql-server-2008.
Tuesday, May 29, 2018 11:16 AM -
Yes it is hosted on Azure App Service.
Actually on the link that you provided is suggested to set the IDENTITY_INSERT to ON insert value with the identity provided by me, myself and the to set the IDENTITY_INSERT to OFF, that I am not looking for as I can't provide the id value since I want it to be incrementally generated by the database.
Tuesday, May 29, 2018 11:56 AM -
You may refer the below SO thread, which has described regarding the similar issue and see if it helps,
Looks like the issue is more related to .NET framework coding related. So, I have moves the thread to appropriate forum for better assistance.
Thursday, May 31, 2018 9:34 AM