Wooley's LINQ Wonderings

Insights and observations regarding LINQ

Using Stored Procedures with LINQ with concurrency checking

When submitting data to the database, you need to watch out for concurrency issues (times when different users make simultaneous changes to the same record). To do this, in a disconnected environment, we either need to combine a ID and a TimeStamp column or pass all of the original values to the database as well as the changed values.

When we issue the update statement, we include a check for the original values in the Where clause along with the new values in the Set clause. Below is a sample stored proc which updates an Author table with concurrency checking.

 PROCEDURE [dbo].[UpdateAuthorWithoutTimestamp]
 @ID UniqueIdentifier output,
 @LastName varchar(50),
 @FirstName varchar(50),
 @WebSite varchar(200),
 @OriginalLastName varchar(50),
 @OriginalFirstName varchar(50),
 @OriginalWebSite varchar(200),
 @UserName varchar(50)
AS

-- Save values
UPDATE dbo.Author
SET 
LastName=@LastName,
 
FirstName=@FirstName,
 
WebSite=@WebSite
WHERE ID=@ID AND
 
LastName=@OriginalLastName AND
 
FirstName=@OriginalFirstName AND
 
WebSite=@OriginalWebSite

RETURN @@RowCount

In this case, if another user made a conflicting change, the return value will be 0 because no records match the original values any more.

With the stored proc set-up, we can now turn our focus to the LINQ implementation. With Beta 1, LINQ checked for methods in the DataContext which followed the following signature:

Private Sub OnTUpdate(byval original as T, byval changed as T)

In Beta 2, this signature was changed to:

Private Sub TUpdate(byval instance as T)

Thus if we want to use our stored procedure in place of the runtime supplied update implementation (for security/auditing/etc purposes), we simply need to define our procedure in a separate Partial data context class as follows:

Private Sub AuthorUpdate(ByVal instance as Author)

In this method, we can call our stored procedure. If we were leveraging the TimeStamp, we would have been fine. We are not using a timestamp on this table. Our stored procedure requires us to know about the original values. Due to the change between beta1 and beta2, we no longer have a handle on the original author instance that we need for concurrency check.

Scott Guthrie pointed me to a helper method on the Table entity type which can grab the original value from an instance: GetOriginalEntityState. With this, we can find the original value and then call our stored procedure method passing these original values. Here is the implementation:

        Private Sub UpdateAuthor(ByVal instance As Author)
            Dim original As Author = Authors.GetOriginalEntityState(instance)
            Me.UpdateAuthorWithoutTimestamp( _
                instance.ID, instance.LastName, instance.FirstName, instance.WebSite, _
                original.LastName, original.FirstName, original.WebSite, _
                System.Threading.Thread.CurrentPrincipal.Identity.Name)
        End Sub

        <System.Data.Linq.Mapping.Function(Name:="dbo.UpdateAuthorWithoutTimestamp")> _
        Public Function UpdateAuthorWithoutTimestamp( _
            <Parameter(Name:="ID")> ByVal iD As Guid?, _
            <Parameter(Name:="LastName")> ByVal lastName As String, _
            <Parameter(Name:="FirstName")> ByVal firstName As String, _
            <Parameter(Name:="WebSite")> ByVal webSite As String, _
            <Parameter(Name:="OriginalLastName")> ByVal originalLastName As String, _
            <Parameter(Name:="OriginalFirstName")> ByVal originalFirstName As String, _
            <Parameter(Name:="OriginalWebSite")> ByVal originalWebSite As String, _
            <Parameter(Name:="UserName")> ByVal userName As String) As Integer
            Dim result As IExecuteResult = Me.ExecuteMethodCall( _
                            Me, _
                          DirectCast(MethodInfo.GetCurrentMethod(), MethodInfo), _
                            iD, lastName, firstName, webSite, _
                            originalLastName, originalFirstName, originalWebSite, userName)
            iD = DirectCast(result.GetParameterValue(0), Guid)
            Return DirectCast(result.ReturnValue, Integer)
        End Function

Authors.GetOriginalEntityState(instance) gives us our missing information and we can now perform our concurrency handling again.

Crossposted from http://devauthority.com/blogs/jwooley/default.aspx
Published Monday, August 27, 2007 2:09 AM by jwooley
Anonymous comments are disabled