I have been teaching ASP.Net and EF in one of my classes and I have been asked on the various ways we can profile database activity. Everyone that I know that uses EF as its data access layer has the same question.

"How can I see the T-SQL code that the LINQ to Entities engine generates on the fly?"

I know a lot of people use VS studio built-in visualisers but that is not enough. A lot of developers use SQL Server Profiler. That is also a good solution since we can see the queries(generated from the EF enfgine) executed against our data store. In this post I will show you how to display the actual query in the .aspx page.More posts will follow on EF queries database profiling.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item in your site. Add a ADO.Net Entity Data Model and name it Northwind.edmx.Place this file in the App_Code special folder.

3) From the wizard choose (create model from database) and select only the Customers and Orders table to be included in our model.

4)  Add a new item to your site, a web form. Leave the default name. Drag and drop a Gridview control on the form.

In the Page_Load event handling routine of the Default.aspx type

   NorthwindEntities ctx = new NorthwindEntities();
       
 
        var query= from c in ctx.Customers
                        where c.Orders.Any(o => o.Freight > 1000)
                        select c;
 
        var myQuery = (ObjectQuery)query;
            
         
      
        Response.Write(myQuery.ToTraceString());
 
        Response.Write("<hr/>");
 
        GridView1.DataSource = query;
 
        GridView1.DataBind();

 5)  Run your application and see the results.You will see the results in the gridview and also the generated query.I am using the ObjectQuery.ToTraceString method that returns the commands executed against the data store.IN this case the query executed against the datasource will be something like this:

 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] FROM [dbo].[Customers] AS [Extent1] WHERE EXISTS (SELECT 1 AS [C1] FROM [dbo].[Orders] AS [Extent2] WHERE ([Extent1].[CustomerID] = [Extent2].[CustomerID]) AND ([Extent2].[Freight] > cast(1000 as decimal(18))) )

Email me if you want the source code.

Hope it helps.