0

My application supports SQL Server and Compact edition. I use EntityFramework as the data access layer. To allow the users to go from Compact to SQL Server and back to Compact, I have a method to copy the data from one database to the other. This works fine from Compact to SQL Server, but when I use the same code to copy from SQL Server to Compact I get exceptions telling me that I cannot add a row with duplicate Id after an object was added to the EF data context and calling context.SaveChanges().

Here is how I copy the tables:

  1. I let EF create the destination database
  2. Open a SqlConnection(SqlCeConnection to source DB and destination DB
  3. Foreach table I:
    • On the destination database SET IDENTITY_INSERT [Tablename] ON
    • Copy the table row by row using Sql inserts, including the ID's
    • On the destination database SET IDENTITY_INSERT [Tablename] OFF

So after I copy the database from SQL Server to Compact and using it with Entity Framework I get the above mentioned exception. So it looks like SET IDENTITY_INSERT ON works (because the data gets into the Compact database) but SET IDENTITY_INSERT OFF does not work on SQL Compact.

Does anyone has similar experiences and a solution for me?

Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
unkreativ
  • 482
  • 2
  • 8
  • You need to reseed ... http://stackoverflow.com/questions/15271143/reseed-identity-column-in-sql-compact – ErikEJ Mar 09 '16 at 19:28
  • Thanks Erik, but using your very cool tool and executing your SQL statement I get the error: "The column cannot become an identity column after it is created" – unkreativ Mar 09 '16 at 21:17
  • Sounds like it is not an identity column then – ErikEJ Mar 10 '16 at 08:51

2 Answers2

1

If you are getting an error that it is trying to add a duplicate key, then the issue you are having doesn't have anything to do with the IDENTITY_INSERT setting. The identity insert only persists during your session.

What is probably happening is that you have inserted key values that are above the current identity seed values. You can reseed your table's identity seed with this command in SQL Server:

DBCC CHECKIDENT (MyTable, reseed)

You can execute this in Entity Framwork with:

context.Database.ExecuteSqlCommand("DBCC CHECKIDENT(MyTable, reseed)")

This will reset the seed to start numbering after the last value used in the table. Run this statement after populating every destination table.

Brian Pressler
  • 6,653
  • 2
  • 19
  • 40
  • Just to be sure: The error mentioned is raised in my Application *after* the database has been copied and when I start to use it in the application. At this point the data is Ok and I have no duplicate keys. The point is, this error only comes up after I copied the database from the server. And the Id is handled by EF, I'm never setting my own Id's – unkreativ Mar 09 '16 at 21:20
  • Unfortunately the compact edition does not suppport the DBCC command – unkreativ Mar 10 '16 at 10:41
0

Ok, ErikEJ tip brought me to the right direction. The final resolution was to

a) get the current max Id

SELECT MAX(Id) FROM TABLENAME

b) Reseeding but with MAX + 1

ALTER TABLE [TABLENAME] ALTER COLUMN [Id] IDENTITY (<MAX + 1>,1)
unkreativ
  • 482
  • 2
  • 8