Is there any way in Linq To SQL to obtain the underlying (raw) SQL happening in a SubmitChanges() call?

Question

I am working on a content management system which is being sort of retrofitted onto an existing database, and the database has many many tables. There will be a staging database, where we will make changes and allow users to 'preview in place'. Then any changes have to be approved, and to publish them we will connect to a live version of the same database (same schema) and play-forward the captured changes.

I have found some code (called Doddle Audit) which, with some customization, is giving me great information about what is changing. I am able to get a list of all columns, before and after, for updates, inserts, and deletes. But what I would really like to have is the underlying SQL being run by SubmitChanges(). LinqToSql has to generate this, so why can't I have it?

I have googled around and looked at code involving SubmitChanges, mousing over stuff, and I can't seem to find it. Does anyone know of a way to obtain this?

Answer 1

Linq To Sql Profiler. It does that and a whole lot more.

Answer 2

You could use SQL Server Profiler, not as good as Linq To SQL Profiler, but free.

To get the sql-statement in a string, you can do something like this:

using (var context = new MyDataContext()) 
{
   var query = from p in context.Persons
             select p;
   string sql = context.GetCommand(query).CommandText;


}

Answer 3

You can try this:

Console.WriteLine(context.GetCommand(query).CommandText);

Hope this helps.

Thanks,

Raja

Answer 4

Use the DataContext.Log property like this:

using(DataContext dc = new DataContext()){
  StringBuilder sb = new StringBuilder();
  dc.Log = new StringWriter(sb);
}

You will see the generated query in debug mode.