Error Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

Ahmed Abd El Aziz 315 Reputation points
2023-08-20T06:42:50.67+00:00

I work with Entity Framework Core 7 and ASP.NET Core using Razor pages. I get an exception when trying to insert data from Entity Framework Core into the SQL Server table ZebraSubPrinters:

SqlException

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

Table ZebraSubPrinters has ID as an identity column

    public class ZebraSubPrinters
    {
        public decimal ID { get; set; }
        public string BranchCode { get; set; }
        public string DisplayName { get; set; }
        public string Category { get; set; }
        public string PrinterName { get; set; }
        public string PrinterIP { get; set; }
        public bool IsActive { get; set; } = true;
    }

Generic repository used for insert and save data

 public virtual TEntity Insert(TEntity entity)
    {
        var result = dbSet.AddAsync(entity).Result.Entity;
   
        return result;
    }

    public void Save()
    {
        try
        {
            _basecontext.SaveChanges();
        }
        catch (Exception ex)
        {    
            throw;
        }    
    }

Function that does the insert and save using generic repository:

public void InsertZebraSubPrinters(ZebraSubPrinters printer)
{ 
    ZebraSubPrinters ZEB = new ZebraSubPrinters();
    ZEB.IsActive = printer.IsActive;
    ZEB.PrinterName = printer.PrinterName;
    ZEB.PrinterIP = printer.PrinterIP;
    ZEB.Category = printer.Category;
    ZEB.DisplayName = printer.DisplayName;
    ZEB.BranchCode = printer.BranchCode;

    aDCSupportUnitOfWork.ZebraSubPrinters.Insert(ZEB);
    aDCSupportUnitOfWork.ZebraSubPrinters.Save();              
}

SQL Server Profiler generated error for insert statement above as below:

exec sp_executesql N'SET IMPLICIT_TRANSACTIONS OFF;

SET NOCOUNT ON;

INSERT INTO [ZebraSubPrinters] ([ID], [BranchCode], [Category], [CreatedBy], [CreatedDate], [DisplayName], [IsActive], [PrinterIP], [PrinterName], [SavedDate], [UpdatedBy], [UpdatedDate])

VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11);

',N'@p0 decimal(18,2),@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 datetime2(7),@p5 nvarchar(4000),@p6 bit,@p7 nvarchar(4000),@p8 nvarchar(4000),@p9 datetime2(7),@p10 nvarchar(4000),@p11 datetime2(7)',@p0=0,@p1=N'10207',@p2=N'Green',@p3=NULL,@p4='0001-01-01 00:00:00',@p5=N'TANTAWY',@p6=1,@p7=NULL,@p8=NULL,@p9='0001-01-01 00:00:00',@p10=NULL,@p11='0001-01-01 00:00:00'

How to solve and prevent this error from happen when inserting?

id column is key and identity 1,1 and I display table with id as

decimal because it exist on database as datatype numeric(18, 0) and

values stored on column id as 1,2,3,4,5 etc.

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,784 questions
ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,599 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Bruce (SqlWork.com) 71,511 Reputation points
    2023-08-20T15:48:14.6933333+00:00

    If you use identity columns, you must configure that column to not be in the insert. If you read the docs it’s pretty clear how to configure autogenerated columns. Add the

    
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    

    to the column definition.

    https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=data-annotations

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.