• Tracing all SQL generated by Entity Framework

    by  • March 3, 2013 • .net, sql • 1 Comment

    A nice quick one this week. I spent a little bit of time trying to debug some Entity Framework queries, particularly the parameter values used in a SQL INSERT composed when calling AddObject/SaveChanges. There are a few ways to determine the underlying SQL.

    If you are only interested in LINQ queries, using ToTraceString() on ObjectQuery is a good choice

    Entity Framework provides a way to get the underlying SQL for SELECT queries. To do so, you need to cast the query (of type IQueryable) to an ObjectQuery and call ToTraceString() on it. I usually pipe this into Debug.Writeline so it appears in the Output Window of Visual Studio if you attach a debugger to the running .NET application.

    So, in a particularly contrived example, lets say we have a model mapped in the classic database-first style, and want to debug a query over all people.

    Here’s a full example with comments:

    var query = (from person in ctx.People
                                 select person);
    
                    //query is of type IQueryable
                    string sql = ((ObjectQuery) query).ToTraceString();
    
                    //writes to output window
                    Debug.WriteLine(sql);
    

    So this is great, but what we can’t do with this mechanism is write out any INSERTs, if we wanted to debug the addition of a new person into People for example.

    Tracing INSERTs, UPDATEs and DELETEs in EF requires a little more work

    Here you’ve got a couple of options:

    Community Entity Framework Provider Wrappers

    The Community Entity Framework Provider Wrappers let you enable tracing by creating a derived data context from your original Entity Framework context. This does require some (albeit temporary) code changes during debugging, explained on the link above.

    Making good ol’ SQL Profiler work for you

    You can also make use of SQL Profiler, great when you need to debug something but aren’t prepared (or are unable) to make code changes.

    Out of the box it’s not geared up for tracing out Entity Framework style querying, but I found these tips really useful to get started. When you are in the Trace Properties dialog:

    Select the right RPC events

    In Events Selection, untick all the default events, then tick ‘Show All Events’ and ‘Show All Columns’ and go down to the Stored Procedures events list. Tick RPC:Completed and RPC:Starting, as the screenshot below shows:

    sql-trace-select-rpc

    Now, before you click Run…

    Filter further…

    I found filtering by SessionLoginName ONLY to the username your app connects to the SQL database by in the connection string filtered out any extraneous events to those I was really interested in.

    sql-trace-filter-by-session-login-name

    Click ‘Column Filters’ and scroll down to the SessionLoginName column, expand the ‘Like’ tree and enter the user in the connection string used by the web application. Additionally, tick ‘Exclude rows that do not contain values’.

    Now you can click ‘Run…’ to collect your EF queries regardless of their type.

    About

    .NET developer at thetrainline.com, previously web developer at MRM Meteorite. Awarded a PhD in misbehaviour detection in wireless ad-hoc networks.A keen C# ASP.net developer bridging the gap with APIs and JavaScript frameworks, one web app at a time.

    http://www.paulkiddie.com

    One Response to Tracing all SQL generated by Entity Framework

    1. waninlezu
      December 19, 2014 at 2:21 am

      very usefull, thank you.

    Leave a Reply

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