Not working IDENTITY_INSERT in SQL Server Compact with Entity Framework 6.4

Question

I want to add a record with a specific ID into the database, but when I write and run this code, the line created starts with the number one. My ID is identity and I disable identity insert with the following code:

Db = new Entitites();

Db.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT CarName ON");

Db.CarName.Add(new MrSanadBaseDb.CarName()
   {
        PK_Car = 120,
        Name = "",
        IsDeleted = false
   });

Db.SaveChanges();
Db.Database.ExecuteSqlCommand(@"SET IDENTITY_INSERT CarName OFF;");
Db.Database.ExecuteSqlCommand(@"ALTER TABLE CarName ALTER COLUMN PK_Car IDENTITY (121,1);");

But the record is not saved with ID = 120, the record is saved with ID = 1.

I work with SQL Server Compact Edition (SQL Server CE).

Answer 1

Identity insert is session (connection-open) scoped. Did you create the db-context with an already open connection? Or did you give it a connection string, or a closed connection? If it is opening the connection per operation: there is no session continuity, so each if the three operations is semantically unrelated, and do not share a session.

Based on that default constructor, I think it is using the "lookup default connection string, use deferred connection" approach. Try using the constructor that takes a connection instead, and make sure it is open first.

Specifically:

using var conn = // ... create connection
conn.Open();
Db = new Entitites(conn);
// ... the rest of your code