.NET Tips and Tricks

Blog archive

Seeing the SQL Generated by LINQ to Entity Queries

Sometimes you really, really, really want to see the SQL that LINQ generates when working with the Entity Framework. For instance, in a comment to a recent tip, a reader mentioned that a LINQ to Entities query generated a SQL statement that joined more than 40 tables. The application's performance was, to say the least, disappointing. If you're not getting what you expect from your LINQ queries, being able to see the SQL statement can give you real clues in tracking down the problem, as it did with that column's reader.

There are a couple of free Visual Studio Add-ins that, among their other features, allow you to see the SQL statement for a LINQ query (The LINQ to Entity Query Visualizer, Entity Visualizer). However, developers have had trouble getting those add-ins up and running in some versions of Visual Studio (especially Visual Studio 2010). Fortunately, if all you want to do is see your SQL statement, you can do it in Visual Studio's debug mode using a little code in the Immediate Window.

The first step, of course, is have a program with a LINQ query as in this example:

 Dim en As New northwndEntities
Dim res = From cust In en.Customers
Select cust

If you cast the variable holding the output from the query (res, in this case) to Objects.ObjectQuery then you get access to the ObjectQuery's ToTraceString property. That property returns the SQL statement that will be issued by your Entity Framework Model. In Visual Basic, you'd type this in the Immediate window:

 CType(res,  Objects.ObjectQuery).ToTraceString()

In C#, you'd type this:

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

For my sample query, the result looked like this in Visual Basic (the C# version includes escape characters for newline and carriage returns):

SELECT   [Extent1].[CustomerID] AS [CustomerID],   
[Extent1].[CompanyName] AS [CompanyName], 
[Extent1].[ContactName] AS [ContactName],
[Extent1].[ContactTitle] AS [ContactTitle],
[Extent1].[Address] AS [Address], 
[Extent1].[City] AS [City], 
[Extent1].[Region] AS [Region], 
[Extent1].[PostalCode] AS [PostalCode],
[Extent1].[Country] AS [Country],  
[Extent1].[Phone] AS [Phone], 
[Extent1].[Fax] AS [Fax], 
[Extent1].[Timestamp] AS [Timestamp]
FROM [dbo].[Customers] AS [Extent1]

Posted by Peter Vogel on 11/03/2011 at 1:16 PM


comments powered by Disqus

Featured

  • ML.NET Improves Object Detection

    Microsoft improved the object detection capabilities of its ML.NET machine learning framework for .NET developers, adding the ability to train custom models with Model Builder in Visual Studio.

  • More Improvements for VS Code's New Python Language Server

    Microsoft announced more improvements for the new Python language server for Visual Studio Code, Pylance, specializing in rich type information.

  • Death of the Dev Machine?

    Here's a takeaway from this week's Ignite 2020 event: An advanced Azure cloud portends the death of the traditional, high-powered dev machine packed with computing, memory and storage components.

  • COVID-19 Is Ignite 2020's Elephant in the Room: 'Frankly, It Sucks'

    As in all things of our new reality, there was no escaping the drastic changes in routine caused by the COVID-19 pandemic during Microsoft's big Ignite 2020 developer/IT pro conference, this week shifted to an online-only event after drawing tens of thousands of in-person attendees in years past.

  • Visual Studio 2019 v16.8 Preview Update Adds Codespaces

    To coincide with the Microsoft Ignite 2020 IT pro/developer event, the Visual Studio dev team shipped a new update, Visual Studio 2019 v16.8 Preview 3.1, with the main attraction being support for cloud-hosted Codespaces, now in a limited beta.

Upcoming Events