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 (3)

  1. Until Beta 2 ObjectQuery’s builder methods used to build a Command Tree. There were multiple problems

  2. Until Beta 2 ObjectQuery’s builder methods used to build a Command Tree. There were multiple problems

  3. Hi, You could use Linq to Entity visualizer (Free) to view your native SQL for any target database server available at http://www.rajavenkatesh.com/projects.aspx

    Regards

    Venkat