I want to save the sql generated by entity framework/LINQ queries for documentation purposes. I had been using the IQueryable extension method from this blog post to get at the raw sql: http://rion.io/2016/10/19/accessing-entity-framework-core-queries-behind-the-scenes-in-asp-net-core/
This works great for most of my queries. However, when I tried to get the sql from a query containing a UNION, the extension method only returned sql for the first half of the union, ignoring the latter half. Does anyone know how to get the sql before and after the union?
For reproducibility, I'm using
EF/LINQ query example
// Union query on DbContext with DbSet "Tables"
var query = dbContext.Tables.Take(1).Union(dbContext.Tables.Take(2));
// IQueryable Extension method
var sql = query.ToSql();
// Clean up SQL for easier reading
sql = sql.Replace("\n", "").Replace("\r", "");
// Value of sql (Missing second half of union)
// SELECT TOP(1) [t].[Id], [t].[CreateDate], [t].[Description], [t].[DisplayName], [t].[Name], [t].[SourceId], [t].[Sql], [t].[Status]FROM [metadata].[Tables] AS [t]
IQueryable extension for reference
public static class IQueryableExtensions
{
private static readonly TypeInfo QueryCompilerTypeInfo = typeof(QueryCompiler).GetTypeInfo();
private static readonly FieldInfo QueryCompilerField = typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields.First(x => x.Name == "_queryCompiler");
private static readonly FieldInfo QueryModelGeneratorField = QueryCompilerTypeInfo.DeclaredFields.First(x => x.Name == "_queryModelGenerator");
private static readonly FieldInfo DataBaseField = QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");
private static readonly PropertyInfo DatabaseDependenciesField = typeof(Database).GetTypeInfo().DeclaredProperties.Single(x => x.Name == "Dependencies");
public static string ToSql<TEntity>(this IQueryable<TEntity> query) where TEntity : class
{
var queryCompiler = (QueryCompiler)QueryCompilerField.GetValue(query.Provider);
var modelGenerator = (QueryModelGenerator)QueryModelGeneratorField.GetValue(queryCompiler);
var queryModel = modelGenerator.ParseQuery(query.Expression);
var database = (IDatabase)DataBaseField.GetValue(queryCompiler);
var databaseDependencies = (DatabaseDependencies)DatabaseDependenciesField.GetValue(database);
var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
var sql = modelVisitor.Queries.First().ToString();
return sql;
}
}
Are you sure that you get tbe complete SQL with the extension method ToSql? This line:
var sql = modelVisitor.Queries.First().ToString();
Can a Union have two queries?
Try first to loop through queries and log them to the console forexample.
foreach (var query in modelVisitor.Queries)
Console.WriteLine(query);
That will call toString() on each query. Can you see the entire query of the union? If that so, do a string.Join to concatenate the entire Sql.