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.
NorthwindEntities ctx = new NorthwindEntities();
var query= from c in ctx.Customers
where c.Orders.Any(o => o.Freight > 1000)
var myQuery = (ObjectQuery)query;
GridView1.DataSource = query;
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.