Linq2SQL using Update StoredProcedure

Question

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

I connected the Update and Insert behavior in DBML to stored procedures. Procedures are called, data is inserted / updated = everything, if accurate, except for optimistic concurrency .

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

When Linq generates the update request itself, it throws a ChangeConflictException , as expected, but an exception is thrown when using the stored procedure .

Thanks for the help!

Answer 1

When configuring UPDATE behavior to use the update stored procedure, Linq2SQL generates a method that does not throw concurrency exceptions. To deal with concurrency optimism I found a suggested solution on the MSDN forums

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

For this you need:

• write an updated stored procedure that takes current and original values ​​as parameters
  • using WHERE columnA = OriginalValueA ... to update only if the values ​​have not been changed
  • Last line of stored procedure RETURN @@ROWCOUNT
  • rowcount allows you to see if the row has been updated or not.
• in DBML, set the Update option to Use Runtime
• The partial xxxDataContext class implements the Update method as follows:
  • the code is taken from what is generated by Linq2SQL, only the last line is added to throw an exception.

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

Work, but not direct. Any other options?

Answer 2

This does not surprise me. When you call a stored procedure to perform your updates and deletes, you will have to do your own concurrency check. L2S will not be able to do this because you basically cut L2S out of the loop using stored procs.

This is one of the reasons why we do not use any stored procedures using L2S-back-end when performing insertions / updates / deletes. Another disadvantage of using stored procedures is that you lose strong type checking in your insert / update / delete statements.