16

I've using Entity Framework CTP5 in "code only" mode. I'm running a LINQ query on a object that was return from the database, as the query is running really slowly. Is there any way in which I can get the SQL statement that is being generated from the query?

Topic currentTopic =
    (from x in Repository.Topics
     let isCurrent = (x.StoppedAt <= x.StartedAt || (x.StartedAt >= currentTopicsStartedAtOrAfter))
     where x.Meeting.Manager.User.Id == user.Id && isCurrent
     orderby x.StartedAt descending
     select x).FirstOrDefault();

The "Repository" property is a descendent of DbContext.

It's a little complicated, as EF can't use my helper methods on the objects, so I'm specifying the logic directly in the query.

So, is there any way I can dump the SQL that will be produced by that LINQ query (e.g. to my log4net repository)?

dommer
  • 19,610
  • 14
  • 75
  • 137
  • I can't help you with the SQL dump, but I'm curious as to why EF can't use your helper methods on the objects. How have you defined the helpers? – Tomas Aschan Mar 01 '11 at 11:22
  • Well, I have a IsCurrent property on the topic, that is defined as "return (StoppedAt <= StartedAt || (DateTime.Now - StartedAt).TotalHours <= ExpirationPeriodHours)". If I use that in the query (i.e. x.IsCurrent), it won't execute. – dommer Mar 01 '11 at 11:27

5 Answers5

19

You can try using Entity Framework tracing provider as described here (but it is old post for CTP3).

Your other choices are:

In common EF you can also use ToTraceString as @Andy suggested but DbQuery in CodeFirst doesn't have this method (or I didn't find it).

Edit:

So DbQuery doesn't have ToTraceString because it is directly implemented as ToString.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 3
    +1 for .ToString() - took me 15 minutes to find this post and 2 seconds to actually see my query! i like it – mlhDev Aug 13 '12 at 19:27
  • 7
    When I use DbQuery.ToString(), the SQL it returns doesn't fill in the parameters. In other words it contains a bunch of "@p__linq__0". Am I the only one who sees this? – Josh Mouch Sep 20 '12 at 23:48
10

This worked for me and it is free:

public static class DebugExtensions
{
    private static object GetPropertyValue(object o, string Name)
    {
        return o.GetType().GetProperties(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public).Where(x => x.Name == Name).First().GetValue(o, null);
    }
    public static string ToTraceString(this IQueryable query)
    {
        var oquery = (ObjectQuery)GetPropertyValue(GetPropertyValue(query, "InternalQuery"), "ObjectQuery");
        return oquery.ToTraceString();
    }
}

Usage:

   var rows = db.Forecasts.Take(1);
   System.Diagnostics.Debug.WriteLine(rows.ToTraceString());
John
  • 634
  • 8
  • 17
3

I'd either use SQL Trace to grab the query running on the server directly, or use the Event Tracing for Windows (SQL Profiling) feature out of ANTS Performance Profiler.

Mel Harbour
  • 371
  • 1
  • 9
2

Setting up logging is as easy as:

context.Database.Log = Console.WriteLine;

Original answer: https://stackoverflow.com/a/20757916/2183503

Community
  • 1
  • 1
kotpal
  • 437
  • 8
  • 11
1

The extension method ToTraceString() might be what you're looking for:

http://msdn.microsoft.com/en-us/library/system.data.objects.objectquery.totracestring.aspx

Andy Holt
  • 572
  • 2
  • 9
  • 1
    This won't work because he's using a DbContext, which uses DbSet and DbQuery, as opposed to an ObjectContext, which uses ObjectQuery. – Josh Mouch Sep 20 '12 at 23:45