Linq to SQL: Ammend command before submission to database

Question

Is there a hook somewhere within Linq to SQL that would allow me to manually modify a command before it is sent to the database?

Specifically, I am trying to introduce the WITH CHANGE_TRACKING_CONTEXT(@contextData) syntax whenever an insert/update/delete is applied to the database in order to be able to add additional information against the change tracking, such as the user who instigated the change (this is a web application that uses SQL Authentication).

This syntax will simply need to be inserted at the beginning of the CommandText, along with an additional parameter (@contextData) being added to the SqlCommand object which will contain the data for the additional statement.

Thanks in advance.

Answer 1

You shouldn't need to alter any SQL... just supply your DataContext with an existing SqlConnection. http://msdn.microsoft.com/en-us/library/bb386986.aspx

Before submitting changes via DataContext.SubmitChanges, create your own SqlCommand (using the same SqlConnection you created your DataContext with) for the WITH CHANGE_TRACKING_CONTEXT command and execute it. That statement will be valid for the duration of the database connection.

UPDATE: Since that didn't work, and if doing change tracking manually isn't an option (via DataContext.GetChangeSet which is what I do on my current app), I think you're going to be stuck doing just about the ugliest thing possible: specify the SQL statements for all of your INSERT/UPDATE/DELETE through partial methods on your DataContext (last section). Or even uglier, use DataContext.Log to log all the SQL generated, execute the SubmitChanges in a Transaction, roll it back, then modify the SQL you get out of the log and execute it as desired.