How to get the TSQL Query from LINQ DataContext.SubmitChanges()

Question

I'm using Linq to SQL. I have a DataContext against which I am .SubmitChanges()'ing. There is an error inserting the identity field, and I'd like to see the query it's using to insert this identity field.

I don't see the query itself within the quickwatch; where can I find it from within the debugger?

Solution 1

There is actually a very simple answer to your question

Just paste this in your watch window

((System.Data.Objects.ObjectQuery)myLinqQueryVar).ToTraceString()

Solution 2

Lots of people have been writing their own "DebugWriter" and attaching it like so:

// Add this class somewhere in your project...
class DebugTextWriter : System.IO.TextWriter {
   public override void Write(char[] buffer, int index, int count) {
       System.Diagnostics.Debug.Write(new String(buffer, index, count));
   }

   public override void Write(string value) {
       System.Diagnostics.Debug.Write(value);
   }

   public override Encoding Encoding {
       get { return System.Text.Encoding.Default; }
   }
}

// Then attach it to the Log property of your DataContext...
myDataContext.Log = new DebugTextWriter()

This will output everything that Linq-to-Sql is doing into Visual Studio's debug window.

Solution 3

Further to Portman's answer, if you're a console application it's as simple as:

myDataContext.Log = Console.Out;

Or you could use something like Linq2SQL Profiler which is a rather excellent tool and in fact the right tool for the job:

Linq to SQL Profiler - Real-time visual debugger for Linq to SQL

Solution 4

FooDataContext dc = new FooDataContext();

StringBuilder sb = new StringBuilder();
dc.Log = new StringWriter(sb);

var result=from r in dc.Tables select d;

.....
string query=sb.ToString();

Solution 5

Run SQL Profiler if you have it. It'll show all traffic to your database, including SQL command text.

View more solutions

Comments

[eglasius]

You can also configure your datacontext to output the queries into a file.

[geofftnz]

SQL Profiler is handy for other reasons too though, eg: seeing the load your code is putting on the DB.

[bobzer]

unfortunately that doesn't always work. For exaple il your query has a groupby...

[bobzer]

the answer depend a lot of your entity framework from the version 4.1 you have the dbcontext which implement context.Log so you can use the solution you see below, but before that if you context heritate from objectcontext you have to do with the solution above except that unfortunable it's not always usable

[Taha Rehman Siddiqui]

the answer was according to the question originally asked