Unable to get complete SQL text from Entity Framework Core queries containing Union

.net-core c# entity-framework-core iqueryable

Question

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

  • .NETCore v2.1.0
  • Entity Framework Core v2.1.3

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;
    }
}
1
2
9/28/2018 7:17:39 PM

Popular Answer

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.

0
9/28/2018 10:04:56 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow