Getting the SQL generated by entity framework queryable

 

ORM’s are great, I don’t remember writing SQL to query a database query via an application for a while now. However, it’s important to keep in mind that queries should be optimized, and there is no way to get a query plan from entity framework.

There are couple of things you can do to get the SQL query generated from Entity Framework.

Option 1 : While debugging

  1. Set a brakepoint after your queryable is built
  2. Use que QuickWatch window on the object (select your variable and press CTRL+ALT+Q)
  3. Copy the value field from the expression window.

untitled

Option 2 : At run-time, using a static extension class.

Implement the following class in your app.

using System;
using System.Collections.Generic;
using System.Data.Entity.Core.Objects;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Newsoft.Extensions
{
    public static class IQueryableExtensions
    {
        public static string ToTraceQuery(this IQueryable query)
        {
            return IQueryableExtensions.ToTraceQuery((dynamic)query);
        }
        /// <summary>
        /// For an Entity Framework IQueryable, returns the SQL with inlined Parameters.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <returns></returns>
        public static string ToTraceQuery<T>(this IQueryable<T> query)
        {

            ObjectQuery objectQuery;
            var method = typeof(IQueryableExtensions).GetMethod("GetQueryFromQueryable");
            var genMethod = method.MakeGenericMethod(query.GetType().GenericTypeArguments[0]);

            objectQuery = (dynamic)genMethod.Invoke(null, new object[] { query });

            var result = objectQuery.ToTraceString();
            foreach (var parameter in objectQuery.Parameters)
            {
                if (parameter.Value == null)
                    continue;
                var name = "@" + parameter.Name;
                var value = "'" + parameter.Value.ToString() + "'";
                result = result.Replace(name, value);
            }
            return result;

        }
        public static System.Data.Entity.Core.Objects.ObjectQuery<T> GetQueryFromQueryable<T>(IQueryable<T> query)
        {
            var internalQueryField = query.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_internalQuery")).FirstOrDefault();
            var internalQuery = internalQueryField.GetValue(query);
            var objectQueryField = internalQuery.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_objectQuery")).FirstOrDefault();
            var objectQueryValue = objectQueryField.GetValue(internalQuery);

            return (dynamic)objectQueryValue;// as System.Data.Entity.Core.Objects.ObjectQuery<T>;
        }
    }
}

Include the namespace of your extension class

Using Newsoft.Extensions;

Call the ToTraceQuery on your queryable.

static void Main(string[] args)

{

var ctx = new NorthwindContext();


var resTyped = ctx.Set<Category>().Where(V => V.CategoryName == "Beverages");


var queryString = resTyped.ToTraceQuery();


}

 

Option 3: Intercept the query on the SQL Server

Open Sql server Profiler, a free tool that comes with Sql server management studio.

Connect to your sql server instance and start a new trace

Intercept the query sent through the database by entity framework

untitled2

Submit a Comment

Your email address will not be published. Required fields are marked *

Share This