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

Question

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?

score 2 · Accepted Answer · answered Oct 21 '16 at 15:24

2

You can use this statement with SQL Server Compact:

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

answered Oct 21 '16 at 15:24

ErikEJ

40,951
5
75
115

This is good, but the only problem is that its not irrelevant of the type of database being used. This command fails on SQL Server (non-Compact), with `Incorrect syntax near the keyword IDENTITY.` – Alexandru Oct 21 '16 at 16:03
There is no general way of doing this, unfortunately – ErikEJ Oct 21 '16 at 16:33
I didn't think so, too bad. I guess that I'll just need to `try-catch` both commands and throw some type of an exception if both fail. – Alexandru Oct 21 '16 at 16:36
1

You can also check what database you are using by checking the type of the context.Database.Connection http://erikej.blogspot.dk/2013/06/sql-server-compact-code-snippet-of-week.html – ErikEJ Oct 21 '16 at 18:37

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

1 Answers1