Alexandru Alexandru - 1 month ago 13
C# Question

Seed primary key in Entity Framework code first to 0 in SQL Server Compact Edition

I have this Entity Framework code first class:

public class ClientEvent
{
[Key]
public int? EventCode { get; set; }
public string Description { get; set; }
}


With this seed function for SQL Server Compact Edition:

protected override void Seed(ClientEventsContext context)
{
var clientEvent = new ClientEvent
{
EventCode = 0,
Description = "Test"
};
context.Events.Add(clientEvent);
base.Seed(context);
}


When I check the database table for ClientEvent after it runs, I see this entry:

EventCode | Description
---------------------------
1 | Test


Notice how the EventCode is 1? I would expect it to be 0. How can I seed this primary key to start at 0? I have tried using the code above, but even utilizing 0 itself sets the first entry to 1.

There is another question similar to this which works for SQL Server, but not SQL Server Compact Edition. I have tried using its suggested answer to set the initializer to 0:

context.Database.ExecuteSqlCommand($"DBCC CHECKIDENT ('ClientEvents', RESEED, 0)");


The problem with this is:


System.Data.SqlServerCe.SqlCeException: There was an error parsing the
query. [ Token line number = 1,Token line offset = 1,Token in error =
DBCC ]


Is there not a standard way of doing this with Entity Framework code first, irrelevant of the type of database being used?

Answer

You can use this statement with SQL Server Compact:

ALTER TABLE [MyTable] ALTER COLUMN [Id] IDENTITY (0,1)