How is rolling back transaction related to LINQ to SQL?

Question

The question is solely about rolling back the changes, not commiting.

Let's say I fetch some data, I change them, I submit changes (optional step) and I roll back transaction. Wherever you look every author writes, this cancels the changes.

But I found out that is half true -- LINQ DataContext will keep the changed data! I tested this using TransactionScope and DataContext.Transaction. In both cases I got the same behaviour.

A workaround would be to recreate DataContext after roll back (however this leads to other problems like caching data and handling nested transactions) or manually discarding the changes in DataContext. Nevertheless those are just workarounds.

Questions

So what am I missing? Is LINQ to SQL not suited for transactions? How to use transactions so they would REALLY roll back changes?

Example

                MyTable record = null;

                db.Connection.Open();
                using (db.Transaction = db.Connection.BeginTransaction())
                {
                        record = db.MyTable.First();
                        record.BoolField = !record.BoolField; // changed
                        db.SubmitChanges();
                        db.Transaction.Rollback();
                }

Answer 1

A data-context should be considered as a unit-of-work. How granular you make that is up to you - it could be a page request, or a single operation; but - if you get an exception (or pretty much anything unexpected) - stop; abandon the data-context and rollback. After a rollback, your data-context is going to be confused, so just don't keep it.

Additionally; don't keep a data-context for longer than necessary. It is not intended as an app-long data cache.

Answer 2

What you seem to be asking for is an in-memory cache of the database (or some part of it) rather than a lightweight ORM. I would say that LINQ to SQL is just fine for transactions and as a lightweight ORM, but not so good to use out of the box as a database cache. The data context functions best, in my opinion, using the Unit of Work pattern. Create the context for a particular task, perform the task, then dispose of the context. If the task happens to include a failed transaction, then you need to figure out how to respond to the failure. This could be by either correcting the errors and retrying with the existing context or, as in a web context, passing back the attempted changes to the user, then trying again with a new context when the data is resubmitted.