Linq DataContext doesn't recover after a caught exception?

Question

I am using LINQ for the first time and I am running into some behavior I don't fully understand with the DataContext. Hopefully StackOverflow can help.

Basically, it seems like once my DataContext encounters an exception, it either caches that exception or never fully recovers. In my specific case, once I try to pass a string that is too large to my DB, it throws the same exception for all following strings.

I have a table as such:

 DisplayString(
  StringID nvarchar(255)
  ,CultureCode varchar(25)
  ,DisplayString nvarchar(255)
 )

In .Net I create a DataContext which I map to this table with a LINQ to SQL class.

Dim context As New LinqTableMappingDataContext(
  My.Settings.LocalizationStringsConnectionString)

I then create a bunch of test strings, iterate through each and send them to my table.

Dim arr As String() = 
  {"aaaaa",  "adsfs", "wefwf", "dfgsfg", "ergsdfg", 
   "fsdgergd", "Sdgdfgegd", "ergdfgsfd"}

  For Each a As String In arr
     addToLinq(a)
  Next

This works fine. However if I add the following string at index 1, All subsequent inserts fail, throwing the same exception (SqlException) as the long string.

"WARNING: This computer program is protected by copyright law and international
treaties. Unauthorized reproduction of this program, or any portion of it, may
result in severe civil and criminal penalties, and will be prosecuted to the
maximum extent possible under the law."

Here is my AddToLinq() method.

Private Sub AddToLinq(ByVal stringID As String)

  Dim f As New DisplayString
  Try

     Dim count As Integer = (From str In context.DisplayStrings _
       Where str.StringID = stringID Select str).Count

     If count = 0 Then

        f.StringID = stringID
        f.CultureCode = "en-US"
        f.DisplayString = stringID

        context.DisplayStrings.InsertOnSubmit(f)
        context.SubmitChanges()
        Console.WriteLine("SUBMITTED: " & stringID)
    End If

  Catch ex1 As SqlException
     Console.WriteLine("------------------------------------------------")
     Console.WriteLine("EXCEPTION: " & ex1.Message)
     Console.WriteLine(stringID)
     Console.WriteLine(stringID.Length)
     Console.WriteLine("------------------------------------------------")

  Catch ex As DuplicateKeyException
     Console.WriteLine(ex.Message)

  End Try
End Sub

If anyone could help me that would be great, thanks.

Answer 1

Create a new context everytime you need it instead of reusing a global/static context member. The problem is your context object is in a bad state.

....
if (count = 0)
   Dim context As New [Your context here]
   ....
end if

Answer 2

It is actually failing on the same insert as you haven't removed the failing string from the data context and it trys to insert it each time you call SubmitChanges. As @confusedGeek suggests you can get around this by instantiating a new data context for each insert. Alternatively, you could set the ConflictMode to ContinueOnConflict and I believe the rest of the changes would be inserted. Since you are submitting changes on on each add, I would opt for the former solution and create a separate context for each insert.

EDIT: Another alternative would be to have your exception handler dispose the old context and create a new one on error. I'm not sure that's much of an improvement -- seems a little more complex than necessary. Data contexts are pretty lightweight entities so I wouldn't be too concerned about creating a new one as needed.

Answer 3

If you observe the GetChangeSet method of your DataContext, you'll observe that it still wants to insert the first string. This is why it doesn't recover.