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

Question

I have LINQ to SQL that sometimes throws

"Cannot insert duplicate key string in object 'dbo.Table' with unique index 'IX_Indexname`. The application was terminated.

Is there any way to enable logging, or at least debug in the datacontext, to find out that sql will execute when the error occurs?

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

The odd thing about this error is that there is only one update in the change sets, and the only field that changes is the datetime field, which is not in the index causing the error.

Answer 1

An easy 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 working on these type problems, I used the SQL profiler. The profiler basically turns on, placing a breakpoint on save / update, clearing the profiler, and then running only this instruction. From there, I have all the SQL that was executed, and I see what was created. [I basically did this with DataServices, so .SaveChanges () is a very convenient place to place a breakpoint]

Answer 3

Write a test to isolate a piece or pieces of code that cause all problems. Set DataContext.Log = Console.Out. Run the test using the tester (NUnit, MSTest, etc.). Testers typically display everything printed on Console.Out along with test results.

Answer 4

You can use the SQL profiler to view SQL when it clicks on 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 sending it to the server.

Your last point of call is to use the VS 2008 debugging capabilities through the .NET framework.

Answer 5

use SQL profiler. This is your friend and ships with SQL. You can view any executable SQL statements with full filtering control.

Answer 6

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