EF core "cannot insert explicit value for identity column" when using BYTE as identity column type in Sql Server

Question

I am having trouble seeding data into a SQL database using EF Core during program startup.

I have an EF Core table defined like this:

migrationBuilder.CreateTable(
            name: "PoolStyles",
            columns: table => new
            {
                Id = table.Column<byte>(type: "tinyint", nullable: false)
                    .Annotation("SqlServer:Identity", "1,1"),
                Name = table.Column<string>(type: "nvarchar(256)", maxLength: 256),
                Code = table.Column<string>(type: "nvarchar(40)", maxLength: 40),
                Description = table.Column<string>(type: "nvarchar(MAX)", nullable: true),
                IsRestricted = table.Column<bool>(type: "bit"),
                Priority = table.Column<byte>(type: "tinyint", nullable: true, defaultValue: 0),
                Icon = table.Column<string>(type: "nvarchar(200)", maxLength: 200, nullable: true),
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_PoolStyles", x => x.Id);
            });

and here is the matching entity class:

public class PoolStyle
{
    public byte Id { get; set; }
    public string Name { get; set; }
    public string Code { get; set; }
    public bool IsRestricted { get; set; }
    public byte Priority { get; set; }
    public string Icon { get; set; }
    public string Description { get; set; }
}

During the start up of my application I attempt to seed some data into my database like so:

        if (!context.PoolStyles.Any())
        {
            context.PoolStyles.Add(new PoolStyle { Code = "SRV", Description = "Survival Pool", Icon = "", IsRestricted = false, Name = "Survival" });
            await context.SaveChangesAsync();
        }

However I keep getting the following error:

An error occurred while migrating or seeding the database.

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'PoolStyles' when IDENTITY_INSERT is set to OFF.

The error is preceded by the SQL EF Core tries to run:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102] Failed executing DbCommand (11ms) [Parameters=[@p0='?' (Size = 1) (DbType = Byte), @p1='?' (Size = 4000), @p2='?' (Size = 4000), @p3='?' (Size = 4000), @p4='?' (DbType = Boolean), @p5='?' (Size = 4000), @p6='?' (Size = 1) (DbType = Byte)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; INSERT INTO [PoolStyles] ([Id], [Code], [Description], [Icon], [IsRestricted], [Name], [Priority]) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6);

PROBLEM: you can see that EF Core is adding the [Id] and [Priority] columns to the executed SQL but those values ARE NOT included in the object that I try to pass to the SaveChangesAsync() function.

Also, I am not using annotations so can't use those to solve this problem, I "hand code" the migrations because of the complexity of what I need.

QUESTION: can someone help me figure out why EF Core is trying to add these values to the insert statement?

This may be due to the data type and convention checking? Try adding [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] to the Id field. — Steve Py
EF is assuming Id is an identity column by convention. The easiest solution is to rename the attribute in your class and use the [Column] tag to associate the attribute with the column in your table. — Nicholas Hunter
thanks both nicholas and steve, It ended up being a data type issue and your comments got me going in the right direction. — J King

J King J King · Accepted Answer · 2021-04-21T15:23:14

The issue was using byte as the data type for the identity column. I am not sure why as I know that I can manually create an identity column in sql server with type byte. Something in EF Core is choking.

THE SOLUTION:

I changed the identity column data type to int and the insert works properly with auto incrementing id values.

Maybe I missed something else but its working and these table will not have many records so I will just use int and continue on, haha.

EF core "cannot insert explicit value for identity column" when using BYTE as identity column type in Sql Server

1 Answers

THE SOLUTION: