Displaying the SQL of Entity Framework Linq to Entities queries

When working with Entity Framework or EF we often blindly run Linq to Entities queries and see that we get the results we are after, ignoring the SQL we are generating behind the scenes. This is often a bad strategy, as we risk the following:

1. The SQL can be erroneuous and give wrong results
2. The SQL can be inefficient and slow
3. We abstract ourselves away from SQL and use the wrong logical constructs in EF

Here is an example of an extension method that allow ourselves to display the SQL of Linq to Entities queries:

public static class IQueryableExtensions
    {

        /// 

        /// Shows the sql the IQueryable query will be generated into and executed on the DbServer
        /// 
        /// The IQueryable to analyze
        /// Set to true if this method should try decoding the parameters
        /// This is the generated SQL query in use for Entity Framework
        public static string ShowSql(this IQueryable query, bool decodeParameters = false)
        {
            var objectQuery = (ObjectQuery)query; 

            string result = ((ObjectQuery)query).ToTraceString();

            if (!decodeParameters)
                return result; 

            foreach (var p in objectQuery.Parameters)
            {
                string valueString = p.Value != null ? p.Value.ToString() : string.Empty;
                if (p.ParameterType == typeof(string) || p.ParameterType == typeof(DateTime))
                    valueString = "'" + valueString + "'";
                result = result.Replace("@" +p.Name, p.Value != null ? valueString : string.Empty); 
            }
            return result; 
        }     

}

Next, here is a simple extension method that shows how we can use this in an integration test. If you want, you can output the sql string to the Output Window, using either Console.WriteLine() or Debug.WriteLine().

    [TestFixture]
    public class IqueryableExtensionsTest
    {

        [Test]
        public void QueryableReturnsSqlAndDoesNotThrow()
        {
            using (var dbContext = SomeObjectContextFactory.SomeObjectDataContext)
            {
                var somerows= from somerow in dbContext.SomeTable
                    where somerow.SomeStatus == (int) SomeStatus.SomeStatusValue
                    && somerow.SomeOtherDateColumn >= new DateTime(2015, 2, 12)
                    select somerow;
                string sql = someRows.ShowSql();
                Assert.IsNotNull(sql);
            }
        }

    }

Share this article on LinkedIn.