As a developer, we often use an Object-Relational Mapper (ORM) to abstract from the way we work with a database. Gone are the days of building a
SqLCommand object, setting the CommandText and executing row by row the result set, as used with ADO.NET. Today, most .NET developers use EntityFramework
to work with the database.
But this abstraction is all and well, and makes us work more efficient. Sadly, many developers are agnostic to the fact that despite they get the results out
from the database, they do often do so in a slow manner. The reason of this is often not that there is a lot of data in the database, but we use Entity Framework
queries that generate the wrong sql, i.e we get the results, but the SQL involved got a poor performance.
You can use LinqPad for example to display the SQL involved in Entity Framework queries. But we can also achieve this using programatically methods, with C#.
Here is an extension class I wrote to achieve this. The extension methods works with both data contexts that inherit from DbContext and data contexts that inherit
from ObjectContext.
using System;
using System.Data.Entity.Core.Objects;
using System.Linq;
using System.Reflection;
namespace Hemit.OpPlan.Data.EntityFramework
{
public static class IQueryableExtensions
{
/// <summary>
/// Shows the sql the IQueryable query will be generated into and executed on the database
/// </summary>
/// <param name="query">The IQueryable to analyze</param>
/// <param name="decodeParameters">Set to true if this method should try decoding the parameters</param>
/// <remarks>This is the generated SQL query in use for Entity Framework. This works using ObjectContext</remarks>
public static string ShowSqlUsingObjectContext(this IQueryable query, bool decodeParameters = false)
{
var objectQuery = (ObjectQuery)query;
string result = ((ObjectQuery)query).ToTraceString();
if (!decodeParameters)
return result;
foreach (var p in objectQuery.Parameters)
{
string valueString = p.Value != null ? p.Value.ToString() : string.Empty;
if (p.ParameterType == typeof(string) || p.ParameterType == typeof(DateTime))
valueString = "'" + valueString + "'";
result = result.Replace("@" +p.Name, p.Value != null ? valueString : string.Empty);
}
return result;
}
public static string ShowSqlUsingDbContext(this IQueryable query, bool decodeParameters = false)
{
var memberInfo = query.GetType().BaseType;
if (memberInfo != null)
{
var internalQueryField =
memberInfo.GetFields(BindingFlags.NonPublic
| BindingFlags.Instance).FirstOrDefault(f => f.Name.Equals("_internalQuery"));
if (internalQueryField != null)
{
var internalQuery = internalQueryField.GetValue(query);
var objectQueryField =
internalQuery.GetType().GetProperty("ObjectQuery");
// Here's your ObjectQuery!
if (objectQueryField != null)
{
var objectQuery = objectQueryField.GetValue(internalQuery) as ObjectQuery;
string sql = ShowSqlUsingObjectContext(objectQuery, decodeParameters);
return sql;
}
}
}
return null;
}
}
}
Note that when we use an IQueryable inside a DbContext, the BaseType is actually a DbQuery, and this wraps the ObjectQuery inside a field called "_internalQuery". In addition, we get a property
inside this field that is called "ObjectQuery". So we can get hold of the ObjectQuery inside a DbQuery. When we got hold of the ObjectQuery, it is easy to decode the contents using ToTraceString()
method and if we want to further decode the parameters EntityFramework generates, we can do so using the Parameters property of ObjectQuery. We can then interpolate the SQL parametrization and get
the SQL string most readable, if we like this form. Of course, some developers rather like the parametrized version. There may be some queries that don't have any parameters at all, but this is fine.
Of course, all this parametrization business is to hinder SQL injection. Please do not resort to creating methods that accepts such "clean sql", you may easily generate an attack vector into your system
if you try do adjusts queries so and not being careful.
With this extension method we can easily test it out:
using SomeAcme.EntityFramework;
using Nunit.Framework;
[Test]
public void TestGettingSomeSql(){
using (var context = new SomeAcmeContext()){
IQueryable query = context.SomeDataEntity.Where(x => x.SomeProperty == 123).AsQueryable();
string sql = string.Empty;
//If this is a dbContext:
sql = query.ShowSqlUsingDbContext(decodeParameters: true);
//Or if this is an ObjectContext:
sql = query.ShowSqlUsingObjectContext(decodeParameters: true);
Console.WriteLine(sql);
}
}
We create a query, using the .AsQueryable() extension method in Linq and we then pass the IQueryable object to the extension methods of the class shown earlier. Of course, the extension method
to use depends on the type of data context you work with. Either a DbContext or an ObjectContext.