Quick tip: how to programmatically monitor the SQL statements your Entity Framework LINQ queries generate

Sometimes, developers ask me, what is the easiest way to monitor the SQL statements generated by your LINQ queries against your Entity Framework data model. If the backend database is SQL Server (as it often is), then I think SQL Server's Profiler is the easiest way to do this.

Although it draws a little on the database's power, usually running a profiling session for just a couple of minutes in a well-selected spot can give you valuable insight into the SQL queries that the database executes. And of course, if you happen to have SQL Server Profiler on your own development machine, nothing stops you from keeping the profiler running extensively.

Sometimes however, you'd like to know the SQL statement generated by a single LINQ query, and not a set of queries. In this case, you can ask for the SQL statement in code, like this:

using System.Data.Objects;
...
string sql = ((ObjectQuery)myLinqQueryResultsObject).ToTraceString();

Here, I'm casting the resulting LINQ object to a class of type ObjectQuery, and then calling the ToTraceString method. Although this is a single-liner, I'd personally prefer an easier approach. But then again, once you know what to ask, the rest is easy.

Here's a complete example of a LINQ query:

NorthwindEntities entities = new NorthwindEntities();
var ordersAndLines = from o in entities.Orders
                     join d in entities.Order_Details on o.OrderID equals d.OrderID
                     join p in entities.Products on d.ProductID equals p.ProductID
                     where o.CustomerID == "ALFKI" & p.ProductID > 30
                     orderby o.OrderID
                     select new { OrderId = o.OrderID, Date = o.OrderDate,
                     OrderLines = o.Order_Details.Count };

And, after the ToTraceString method has been called on "ordersAndLines", the resulting SQL statement is:

SELECT [Project1].[OrderID] AS [OrderID], 
[Project1].[OrderDate] AS [OrderDate], 
[Project1].[C1] AS [C1]
FROM ( SELECT [Filter1].[OrderID1] AS [OrderID], 
	[Filter1].[OrderDate] AS [OrderDate], 
	(SELECT COUNT(1) AS [A1]
		FROM [dbo].[Order Details] AS [Extent4]
		WHERE [Filter1].[OrderID1] = [Extent4].[OrderID]) AS [C1]
	FROM (SELECT [Extent1].[OrderID] AS [OrderID1],
	[Extent1].[OrderDate] AS [OrderDate], [Extent2].[ProductID] AS [ProductID]
		FROM  [dbo].[Orders] AS [Extent1]
		INNER JOIN [dbo].[Order Details] AS [Extent2] ON [Extent1].[OrderID] =
		[Extent2].[OrderID]
		WHERE N'ALFKI' = [Extent1].[CustomerID] ) AS [Filter1]
	INNER JOIN [dbo].[Products] AS [Extent3] ON [Filter1].[ProductID] =
	[Extent3].[ProductID]
	WHERE [Extent3].[ProductID] > 30
)  AS [Project1]
ORDER BY [Project1].[OrderID] ASC

Happy hacking!