How to Get the Native SQL from an ObjectQuery
The way to get the native SQL generated for an ObjectQuery is exactly the same as for EntityCommand – using the new ToTraceString() method introduced in Beta 3. Previously that would require a similar (even more obscure) pattern that involved ObjectQuery.CreateCommandTree() and IServiceProvider.CreateCommandDefinition(). Now that there is a new pattern, both of the above methods have been removed from public surface. Yes, that means if you’ve discovered that pattern and you’ve been using it, you have to switch to ToTraceString(). Same rules and restrictions as for EntityCommand apply.
And, here is a sample that shows how to print the native SQL generated for an ObjectQuery:
// Create an ObjectContext
using (Northwind.Northwind northwind = new Northwind.Northwind(NorthwindConnectionString))
{
// Create an ObjectQuery
ObjectQuery<Northwind.Product> products = northwind.Products
.Where("LEFT(it.ProductName, 1) = 'C'")
.OrderBy("it.ProductName");
// Make sure the connection is open
northwind.Connection.Open();
// Display the Entity SQL built for the ObjectQuery
Console.WriteLine("\n\n---------------------------------------------------------");
Console.WriteLine("Entity SQL");
Console.WriteLine("---------------------------------------------------------");
Console.WriteLine(products.CommandText);
// Display the T-SQL generated for the (Entity SQL of the) ObjectQuery
Console.WriteLine("\n\n---------------------------------------------------------");
Console.WriteLine("T-SQL");
Console.WriteLine("---------------------------------------------------------");
Console.WriteLine(products.ToTraceString());
// Render the result to make sure the query is valid
Console.WriteLine("\n\n---------------------------------------------------------");
Console.WriteLine("Result");
Console.WriteLine("---------------------------------------------------------");
foreach (Northwind.Product product in products)
{
Console.WriteLine("{0,2}: {1}", product.ProductID, product.ProductName);
}
}
Comments
Anonymous
December 04, 2007
Until Beta 2 ObjectQuery’s builder methods used to build a Command Tree. There were multiple problemsAnonymous
December 04, 2007
Until Beta 2 ObjectQuery’s builder methods used to build a Command Tree. There were multiple problemsAnonymous
July 28, 2010
Hi, You could use Linq to Entity visualizer (Free) to view your native SQL for any target database server available at www.rajavenkatesh.com/projects.aspx Regards Venkat