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);

                }

            }