LINQ to SQL - The database generated a key that is already in use

Question

I have a simple problem that reads an Excel file (using interop) and fills an MSSQL database file with some data extracted from it. That's fine so far. I have a Shops table with the following fields:

• ID: int, auto-generated, auto-sync: on insert
• Name: string
• Settlement: string
• County: string
• Address: string

I read the excel file, and then create a new Shops object and set the Name, Settlement, County and Address properties and I call Shops.InsertOnSubmit() with the new Shops object.

After this I have to reset the database (at least, the table), for which the easiest way I found was to call the DeleteDatabase() method and then call CreateDatabase() again. The problem is, that after the first reset, when I try to fill the table again, I get the exception: The database generated a key that is already in use.

Additionally, from that point on, I'm unable to use that database file, because DatabaseExists() returns FALSE, but when I call the CreateDatabase() method, it throws an exception, that the database already exists (although the data files don't exist).

What am I doing wrong? Thank you very much in advance!

Answer 1

I encountered this error because I was using a custom stored procedure for the insert with a table that had an identity column, but I had forgotten to "SET @Id = SCOPE_IDENTITY" at the end of my sproc.

I wasn't actually using the resulting identity value so the problem only showed up when I inserted two or more rows. Tricky bug.

Answer 2

I encountered this error. I had a log table, with an identity. I was truncating the log, while my application was running. What happened was the DB would start the identity column over again when I truncated, however the data context I was using to log still had objects it was tracking with the same key.

Answer 3

It sounds like you are re-using the data-context beyond what is wise. Try disposing and re-creating the data-context after deleting the database.

I suspect the problem is that the identity manager is still tracking objects (destroying and recreating the database is such an edge-case that I think we can forgive it for not resetting itself here).