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

0

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?

c#
sql-server
entity-framework
entity-framework-core
asked on Stack Overflow Apr 20, 2021 by J King • edited Apr 21, 2021 by J King

1 Answer

0

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.

answered on Stack Overflow Apr 21, 2021 by J King

User contributions licensed under CC BY-SA 3.0