Order of Insert and Delete

[matt ryan]

The order of insert and deletes is not being obeyed when submit changes is executed. For instance deleting a record then inserting a new record with the same unique column value will fail with the following "System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object '...' with unique index '...'. The duplicate key value is (...)."

Found in LinqConnect version 4.4.463.0 targeting SQL Server.

Code: Select all

using (var context = new DataContext())
{
    var foosToDelete = from foo in context.Foos
                       where foo.Id == Id
                       select foo;
    context.Foos.DeleteAllOnSubmit(foosToDelete);

    var fooToInsert = new Foo
        {
            Id = Id
        };
    context.Foos.InsertOnSubmit(fooToInsert);

    context.SubmitChanges();
}

[MariiaI]

We couldn't reproduce this issue in our environment with the latest build of LinqConnect 4.4.463 and SQL Server 2008/2012.

We are sending you a sample project to the e-mail address you have provided in your forum profile. Please check that the letter is not blocked by your mail filter. Please modify our sample project so that the issue could be reproduced and send it back to us, or send us your sample project.
Also, please specify the version of SQL Server you are working with.

[matt ryan]

I have submitted a sample that reproduces the error. It is important that a unique index that is not the primary key exist in the table. That column is the one that will have the issue.

We are using SQL Server 2008.

[MariiaI]

Thank you for the sample project. We have reproduced this issue. We will investigate it and inform you about the results as soon as possible.

[matt ryan]

Great, look forward to your next update. We are having to do multiple submits in a transaction to get around it for now.

[MariiaI]

We are working on this issue and inform you when it is fixed.
The issue is in fact that the auto-generated primary keys are used. We are sending you a sample project with a workaround to the e-mail address you have provided in your forum profile. Please check that the letter is not blocked by your mail filter.

[MariiaI]

The bug related to wrong order of the insert/delete operations on SubmitChanges when the autogenerated primary keys are used is fixed.
New build of LinqConnect 4.4.484 is available!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=29439.