In EF 4.1 DbContext how to trace generated SQL

Question

I wonder how to trace generated SQL like DataContext in LinqToSql.

I also read articles about the solution of EFProviderWrapper on Jaroslaw Kowalski's blog, but it is based on ObjectContext, does not work for DbContext.

Anyone know how to do this in DbContext?

Thank you.

This is one of those [features](http://msdn.microsoft.com/en-us/magazine/ee336126.aspx) that Microsoft wants you to spend a lot more money to get (Visual Studio Ultimate). There are some 3rd party libraries that make it easier though. — David Sherret, Dec 09 '13 at 21:16

score 16 · Answer 1 · answered Jul 19 '11 at 14:32

16

The easiest way with DbContext and DbSet<T> is just to use ToString() on the IQueryable you have built. For example:

var query = context.Blogs.Include(b => b.Posts)
                   .Where(b => b.Title == "AnyTitle");

string sql = query.ToString();

sql contains the SQL command which will be issued to the DB when the query gets executed.

answered Jul 19 '11 at 14:32

Slauma

175,098
59
401
420

1

That only helps if you are doing a search (that returns an IQueryable). If you are adding, modifying and deleting entities how do you view that sql? – codemonkey Aug 12 '11 at 19:08
@codemonkey: True, works only for queries. I don't think that there is any built-in way to trace *all* SQL statements with DbContext. – Slauma Aug 12 '11 at 21:02
I came here to ask @codemonkey 's question. Everything I'm finding shows the former, no luck yet on the latter. – Funka Jan 26 '13 at 01:34
1

@Slaima: but how can i log generated Sql from SaveChanges() statement via DBContext? – Masoud Jun 02 '13 at 06:32

score 1 · Accepted Answer · edited May 23 '17 at 12:22

1

The MVC-Mini-Profiler is a pwerful tool, not ony trace generated sql, but also profiling tool.

Using mvc-mini-profiler database profiling with Entity Framework Code First

edited May 23 '17 at 12:22

Community

1
1

answered Jul 19 '11 at 12:40

Chance

1,317
2
12
18

kmk · Answer 3 · 2013-04-02T19:43:17.153

For anyone not wanting to pull in a third party library, and is simply looking for the SQL containing the parameters (and isn't upset by all the reflection), this extension method grabs the InternalQuery object and the ObjectQuery object from the DbQuery and returns the ToTraceString after realizing the Parameters back into the string. If it fails it return the parameter-less ToString from the IQueryable:

    public static string ToSqlString<TEntity>(this IQueryable<TEntity> queryable) where TEntity : class
    {
        try
        {
            var dbQuery = queryable as DbQuery<TEntity>;

            // get the IInternalQuery internal variable from the DbQuery object
            var iqProp = dbQuery.GetType().GetProperty("InternalQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);

            var iq = iqProp.GetValue(dbQuery);

            // get the ObjectQuery internal variable from the IInternalQuery object
            var oqProp = iq.GetType().GetProperty("ObjectQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);

            var oq = oqProp.GetValue(iq);

            var objectQuery = oq as ObjectQuery<TEntity>;

            var sqlString = objectQuery.ToTraceString();

            foreach (var objectParam in objectQuery.Parameters)
            {
                if (objectParam.ParameterType == typeof(string) || objectParam.ParameterType == typeof(DateTime) || objectParam.ParameterType == typeof(DateTime?))
                {
                    sqlString = sqlString.Replace(string.Format("@{0}", objectParam.Name), string.Format("'{0}'", objectParam.Value.ToString()));
                }
                else if (objectParam.ParameterType == typeof(bool) || objectParam.ParameterType == typeof(bool?))
                {
                    bool val;
                    if (Boolean.TryParse(objectParam.Value.ToString(), out val))
                    {
                        sqlString = sqlString.Replace(string.Format("@{0}", objectParam.Name), string.Format("{0}", val ? 1 : 0));
                    }

                }
                else
                {
                    sqlString = sqlString.Replace(string.Format("@{0}", objectParam.Name), string.Format("{0}", objectParam.Value.ToString()));
                }
            }

            return sqlString;
        }
        catch (Exception)
        {
            //squash it and just return ToString
            return queryable.ToString();
        }
    }

There is a problem with this solution when there are more than 10 parameters. Let's say a parameter is called p10. When you replace p1 with it's value, you end up with invalid SQL. — David Airapetyan, Jun 16 '14 at 21:46

score 0 · Answer 4 · answered Apr 08 '13 at 21:05

Execute your code and then run this query to see the last SQL executed.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

score 0 · Answer 5 · answered Jun 16 '14 at 21:47

This is a slight improvement over the solution (by @kmk) that replaces parameters with values. This solution declares and assigns the parameters instead:

    public static string ToSqlString<TEntity>(this IQueryable<TEntity> queryable) where TEntity : class
    {
        StringBuilder parametersBuilder = new StringBuilder();

        try
        {
            var dbQuery = queryable as DbQuery<TEntity>;

            // get the IInternalQuery internal variable from the DbQuery object
            var iqProp = dbQuery.GetType().GetProperty("InternalQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);

            var iq = iqProp.GetValue(dbQuery, null);

            // get the ObjectQuery internal variable from the IInternalQuery object
            var oqProp = iq.GetType().GetProperty("ObjectQuery", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);

            var oq = oqProp.GetValue(iq, null);

            var objectQuery = oq as ObjectQuery<TEntity>;

            var sqlString = objectQuery.ToTraceString();

            foreach (var objectParam in objectQuery.Parameters)
            {
                SqlMetaData metadata = SqlMetaData.InferFromValue(objectParam.Value, objectParam.Name);
                string sqlType = metadata.TypeName + (metadata.SqlDbType == SqlDbType.NVarChar ? "(" + metadata.MaxLength + ")" : String.Empty);

                parametersBuilder.AppendFormat("declare @{0} {1} = '{2}'", objectParam.Name, sqlType, objectParam.Value);
                parametersBuilder.AppendLine();
            }

            parametersBuilder.AppendLine();
            return parametersBuilder.ToString() + sqlString;
        }
        catch (Exception)
        {
            //squash it and just return ToString
            return queryable.ToString();
        }
    }

score 0 · Answer 6 · answered Apr 09 '11 at 06:01

0

I use the SQL Server profiler tool to see exactly what SQL has been created. There is also http://efprof.com/ but it has quite a high price.

answered Apr 09 '11 at 06:01

JK.

21,477
35
135
214

1

Thank you, I know that tool. And I'm looking for a DIY solution. – Chance Apr 09 '11 at 06:16

score 0 · Answer 7 · answered Jul 19 '11 at 13:12

0

You can use the ObjectQuery.ToTraceString method to view store commands (SQL statements for example). The How To on MSDN will show you how it can be used.

Note that in many cases, you will be able to cast an IQueryable (the return type from Linq extension methods such as IQueryable.Where) to an ObjectQuery so that you have access to the ToTraceString method.

answered Jul 19 '11 at 13:12

Scott Munro

13,369
3
74
80

this is not quite convenient. I recommend this : http://stackoverflow.com/questions/6550046/using-mvc-mini-profiler-database-profiling-with-entity-framework-code-first/6743941#6743941 – Chance Jul 20 '11 at 04:43

score 0 · Answer 8 · answered Oct 04 '11 at 09:41

I found this EFTracingProvider extension for the ObjectContext here:

http://efwrappers.codeplex.com/

But the example is for ObjectContext not DbContext, to get it to work with DbContext do the following in the constructor:

Public Sub New()
  MyBase.New(EFTracingProviderUtils.CreateTracedEntityConnection("MyDbConnection"), True)
  Dim context As ObjectContext = CType(Me, IObjectContextAdapter).ObjectContext
  context.EnableTracing()
End Sub

Oh and remember to set the config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.diagnostics>
    <sources>
      <source name="EntityFramework.MyDbConnection" switchValue="All" />
    </sources>
 </system.diagnostics>

That then traces all the SQL to immediate window.

In EF 4.1 DbContext how to trace generated SQL

8 Answers8