LINQ: Cannot insert duplicate key row in object 'dbo.tblOutstandingCompletions' with unique index

Question

Hello I have an application (ASP.NET 3.5) that allows users to rerun a particular process if required. The process inserts records into an MS SQL table. I have the insert in a Try / Catch and ignore the catch if a record already exists (the error in the Title would be valid). This worked perfectly using ADO but after I conveted to LINQ I noticed an interesting thing. If on a re-run of the process there was already records in the table, any new records would be rejected with the same error even though there was no existing record. The code is as follows:

            Dim ins = New tblOutstandingCompletion
            With ins
                .ControlID = rec.ControlID
                .PersonID = rec.peopleID
                .RequiredDate = rec.NextDue
                .RiskNumber = 0
                .recordType = "PC"
                .TreatmentID = 0
            End With

            Try
                ldb.tblOutstandingCompletions.InsertOnSubmit(ins)
                ldb.SubmitChanges()
            Catch ex As Exception
                ' An attempt to load a duplicate record will fail
            End Try

The DataContext for database was set during Page Load .

I resolved the problem by redefining the DataContext before each insert:

        ldb = New CaRMSDataContext(sessionHandler.connection.ToString)
        Dim ins = New tblOutstandingCompletion

While I have solved the problem I would like to know if anyone can explain it. Without the DataContext redefinition the application works perfectly if there are no duplicate records.

Regards James

Answer 1

It sounds like the DataContext thinks the record was inserted the first time, so if you don't redefine the context, it rejects the second insert because it "knows" the record is already there. Redefining the context forces it to actually check the database to see if it's there, which it isn't. That's LINQ trying to save a round trip to the database. Creating a new context as you've done forces it to reset what it "knows" about the database.

Answer 2

I had seen a very similar issue in my code were the identity column wasn't an autoincrementing int column, but a GUID with a default value of newguid() - basically LINQ wasn't allowing the database to create the GUID, but inserting Guid.Empty instead, and the second (or later) attempts would (correctly) throw this error.

I ended up ensuring that I generated a new GUID myself during the insert. More details can be seen here: http://www.doodle.co.uk/Blogs/2007/09/18/playing-with-linq-in-winforms.aspx

This allowed me to insert multiple records with the same DataContext.

Also, have you tried calling InsertOnSubmit multiple times (once for each new record) but only calling SubmitChanges once?