Entity Framework: Cannot insert explicit value for identity column in table '[table]' when IDENTITY_INSERT is set to OFF

Question

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

I'm a little confused by this (crystal) clear error. The BatchId column is marked as the KEY, in SQL is an auto-increment PK field. When I try to assign my own ID I get this error. When I don't assign an ID, I get this error. I am using EF 6+, reverse generated the classes from the DB in code first style, do not have any EDMX files, just POCO models and EntityTypeConfiguration mapping classes.

My Mapping class:

public class BatchMap : EntityTypeConfiguration<Batch>
{
    public BatchMap()
    {
        // Primary Key
        this.HasKey(t => t.BatchID);

        // Properties
        this.Property(t => t.BatchDesc)
            .HasMaxLength(50);

        this.Property(t => t.UserID)
            .HasMaxLength(50);

        // Table & Column Mappings
        this.ToTable("Batch");
        this.Property(t => t.BatchID).HasColumnName("BatchID");
        this.Property(t => t.VendorID).HasColumnName("VendorID");
        this.Property(t => t.BatchDesc).HasColumnName("BatchDesc");
        this.Property(t => t.ImportDate).HasColumnName("ImportDate");
        this.Property(t => t.ImportMethodID).HasColumnName("ImportMethodID");
        this.Property(t => t.BatchTotal).HasColumnName("BatchTotal");
        this.Property(t => t.BatchCount).HasColumnName("BatchCount");
        this.Property(t => t.StartDateTime).HasColumnName("StartDateTime");
        this.Property(t => t.EndDateTime).HasColumnName("EndDateTime");
        this.Property(t => t.UserID).HasColumnName("UserID");
    }
}

My POCO class:

public partial class Batch
{
    public decimal BatchID { get; set; }
    public Nullable<decimal> VendorID { get; set; }
    public string BatchDesc { get; set; }
    public Nullable<System.DateTime> ImportDate { get; set; }
    public Nullable<decimal> ImportMethodID { get; set; }
    public Nullable<decimal> BatchTotal { get; set; }
    public Nullable<decimal> BatchCount { get; set; }
    public Nullable<System.DateTime> StartDateTime { get; set; }
    public Nullable<System.DateTime> EndDateTime { get; set; }
    public string UserID { get; set; }
}

And finally, the context definition:

public partial class PaymentProcessingContext : DbContext
{
    static PaymentProcessingContext()
    {
        Database.SetInitializer<PaymentProcessingContext>(null);
    }

    public PaymentProcessingContext() : base("Name=PaymentProcessingContext")
    {
    }

