0

I deleted a record by using ExecuteCommand between Linq to SQL insertions as below.

At the last call of SubmitChanges, I got below error :

Cannot add an entity with a key that is already in use.

If I use Linq to SQL for deleting, I get no problem, but ExecuteCommand is a released code, so I can't change it.

How should I insert deleted record again ?

Below is my Code :

Using db = New SomeDataContext(...)

    db.Connection.Open()
    db.Transaction = db.Connection.BeginTransaction()

    ' Insert
    db.Student.InsertOnSubmit(New Student() With {.Id = 1, .Name = "a"})
    db.SubmitChanges()

    ' Delete(ExecuteCommand)
    db.ExecuteCommand("delete from Student where Id = 1")
    db.SubmitChanges()

    '' Delete(Linq to SQL)
    'db.Student.DeleteOnSubmit(db.Student.Single(Function(m) m.Id = 1))
    'db.SubmitChanges()

    ' Insert
    db.Student.InsertOnSubmit(New Student() With {.Id = 1, .Name = "a"})
    db.SubmitChanges()
End Using

I am using Visual Studio 2013 with SQL Server access.

LuFFy
  • 8,799
  • 10
  • 41
  • 59
Yoshiro
  • 1
  • I don't use LINQ to SQL but I think I know what's going on. `ExecuteCommand` acts directly on the database and bypasses the L2S context. That means that, even though you're deleting a record in the database, you aren't removing the corresponding object from the context. `SubmitChanges` will commit changes from the context to the database but not vice versa. The question is why you would be mixing and matching like that. You should stick with one or the other and you avoid that sort of issue. You'd need to refresh the context from the database, which I don't know how to do. – jmcilhinney Nov 24 '17 at 04:41
  • Thanks. Yes it's not vice versa. Actually my question is how to refresh the context. I've been trying to these ways but, results are all the same. db.Refresh(Data.Linq.RefreshMode.OverwriteCurrentValues, ....). Linq deletion is so slow, so if I can't refresh my context, I need to change all codes to raw SQLs. – Yoshiro Nov 24 '17 at 04:48
  • "Actually my question is how to refresh the context". That's interesting because I don't see that anywhere in what you posted. It's probably a good idea to actually post the question you want an answer to. – jmcilhinney Nov 24 '17 at 04:49

0 Answers0