How do I find out the actual SQL that this statement generates?

Question

I an using VS2010, .NET4 and EF4. I would like to see the actual SQL that is generated when this is run. Also, what is this the best way to write this statement?

Here is my code:

var cklContactItems = from a in dbTestCenterViews.appvuChecklistExports
                                   where a.MarketChecklistID == MCLID 
                                        && a.checklistSectionID == SID 
                                        && a.fieldGroupOrder != null
                                   orderby a.fieldGroupOrder ascending
                                   select new { a.Column1, a.Column2, a.Column3, a.Column4, a.Column5,a.Column1FieldID,a.Column2FieldID,a.Column3FieldID,a.Column4FieldID,a.Column5FieldID,a.fieldGroupOrderLabel };

Answer 1

Run SQL Profiler

Answer 2

You can Use SQL Server Profiler. LinqPad, and Im sure there are other tools

Answer 3

var query = (from x in context.MyEntity where x... select x);

(query as ObjectQuery<MyEntity>).ToTraceString();

That will print to the trace log... if you want a simple trace viewer (outside of Visual Studio) check out DebugView

And as an additional side note, the best "real time" profiler I have used/seen out there is Entity Framework Profiler, you have to pay for it, but there is a trial version available, and it will give you SQL matched to the line of code. It also recognizes common "issues." Setup is stupid simple... all you have to add is one statement in the initializer of your application.

---

*Edit updated to show how to do it with users code *

I guess i can do all the hard work for you... ;). Since you use an annoynmous type just leave off the <T> part.

var cklContactItems = from a in dbTestCenterViews.appvuChecklistExports
                               where a.MarketChecklistID == MCLID 
                                    && a.checklistSectionID == SID 
                                    && a.fieldGroupOrder != null
                               orderby a.fieldGroupOrder ascending
                               select new {
                                   a.Column1, 
                                   a.Column2, 
                                   a.Column3, 
                                   a.Column4,
                                   a.Column5,
                                   a.Column1FieldID,
                                   a.Column2FieldID,
                                   a.Column3FieldID,
                                   a.Column4FieldID,
                                   a.Column5FieldID,
                                   a.fieldGroupOrderLabel 
                                  };

System.Diagnostics.Trace.WriteLine((query as ObjectQuery).ToTraceString());

Answer 4

You are using VS10 and there is a tool for this called Intellitrace.