Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Cannot insert explicit value for identity column in table 'X' when IDENTITY_INSERT is set to OFF? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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
           });
    Tuesday, May 29, 2018 9:47 AM

All replies