Jaa

How to Get the Native SQL from an EntityCommand

  • Artikkeli

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

            }

Comments

  • Anonymous
    December 04, 2007
    PingBack from http://msdnrss.thecoderblogs.com/2007/12/04/how-to-get-the-native-sql-from-an-entitycommand/

  • Anonymous
    December 04, 2007
    The way to get the native SQL generated for an ObjectQuery is exactly the same as for EntityCommand –

  • Anonymous
    December 04, 2007
    The way to get the native SQL generated for an ObjectQuery is exactly the same as for EntityCommand

  • Anonymous
    March 16, 2008
    Hi Zlatko, how I can get the native SQL from Linq To Entities query which return anonymous type? For expample: NorthwindEntities context = new NorthwindEntities(); var query = from e in context.Employees            select new { e.LastName, e.FirstName }; Thank you for your help!