LINQ to SQL - The database generated a key that is already in use
c#, linq-to-sql, sql-server
Solution
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).
Problem
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!