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

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]

Answer 2

You could use SQL profiler to view the SQL as it's hitting the SQL server.

If you want to see what is actually in the change sets you need to use:

context.GetChangedSet();

MSDN - http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.getchangeset.aspx

You can then view each SQL statement before it is sent to the server.

Your last point of call is to use VS 2008's ability to debug through the .NET framework.

Answer 3

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 4

Write a test to isolate the piece or pieces of code causing all the trouble. Set DataContext.Log = Console.Out. Run the test with a testrunner (NUnit, MSTest, etc). The testrunners typically display anything printed to Console.Out along with the test results.

Answer 5

use SQL Profiler. It is your friend and ships with SQL. you can view any SQL statements that are being executed, with complete control over filtering.

Answer 6

I have to agree with Bradley Grainger using the DataContext.Log property is the best way to see the executed sql.