How to Get the Native SQL from an EntityCommand

  • Article

With the Beta 3 release, Entity Framework is introducing an easy way to get the native SQL from an EntityCommand. There was public API to get to the native SQL, but the pattern was too obscure, and I don’t want to go into detail. Let’s discuss the new one, instead. It’s a single method directly exposed off EntityCommand – ToTraceString() , which returns a string.

 

There is no guarantee the returned string is directly executable by the provider (although as of today it is for SQL Server). What you should keep in mind is that ToTraceString() requires the associated EntityConnection to be open. On the other hand, ToTraceString() does not require the EntityCommand to be prepared.

 

Here’s a sample code snippet that shows how to print the native SQL generated for an EntityCommand:

 

            // Create an EntityConnection

            using (EntityConnection connection = new EntityConnection(NorthwindConnectionString))

            {

                string esql = "SELECT VALUE product \n" +

                              "FROM Northwind.Products AS product\n" +

                              "WHERE LEFT(product.ProductName, 1) = 'C' \n" +

                              "ORDER BY product.ProductName";

                // Create an EntityCommand for this connection.

                EntityCommand productsCmd = connection.CreateCommand();

                productsCmd.CommandText = esql;

                // Open the connection.

                connection.Open();

                // Display the Entity SQL text using the CommandText property from DbCommand.

                Console.WriteLine("\n\n---------------------------------------------------------");

                Console.WriteLine("Entity SQL");

                Console.WriteLine("---------------------------------------------------------");

                Console.WriteLine(productsCmd.CommandText);

                // Display the T-SQL text using the new ToTraceString() method.

                Console.WriteLine("\n\n---------------------------------------------------------");

                Console.WriteLine("T-SQL");

                Console.WriteLine("---------------------------------------------------------");

                Console.WriteLine(productsCmd.ToTraceString());

                // Traverse and display the result to make sure the query is valid.

                Console.WriteLine("\n\n---------------------------------------------------------");

                Console.WriteLine("Result");

                Console.WriteLine("---------------------------------------------------------");

                EntityDataReader productsReader = productsCmd.ExecuteReader(CommandBehavior.SequentialAccess);

                while (productsReader.Read())

                {

                   Console.WriteLine("{0,2}: {1}", productsReader.GetInt32(productsReader.GetOrdinal("ProductID")),

                                                    productsReader.GetString(productsReader.GetOrdinal("ProductName")));

                }

                productsReader.Close();

                connection.Close();

            }