Linq2SQL using Update StoredProcedure

Question

We use queries generated by Linq for data retrieval but for INSERT and UPDATE we do not allow generated SQL, but restrict to the use of stored procedures.

I connected the Update and the Insert behaviour in the DBML to the stored procedures. The procedures are called, the data gets inserted/updated = all if fine, except in the case of optimistic concurrency.

If a record was changed between retrieval and update, the update should fail.

When Linq generates the Update statement itself, it throws a ChangeConflictException as expected, but using the stored procedure no Exception is thrown.

Thanks a lot for any help on this!

Answer 1

This doesn't surprise me. When calling a stored procedure to do your updates and deletes, you are going to have to do your own concurrency checking. L2S won't be able to do it because you've basically cut L2S out of the loop by using stored procs.

This is one reason why we don't use any stored procedures with out L2S back-end when doing inserts/updates/deletes. Another disadvantage of using stored procedures is you lose strong type checking on your insert/update/delete statements.

Answer 2

When configuring the UPDATE behavior to use the update stored procedure, Linq2SQL generates a method that is not throwing concurrency exceptions. To handle optimistic concurrency I found a proposed solution in the MSDN forums

You can implement the Update method yourself in the patial DataContext class and throw a ChangeConflictException.

To achieve this you have to:

• write an Update stored procedure which takes the current and original values as parameters
  • using WHERE columnA = OriginalValueA ... to update only if the values were not changed
  • the last line in the stored procedure is RETURN @@ROWCOUNT
  • the rowcount lets you see if the row was updated or not
• in the DBML set the Update behaviour to "use runtime"
• the partial class xxxDataContext implement an Update method like this:
  • the code is taken from what Linq2SQL generates, just the last line to throw the exception is added

partial void UpdateYourEntityClass(YourEntityClass obj)
        {
            EntityClass original = ((YourEntityClass)(EntityClasss.GetOriginalEntityState(obj)));
            int result = this.YourEntityClassUpdate((...));
            if (result == 0) throw new ChangeConflictException();
        }

Working, but not straight forward. Any other options?