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?
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.
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.
User contributions licensed under CC BY-SA 3.0