Entity Framework – View generated SQL queries
11 Mar 2010I’ve created an example that uses the “Orders” and “Order_Details” tables in the Northwind database, to show how you can easily view the SQL queries that are being generated by the Entity Framework. In order to simplify this I’ve created a static class called “ExtensionMethods” which has two extension methods, both named “SqlQuery”.
The code
In the first query I’m retrieving the orders that have been shipped to Norway. In this case it will return the matching orders as IQueryable<Order>, since this table is on the “one” end of the relationship with “Order_Details”.
In the second query I want the order details of order 10248. Since the order details table is on the “many” end of the relationship with “Orders” it will return the order details as EntityCollection<Order_Detail>.
We have to treat these two types differently in order to extract the SQL query generated by the Entity Framework. How this can be done is shown in the 2nd snippet below.
{
using (NorthwindEntities db = new NorthwindEntities())
{
IQueryable<Order> orders = db.Orders
.Where(x => x.ShipCountry == "Norway");
Console.WriteLine("Generated SQL query: " +
Environment.NewLine +
orders.SqlQuery() +
Environment.NewLine);
EntityCollection<Order_Detail> orderDetails = db.Orders
.Where(x => x.OrderID == 10248)
.First()
.Order_Details;
Console.WriteLine("Generated SQL query: " +
Environment.NewLine +
orderDetails.SqlQuery());
}
}
And here are the extension methods I’ve created, which return the generated SQL queries for data of IQueryable<T> and EntityCollection<T>, respectively.
{
public static string SqlQuery<T>(this IQueryable<T> source)
{
ObjectQuery<T> objectQuery = source as ObjectQuery<T>;
if (objectQuery != null)
return objectQuery.ToTraceString();
return string.Empty;
}
public static string SqlQuery<T>(this EntityCollection<T> source) where T : class
{
ObjectQuery<T> objectQuery = source.CreateSourceQuery();
return objectQuery.ToTraceString();
}
}
And here’s the output:
Enjoy!