How do I rollback a failed InsertOnSubmit? (VB.NET LINQ)

Question

I'm having a problem with undoing a failed InsertOnSubmit when SubmitChanges fails. Here's the code:

    Dim NewFac As New t_Facility With
            {.FK_Instance_ID = guInstance_ID,
             .FK_AccountType_ID = guAccountType_ID,
             .FK_ATP_ID = guATP_ID,
             .FK_Repayment_ID = guRepaymentType_ID,
             .FK_InterestType_ID = guInterestType_ID,
             .FK_FT_ID = guFacilitiesType_ID,
             .NewRecord = bNewRecord,
             .IndexNum = iIndexNum,
             .SortCode = sSortCode,
             .AccountNumber = sAccountNumber,
             .Balance = decBalance,
             .LastSanction = decLastSanctioned,
             .Proposed = decProposed,
             .Term_MTHs = iTerm_MTHS,
             .Term_Expiry = dTerm_Expiry,
             .InterestRate = decInterestRate,
             .ArrangementFee = decArrangementFee,
             .DateTime_From = Now(),
             .ID = guFacilities_ID}
    db.t_Facilities.InsertOnSubmit(NewFac)
    Try
        db.SubmitChanges()
    Catch e As Exception
        Console.WriteLine(e)
        MessageBox.Show(e.Message & ". Please correct the field and try again", "ERROR", MessageBoxButton.OK, MessageBoxImage.Stop)

        Exit Sub 'Takes the user back to the form to correct the value
    End Try

When they hit submit again, it comes back around and fails at the same point with the same values as the original submission ignoring the new values that the user input.

The values in "NewFac" are the corrected new values. I've checked them on this line manually in debug: "db.t_Facilities.InsertOnSubmit(NewFac)"

I assume i need to somehow remove the failed "NewFac" that contains the incorrect values from "db.t_Facilities.InsertOnSubmit(NewFac)" in the catch somehow, but i don't see a way to do this?

FYI: I got the principal of this approach from here: https://msdn.microsoft.com/en-us/library/bb763516

Any help would be appreciated.

G.Williams · Accepted Answer · 2016-07-22T09:56:43.073

Now this is probably not going to be technically correct, so someone informed; please feel to give the real reason this worked (So its not really based on any statement of fact other than it worked, and is entirely my opinion - but it worked):

The solution dawned on me while reading through several questions from various other people having similar issues (all using various ways of Programmatically looping through the DataContext looking for a match), that the changes are just added the DataContext which appears to effectively act like an offline CLR of the database, so if InsertOnSubmit added to it, it stands to reason DeleteOnSubmit should remove it right?

So with that in mind, i tried this:

db.t_Facilities.InsertOnSubmit(NewFac)
Try
    db.SubmitChanges()
Catch e As Exception
    Console.WriteLine(e)
    MessageBox.Show(e.Message & ". Please correct the field and try again", "ERROR", MessageBoxButton.OK, MessageBoxImage.Stop)

    db.t_Facilities.DeleteOnSubmit(NewFac)

    Exit Sub 'Takes the user back to the form to correct the value
End Try

It worked! :D

So i may be totally wrong as to why it worked (please inform me why - i'd genuinely like to know), but it worked.

EDIT: If someone can give the correct reason it worked, i'll accept their answer instead of my own

This works but it's better design to not reuse DataContexts for different units of work. You will have all kinds of hard to diagnose bugs if you ever forget to clean something up. Also, it's a memory leak because all loaded entities stay in memory. Adopt an architecture where you create one context per UOW. — usr, Jul 22 '16 at 10:10
I'm don't know what UOW means so i don't understand the last part of your comment? I have the DataContext per Window and all fields in the Window are bound to the DataContext. I'm pretty new to WPF and LINQ, so i may have grabbed the wrong end of the stick and run with it, but I'm not sure how else i would do it since the DataContext needs to exist when the Window is open? — G.Williams, Jul 22 '16 at 10:19
Unit of Work = UOW. Yes, context per window is not good. Data binding to entities is not that advisable, either. Microsoft tutorials steer users into that direction for simplicity and wow-effect but it's not appropriate for a real app. — usr, Jul 22 '16 at 14:49

How do I rollback a failed InsertOnSubmit? (VB.NET LINQ)

1 Answers1