    public DbSet<AccountLookup> AccountLookups { get; set; }
    public DbSet<Batch> Batches { get; set; }
    public DbSet<Channel> Channels { get; set; }
    public DbSet<Deposit> Deposits { get; set; }
    public DbSet<ExceptionType> ExceptionTypes { get; set; }
    public DbSet<FeeType> FeeTypes { get; set; }
    public DbSet<ImportMethod> ImportMethods { get; set; }
    public DbSet<PaymentMethod> PaymentMethods { get; set; }
    public DbSet<PaymentTranDetail> PaymentTranDetails { get; set; }
    public DbSet<PpaAdmin> PpaAdmins { get; set; }
    public DbSet<Preference> Preferences { get; set; }
    public DbSet<PrefType> PrefTypes { get; set; }
    public DbSet<Return> Returns { get; set; }
    public DbSet<SessionData> SessionDatas { get; set; }
    public DbSet<SettleBatchReport> SettleBatchReports { get; set; }
    public DbSet<Settlement> Settlements { get; set; }
    public DbSet<SplitPayment> SplitPayments { get; set; }
    public DbSet<TransCode> TransCodes { get; set; }
    public DbSet<UTLData> UTLDatas { get; set; }
    public DbSet<Vendor> Vendors { get; set; }
    public DbSet<VendorRefLookup> VendorRefLookups { get; set; }
    public DbSet<Missing_Trans_Key> Missing_Trans_Keys { get; set; }
    public DbSet<UnmatchedDeposit> UnmatchedDeposits { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new AccountLookupMap());
        modelBuilder.Configurations.Add(new BatchMap());
        modelBuilder.Configurations.Add(new ChannelMap());
        modelBuilder.Configurations.Add(new DepositMap());
        modelBuilder.Configurations.Add(new ExceptionTypeMap());
        modelBuilder.Configurations.Add(new FeeTypeMap());
        modelBuilder.Configurations.Add(new ImportMethodMap());
        modelBuilder.Configurations.Add(new PaymentMethodMap());
        modelBuilder.Configurations.Add(new PaymentTranDetailMap());
        modelBuilder.Configurations.Add(new PpaAdminMap());
        modelBuilder.Configurations.Add(new PreferenceMap());
        modelBuilder.Configurations.Add(new PrefTypeMap());
        modelBuilder.Configurations.Add(new ReturnMap());
        modelBuilder.Configurations.Add(new SessionDataMap());
        modelBuilder.Configurations.Add(new SettleBatchReportMap());
        modelBuilder.Configurations.Add(new SettlementMap());
        modelBuilder.Configurations.Add(new SplitPaymentMap());
        modelBuilder.Configurations.Add(new TransCodeMap());
        modelBuilder.Configurations.Add(new UTLDataMap());
        modelBuilder.Configurations.Add(new VendorMap());
        modelBuilder.Configurations.Add(new VendorRefLookupMap());
        modelBuilder.Configurations.Add(new Missing_Trans_KeyMap());
        modelBuilder.Configurations.Add(new UnmatchedDepositMap());
    }
}

And in SQL, the table is defined as:

USE [PaymentProcessing]
GO

/****** Object:  Table [dbo].[Batch]    Script Date: 11/13/2014 10:30:06 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Batch](
    [BatchID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [VendorID] [numeric](18, 0) NULL,
    [BatchDesc] [varchar](50) NULL,
    [ImportDate] [datetime] NULL,
    [ImportMethodID] [numeric](18, 0) NULL,
    [BatchTotal] [numeric](18, 2) NULL,
    [BatchCount] [numeric](18, 0) NULL,
    [StartDateTime] [datetime] NULL,
    [EndDateTime] [datetime] NULL,
    [UserID] [varchar](50) NULL,
 CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED 
(
    [BatchID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

I don't see any FK constraints. (Saw that a FK caused some problems in another SO question.)

And now, my code:

public Batch InsertNewBatchRecord(int vendorId)
{
    Batch batch;
    using (var uow = new UnitOfWorkPaymentProcessingEf())
    {
        var repVendor = new RepositoryVendor(uow);
        var repBatch = new RepositoryBatch(uow);

        var vendor = repVendor.GetById(vendorId);   // get vendor info...
        var maxId = repBatch.NewId();              // Get next BatchId...

        // Create full description...
        var desc = vendor != null
            ? string.Format("{0}", vendor.VendorLongName)
            : string.Format("Could not find vendor for ID [{0}]", vendorId);
        desc = string.Format("{0} - {1} : {2}", desc, DateTime.Now, maxId);

        // Create new batch record
        batch = new Batch
        {
            //BatchId = maxId,  <------ DOES NOT MATTER IF COMMENTED OUT OR NOT - same error both ways!
            VendorID = vendorId,
            BatchDesc = desc,
            ImportDate = DateTime.Now,
            ImportMethodID = 1,
            UserID = Environment.UserName
        };
        repBatch.Insert(batch);
        uow.Commit();   // <----- THIS FAILS!!!  Internally it calls SaveChanges() only.
    }
    return batch;
}

Any ideas why the error is even being thrown?

Answer 1

sounds like a HasDatabaseGeneratedOption is missing. You should have:

this.Property(t => t.BatchID).
    HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

BTW: an identity on a [decimal|numeric] sounds weird. Usually one use int or bigint.