Linq-to-SQL: Use a shared transaction between LinqDataSource and DataContext?

I have a page where I use two ASP DetailsView controls to render data from two different, but related, LinqDataSource objects. My primary table is represented in the first DetailsView/LinqDataSource set, and I use the LinqDataSource's built-in update functionality to persist changes when the DetailsView is in edit mode. The primary table here represents a "Contact" entity (i.e. a person or organization and their contact info).

The second DetailsView renders data fr开发者_JAVA技巧om a cross-reference table that associates a Contact with one or more "tags". The tag definitions come from another table, so the cross reference table just associates a Contact ID with a Tag ID; standard many-to-many type scenario.

I have updates to the Tag Xref working properly; when tags are added or removed from a contact, the appropriate rows are inserted or deleted from the Xref table. This is accomplished not via the LinqDataSource, but by handling the ItemUpdating event for the tags DetailsView and using a DataContext to manually process the inserts and deletes.

So the Contact and the Contact's tags can be updated individually. Trouble is, I need updates to Contact and to the Tags Xref to happen together in a single transaction. I have the two DetailsView controls linked so that when the user clicks "Update" for the first control, it triggers an update on the second. But the first DetailsView uses its bound LinqDataSource to do the update, which has its own DataContext. Attempts to "share" the DataContext between my manual update and the LinqDataSource result in ObjectDisposed exceptions.

I feel I'm missing something obvious. Sharing DataContext objects seems like a slippery slope. Is there a way I can run a transaction on the page that would enclose both operations?

You've got the right approach. Create one DataContext object sometime earlier in the page lifecycle (I typically use the constructor). Add a ContextCreating handler to both LinqDataSources. In the handler, assign your DataContext to the event's ObjectInstance property (see also: this answer). Create the transaction in, say, the ItemUpdating event:

myDataContext.Connection.Open();
myDataContext.Transaction = myDataContext.Connection.BeginTransaction();

Now here comes the tricky step—where you were getting tripped up before. By default, the LinqDataSource will dispose of its DataContext when it finishes submitting changes. You need to override this behavior by adding a ContextDisposing event handler. In the handler, set the event's Cancel property to true.

Now after the work is complete, you can either commit or rollback the transaction. (Don't forget to dispose of the transaction.) Finally, you can dispose of the DataContext in Page_Unload.

P.S. If you're using DynamicData, be aware that the DynamicDataManager adds its own handler to ContextCreating that creates a new DataContext, thereby avoiding your explicitly created transaction. Annoying.

Alternatively, you can use TransactionScope. Simply create a new object before the work needs to be done, then call Complete when the work is done. There's way too much magic going on there for my tastes though. I prefer to use the more explicit DataContext transactions when I can.