Linq DataContext not recovering after caught exception?

Question

I am using LINQ for the first time, and I am encountering some kind of behavior that I do not quite understand with DataContext. Hope StackOverflow can help.

Basically, it seems that when my DataContext encounters an exception, it either caches this exception, or it never gets completely recovered. In my specific case, when I try to pass a row that is too large for my database, it throws the same exception for all the following rows.

I have a table as such:

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

In .Net, I create a DataContext that I map to this table with the LINQ to SQL class.

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

Then I create a bunch of test rows, iterate over them and send them to the table.

 Dim arr As String() = {"aaaaa", "adsfs", "wefwf", "dfgsfg", "ergsdfg", "fsdgergd", "Sdgdfgegd", "ergdfgsfd"} For Each a As String In arr addToLinq(a) Next 

It works great. However, if I add the next row to index 1, all subsequent inserts fail, throwing the same exception (SqlException) as a long row.

 "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

If you observe the GetChangeSet method of your DataContext, you will notice that it still wants to insert the first row. That is why it does not recover.

Answer 2

Create a new context every time you need it, instead of reusing a member of the global / static context. The problem is that your context object is in poor condition.

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

Answer 3

Actually, it does not work in the same insertion, since you did not delete the row with the error from the data context, and it tries to insert it every time you call SubmitChanges. Since @confusedGeek suggests you get around this, creating a new data context for each insert. Alternatively, you can set ConflictMode to ContinueOnConflict, and I believe that other changes will be inserted. Since you are posting changes in each add, I would select the old solution and create a separate context for each insert.

EDIT . Another alternative is for the exception handler to delete the old context and create a new one on error. I'm not sure that most of the improvement - it seems a little more complicated than necessary. Data contexts are fairly lightweight objects, so I wouldn't worry too much about creating a new one.