LINQ to SQL - How do stored procedures interact with unsubmited datacontext changes?

Question

Someone here asked:

"Linq-To-Sql enables calling SPs. If this SP executes an update/delete/insert, do I need to SubmitChanges() after it?"

And the answer was:

"No you don't. The code will work. Submit changes is only concerned with modified LINQ to SQL objects and not stored procs."

I would just like to clarify:

(Please excuse me, I'm relatively new to LINQ)

Yes, but won't a stored procedure bypass the datacontext object and modify the database itself? (whereas linq modifies the datacontext object only, until the datacontext.submitchanges method is called, where it then modifies the database)

Can I have a stored procedure modify my datacontext object somehow? Does it already do this?

Can I have it so that

• if I call a SP with linq (and I'm assuming that it directly modifies the database),
• then use linq to modify my datacontext object, and submit the changes I made to my datacontext object,

the two different changes I made (One with the SP, the other with Linq to my datacontext) won't interfere?

What I really want is a way to have my stored procedures modify my datacontext. Is this possible?

I should mention that I'm converting a website from SQL to LINQ that has mainly stored procedures and trying to pick my battles with which stored procedures to convert to LINQ and which to keep in SQL and just make SP calls to them with LINQ.

Thank you for your help, -Jeff

Answer 1

FWIW, our litmus test is any trivial 'crud' type PROC which inserts / updates a single record can move across to an ORM

However, we tend to leave the more advanced procs (batch updates, or highly performance sensitive ones) 'as is'.

But yes, you will need to have called SubmitChanges() before calling a Proc which is dependent on the state of data changed in the DataContext

Possibly unrelated, if you need to control units of work, look at putting TransactionScopes around your code?

Answer 2

If you update records via stored proc, your loaded (and tracked) objects may become stale. If you call SubmitChanges with stale objects, you'll get concurrency exceptions. You can refresh a stale object using the Refresh method on DataContext.