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

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;
    }
}

ΩmegaMan · Accepted Answer · 2018-09-28 20:28:36Z

0

Use Linqpad instead and upon the result select SQL to view the resulting sql. Note if one needs specific dlls from the project, just include them within the query.

answered Sep 28, 2018 at 20:28

ΩmegaMan

32.2k13 gold badges110 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Evan French Over a year ago

Thanks for your answer. That may work for getting the SQL on an ad hoc basis, but I want to save the sql in a database and update that sql each time the query is run. Basically, my tool should be self-documenting its own sql for reference by end users.

ΩmegaMan Over a year ago

Sounds like a lot of work for little gain. Keep in mind that EF generated sql technically could change with every release. If one wants such documented sql, why not use/utilize stored procedures to hold the business logic?

Evan French Over a year ago

I want to allow business owners to see how their metrics are being calculated, which will add a layer of transparency to their reporting process, which they find incredibly valuable. Minor changes from newer versions are irrelevant for my use case. The calculation of the metrics may change frequently and this approach guarantees that the documentation will remain up to date.

Tore Aurstad · Accepted Answer · 2018-09-28 22:04:56Z

0

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.

answered Sep 28, 2018 at 22:04

Tore Aurstad

3,9501 gold badge35 silver badges27 bronze badges

1 Comment

Evan French Over a year ago

I thought about that too, but modelVisitor.Queries only contains a single value. However, queryModel.ResultOperators has two values corresponding to both halves of my union. I suspect that there's a problem with modelVisitor.CreateQueryExecutor<TEntity>(queryModel);, which I assume populates modelVisitor.Queries with only one value

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related