Keeping a "reference" to a row object for a long time

Question

In my application I keep a LINQ object fetched from the database, and regularly modifies it followed by db.SubmitChanges().

I have read that you are not supposed to keep around the DataContext object for a long time. But if I close my DataContext between modifications, I guess the data object loses its link to the actual database row.

What is the best alternative to keeping the DataContext open? Do I really have to reload the row every time I want to update it?

Answer 1

Reloading the row when you want to update it shouldn't be much of a performance hit, and it will guarantee that you are updating the latest version of the row (in case another user/process has modified it since you first opened it).

If you know nothing else will be updating the row, then you could probably keep the DataContext around without much of a problem (barring a loss of connection to the database).

Answer 2

otherwise you could execute a direct SQL query against the database to make the update as quick as possible.

Due to concurrency issues the default Linq UPDATE statement is not be most optimal due to excessive compares. take a look in SQL profiler or use the Linq SQL visualizer.

//andy