Getting the SQL that was generated using LINQ to Entities

LINQ to Entities doesn’t have debugger support for getting the SQL that was generated for a query like LINQ to SQL does. However, you can easily get the generated SQL with a few lines of code. I have added some additional lines of code to make it more robust. I recommend adding the following method to your partial class that inherits from ObjectContext. This designer class is created by default when you create an ADO.NET Entity Data Model, but you don’t want to edit that. Instead you want to add a partial class with the same name and in the same namespace.  If you don’t want to put the code there you could modify it slightly to take the ObjectContext as a parameter and put it anywhere you want.

/// <summary>
/// For debugging only. Returns the SQL statement that is generated
/// by LINQ for an IQueryable object. This does NOT execute the query
/// </summary>
/// <param name="query">The IQueryable object</param>
/// <returns>The generated SQL as a string</returns>
public string GetGeneratedSQL(IQueryable query)
{
    string sql = string.Empty;
    bool weOpenedConnection = false;
    try
    {
        if (Connection.State != ConnectionState.Open)
        {
            Connection.Open();
            weOpenedConnection = true;
        }
        sql = (query as ObjectQuery).ToTraceString();
    }
    finally
    {
        if (weOpenedConnection)
        {
            Connection.Close();
        }
    }
    return sql;
}

Here is how you use it.

using (ICA3Entities ctx = new ICA3Entities())
{
    var query = from t in ctx.MyTable
                select t;
    string sql = ctx.GetGeneratedSQL(query);
}

For tools and more information on other options you may want to check out: http://www.scip.be/index.php?Page=ArticlesNET13#ToTraceString