ToQueryPlan to... get the query plan...

[roji]

Very cool idea suggested by @lauxjpn: we now have ToQueryString, we could also get the query plan from the database.

SQL Server:

SET SHOWPLAN_ALL ON;
<query>
SET SHOWPLAN_ALL OFF;

PostgreSQL / SQLite

EXPLAIN <query>

[lauxjpn]

When this has been implemented, this basically screams for an extension, that allows users to specify execution duration limits, that will log the query together with the query plan, once those limits get hit.

[roji]

We may as well just find out what's wrong with the queries and fix it too 🤣

Seriously, this is a really nice direction.

[ajcvickers]

This has challenges that ToQueryString doesn't have--most notably that it requires a connection to the database to run the "explain" query. So this probably needs a bit more thought, but integrating this in some way with EF Core would be very useful.

[ErikEJ]

Also not all users have showplan rights with SQL Server..

[NickStrupat]

I just wrote an extension method along these lines

static class SqliteExtensions
{
    public static String ToQueryPlan(this IQueryable queryable)
    {
          using var command = queryable.CreateDbCommand();
          command.CommandText = "explain query plan " + command.CommandText;
          using var reader = command.ExecuteReader();
          reader.Read();
          return reader.GetString(3);
    }
    
    
    public static async Task<String> ToQueryPlanAsync(this IQueryable queryable, CancellationToken ct = default)
    {
        await using var command = queryable.CreateDbCommand();
        command.CommandText = "explain query plan " + command.CommandText;
        await using var reader = await command.ExecuteReaderAsync(ct);
        await reader.ReadAsync(ct);
        return reader.GetString(3);
    }
}

[Giorgi]

It's not a method but I built a VS debugger visualizer to view query plans inside Visual Studio: EFCoreVisualizer

Requires VS 17.9 which was released yesterday.

[roji]

@Giorgi this is super nicer! Obviously the visualization is also much better than any text-based dump we could integrate.

Did you write the visualization yourself, or could you leverage any existing SQL Server component to do that for you?

[Giorgi]

All the credit for visualization goes to html-query-plan and pev2, I wrote glue code only 😃

[ErikEJ]

@Giorgi you should get it added to the tools list in the docs

[roji]

Super cool!