Friday, June 12, 2009

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

3 comments:

Unknown said...

Dear Brent,

It is a very nice post. I'm looking a way to get SQL condition part of an EF query. Your post is very helpful to me.

Thank you.

whateversuitsyou said...

I don’t get why the web is full of solutions to this question which (like this one) only cover half of the cases. How do you output the SQL query generated for a .Count(), .First(), etc.? Why does no other programmer but me seem to notice this gap???

Suresh Guggarigoudar said...

I do. What is the SQL when return is a string or a number?
eg:
var query = (from rows in entity.labregister
where rows.LabNo.ToLower() == paramLabNo.ToLower() && rows.Test.ToLower() == paramTest.ToLower()
select rows).FirstOrDefault();