Code First With Existing Database, Entity Framework: Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Question

First off, please do not mark this as duplicate. I know it has been touched several times, but I believe my situation is different. I'm doing a code first approach where the database essentially already existed in some for or another as well.

When I call DbContext.SaveChanges() I receive the following error: "Cannot insert explicit value for identity column in table 'CustomerShippingConfiguration' when IDENTITY_INSERT is set to OFF". As you can see in the generated SQL below, Entity Framework is trying to insert 0 into the Id column for a new record.

INSERT [dbo].[CustomerShippingConfiguration]([Id], [CustomerId], [AverageCartonWeight], [AverageCartonsPerPallet], [CreatedBy], [Created], [UpdatedBy], [Updated])
VALUES (@0, @1, @2, @3, @4, @5, @6, @7)

-- @0: '0' (Type = Int32)

-- @1: '119' (Type = Int32)

-- @2: '11' (Type = Decimal, Precision = 18, Scale = 2)

-- @3: '11' (Type = Int32)

-- @4: '616' (Type = Int32)

-- @5: '8/9/2016 10:09:08 AM' (Type = DateTime)

-- @6: '616' (Type = Int32)

-- @7: '8/9/2016 10:09:08 AM' (Type = DateTime)

This Id column is of type INT and setup as the identity column, primary key, and auto increments.

The entity model looks like this:

public class ShippingConfigurationEntity : EntityBase<ShippingConfigurationEntity>, IEntity
{
      public int CustomerId { get; set; }

      public virtual CustomerEntity Customer { get; set; }

      public decimal? AverageCartonWeight { get; set; }

      public int? AverageCartonsPerPallet { get; set; }

      public virtual ICollection<ShippingAddressEntity> Addresses { get; set; }

      public ShippingConfigurationEntity()
      {
          Addresses = new List<ShippingAddressEntity>();
      }
}

public abstract class EntityBase<T>
{
      public virtual int Id { get; set; }

      public virtual int CreatedBy { get; set; }

      public virtual DateTime Created { get; set; }

      public virtual int UpdatedBy { get; set; }

      public virtual DateTime Updated { get; set; }
}

And the configuration for the entity looks like this:

public class ShippingConfigurationConfiguration : EntityTypeConfiguration<ShippingConfigurationEntity>
{
    public ShippingConfigurationConfiguration()
    {
        HasKey(t => t.Id);

        Property(t => t.CustomerId).IsRequired();
        Property(t => t.AverageCartonsPerPallet).IsOptional();
        Property(t => t.AverageCartonWeight).IsOptional();
        Property(t => t.CreatedBy).IsRequired();
        Property(t => t.Created).IsRequired();
        Property(t => t.UpdatedBy).IsRequired();
        Property(t => t.Updated).IsRequired();

        ToTable("CustomerShippingConfiguration");

        HasMany(x => x.Addresses).WithRequired(x => x.ShippingConfiguration).HasForeignKey(x => x.ShippingConfigurationId).WillCascadeOnDelete();
    }
}

There is a parent entity called Customer that looks similar to this:

public class CustomerEntity : EntityBase<CustomerEntity>, IEntity
{
    public int LocationID { get; set; }        

    public virtual ShippingConfigurationEntity ShippingConfiguration { get; set; }
}

Where the configuration looks like this:

class CustomerConfiguration : EntityTypeConfiguration<CustomerEntity>
{
    public CustomerConfiguration()
    {
        HasKey(t => t.Id);

        Property(t => t.LocationID).IsRequired();

        Ignore(t => t.Created);
        Ignore(t => t.CreatedBy);
        Ignore(t => t.Updated);
        Ignore(t => t.UpdatedBy);

        ToTable("Customer");

        Property(t => t.Id).HasColumnName("ID");
        Property(t => t.LocationID).HasColumnName("LOCATION_ID");

        HasOptional(x => x.ShippingConfiguration).WithRequired(x => x.Customer).WillCascadeOnDelete();
    }
}

If I remove the relationship between CustomerEntity and ShippingConfiguration Entity, everythings if I use the following for the Id property on ShippingConfiguration:

Property(t => t.Id).IsRequired().HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

I have another entity which is setup in a very similar pattern that works just fine. I'm really at a loss here. Thank you in advance for any advice.

Answer 1

You need to tell your configuration that the database will handle key generation.

using System.ComponentModel.DataAnnotations.Schema;

public class ShippingConfigurationConfiguration : EntityTypeConfiguration<ShippingConfigurationEntity>
{
    public ShippingConfigurationConfiguration()
    {
        HasKey(t => t.Id);

        Property(a => a.Id)
            .IsRequired()
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}