Exception on submit changes in LINQ to SQL

mangel · April 5th, 2011, 10:53 AM

THIS IS NOT WORKING AFTER ALL. SOMETHING ABOUT ACCESSING A DISPOSED OBJECT. I NEED TO LOOK AT IT MORE.

Hi Imar,

Here is what I did. I use a Try/Catch in all procedures. I put a dc.Dispose() in the Catch block and upon entering each procedure, I have:

Code:

If dc Is Nothing Then
    dc = New PubsDataContext
End If

This is because I would need to re-instantiate dc if I had to dispose of it because of a previous error.

This worked in all cases.

Is this what I should be doing?

Thanks,

mangel

PS: You are an inveterate teacher; I can see why you are a WROX author. I for one appreciate you beyond measure.

Imar · April 5th, 2011, 11:17 AM

Hi mangel,

It probably doesn't work because Dispose() does not set the object reference to Nothing.

In other words, dc is still a valid object (and thus not Nothing) but internally it's been disposed.

I see a few options:

1. In the Catch block, call Dispose and directly create a new instance.

2. Create a wrapper around your context that manages the life time. Here's a quick example:

Code:

 
Public Class ContextWrapper
  Private _dataContext As PlanetWroxDataContext
  Private _isReset As Boolean = False
  Public Sub New()
    _dataContext = New PlanetWroxDataContext()
  End Sub
  Public ReadOnly Property DataContext As PlanetWroxDataContext
    Get
      If _isReset Then
        _dataContext = New PlanetWroxDataContext()
        _isReset = False
      End If
      Return _dataContext
    End Get
  End Property
 
  Public Sub SaveChanges()
    _dataContext.SubmitChanges()
  End Sub
 
  Public Sub ResetChanges()
    _dataContext.Dispose()
    _isReset = True
  End Sub
End Class

You can now instantiate an instance of ContextWrapper and access its DataContext property. You can call ResetChanges which kills the underlying context, but a new one is created when you access the context again. Note: untested code, so make sure you test this before you start using this.

With this code, you don't need to check the object in each method call as now the wrapper will take care of this.

If you go down this route, implementing a Repository pattern may be a better idea: http://www.google.com/#sclient=psy&h...y+unit+of+work

Quote:

PS: You are an inveterate teacher; I can see why you are a WROX author. I for one appreciate you beyond measure.

Thank you!

Imar

mangel · April 5th, 2011, 12:37 PM

Hi Imar,

Thanks again. Before I use your wrapper suggestion and because I tried something else before I received your message and because it appears to be working and because it is a simple change from what I did earlier, let me tell you what it is. I removed the if is nothing code and used the following in the catch block instead.

Code:

dc.Dispose()
dc = New PubsDataContext

I added code to deliberately create exceptions to see if things would recover using this approach. I tried to add a title that had already been added, the exception occurs and then the code above runs. I next update an existing title and it works; so it looks like it recovered from the previous exception. I then try to update a non-existing title and the exception occurs and so I run the code above again. I then delete a title without a problem; so it looks like it recovered from the second exception. The rest of the examples work properly. Is there a gotcha here I am not considering?

Thanks,

mangel

Imar · April 5th, 2011, 12:48 PM

Quote:

The rest of the examples work properly. Is there a gotcha here I am not considering?

Nope, this is exactly what I meant by:

Quote:

In the Catch block, call Dispose and directly create a new instance.

The only "problem" with this code is that it might break the DRY principle. You now have to remember to call Dispose and reinstantiate dc in every Catch block.

By creating a wrapper or a repository, you can centralize this behavior and make things as easy as a single method call.

So, it's mostly a design and maintainability decision, not necessarily a technical decision.

Cheers,

Imar

mangel · April 5th, 2011, 04:23 PM

Hi Imar,

I've not heard of DRY before but I generally try not to repeat myself in code. My intention was to find out if my solution was technically correct and if so I would go on to the next step. I tend to use functions and subs even if only to act as self-commenting code; so I'm a DRY kind of guy. Here is what I did:

Code:

Private Function RefreshPubsDataContext(ByVal dc As DataContext) As PubsDataContext
    dc.Dispose()
    Return New PubsDataContext
End Function

Code:

Private Function RefreshPubsDataContext(ByRef dc As PubsDataContext) As PubsDataContext
    dc.Dispose()
    Return New PubsDataContext
End Function

I can use either of the functions above in the catch block as:

Code:

dc = RefreshPubsDataContext(dc)

But I think that the ByRef version is preferable.

So all I have to remember is to refresh the data context when there are exceptions. This works quite well.

I am now trying implement the wrapper and/or the repository.

Thanks,

mangel

BillS · April 5th, 2011, 07:19 PM

A few things to keep in mind:
1. You don't always need to destroy and recreate your DataContext - you can refresh it: http://msdn.microsoft.com/en-us/libr...t.refresh.aspx just pay attention to which flag you use, typically after an error you'll want to force an update from teh DB.
2. Keeping your data context constantly open isn't always best practice, especially in a multi-user environment. The data context for LINQ-to-SQL uses optomistic locking. This means if another user makes changes in the same table behind you your update may fail.
3. Most importantly consider LINQ-to-SQL to be 'depricated'. It isn't officially, however it's in a maintenance only mode. Note LINQ to datasets, EF, XML etc. are all alive and well, I have an article on SQL Mag discussing the current state of LINQ and in particular LINQ to SQL. The good news is the transition from LINQ to SQL to LINQ to EF is rather painless.