Implementing Missing Features in Entity Framework Core – Part 5: Getting the SQL for a Query
Updated for EF Core 3.1
This will be the fifth post in a series of posts about bringing the features that were present in Entity Framework pre-Core into EF Core. The others are:
-
Part 1: Introduction, Find, Getting an Entity’s Id Programmatically, Reload, Local, Evict
-
Part 2: Explicit Loading
-
Part 3: Validations
-
Part 4: Conventions
This time I’m going to talk about something that is often requested: how can I get the SQL string for a LINQ query? If you remember, in the pre-Core days you had to do some reflection in order to get the underlying ObjectQuery and then call its ToTraceString method. Now, things are very different, although I may say, still rather tricky!
So, this is all we need:
public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
{
var entityQueryable = query as EntityQueryable<TEntity>;
var internalDbSet = query as InternalDbSet<TEntity>;
if ((entityQueryable == null) && (internalDbSet == null))
{
throw new ArgumentException("Invalid query.", nameof(query));
}
var enumerator = query.Provider.Execute<IEnumerable<TEntity>>(query.Expression).GetEnumerator();
var relationalCommandCache = enumerator.GetType().GetField("_relationalCommandCache", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(enumerator) as RelationalCommandCache;
var selectExpression = relationalCommandCache.GetType().GetField("_selectExpression", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(relationalCommandCache) as SelectExpression ?? throw new InvalidOperationException($"could not get SelectExpression");
var factory = relationalCommandCache.GetType().GetField("_querySqlGeneratorFactory", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(relationalCommandCache) as IQuerySqlGeneratorFactory ?? throw new InvalidOperationException($"could not get IQuerySqlGeneratorFactory");
var sqlGenerator = factory.Create();
var command = sqlGenerator.GetCommand(selectExpression);
var sql = command.CommandText;
return sql;
}
This example, unlike previous versions, does not use reflectoon! For the time being, it works perfectly:
var sql1 = ctx.Blogs.ToSql();
var sql2 = ctx
.Blogs
.Where(b => b.CreationDate.Year == 2016)
.ToSql();
Hope this is useful to you! 