How can you see the sql that is causing an error on SubmitChanges in LINQ to SQL?

Question

I've got some LINQ to SQL that sometimes throws a

"Cannot insert duplicate key row in object 'dbo.Table' with unique index 'IX_Indexname'.The statement has been terminated."

Is there some way I can turn on logging or at least debug into the datacontext to see what sql is being executed at the time that error is raised?

Update: I should have mentioned I know about the GetChangeSet() method, I was wondering if there is a property on the DataContext that shows the last SQL that was executed, or a property on the sql exception that shows the SQL.

The odd thing about this error is that in the change sets, there is only one update & the only field that's changing is a datetime field that isn't in the index that causing the error.

Answer 1

A simple way to do this is to use the DataContext.Log property:

using (MyDataContext ctx = new MyDataContext())
{
    StringWriter sw = new StringWriter();
    ctx.Log = sw;

    // execute some LINQ to SQL operations...

    string sql = sw.ToString();
    // put a breakpoint here, log it to a file, etc...
}   

Answer 2

When running into these type issues I've been using the SQL profiler. Basically turning on the profiler, putting a break point on the save/update, clearing the profiler and then running just that statement. From there I have all the SQL that was executed and I can see what was generated. [I've mostly been doing this through DataServices so the .SaveChanges() is a very convenient location to put the breakpoint]