Updating LINQPad.QueryPlanVisualizer for LINQPad6 And Entity Framework Core

LINQPad.QueryPlanVisualizer is a custom visualizer for LINQPad that shows a database query plan inside LINQPad. It also shows missing indexes for the query that you can create directly from LINQPad.

Since then, a new major version, LINQPad 6, was released that targets .NET Core 3 and .NET 5 and uses Entity Framework Core (as well as LINQ-to-SQL) for running Linq queries.

To support these changes, I have just released a new version of LINQPad.QueryPlanVisualizer that is compatible with LINQPad 6, supports Entity Framework Core, and, last but not least, adds support for viewing PostgreSQL query plans in LINQPad. It also can share SQL Server query plan to Brent Ozar’s Paste The Plan and share PostgreSQL query plan explain.dalibo.com.

Using LINQPad.QueryPlanVisualizer

To use LINQPad.QueryPlanVisualizer in your queries, either reference it from NuGet or download the latest release from GitHub and copy QueryPlanVisualizer.LinqPad6.dll to My Documents\LINQPad Plugins\NetCore3 folder.

To view the query plan call DumpPlan method on any IQueryable instance:

Posts.OrderBy(post => post.PostDate.Year > 2020).OrderByDescending(post => post.BlogId).DumpPlan();

If you run the same query on an PostgreSQL database, you will see a similar output:

Internals of LINQPad.QueryPlanVisualizer

As the visualizer supports multiple ORMs (EF Core and LINQ-to-SQL) and different databases (SQL Server and PostgreSQL), the project uses Bridge Pattern to avoid creating separate classes for all possible combinations. The main class hierarchies are OrmHelper, which is responsible for extracting DbCommand from IQueryable for a specific ORM; DatabaseProvider is responsible for getting query plan for a specific database; and PlanProcessor that converts raw plan to Html. The OrmHelper also contains a factory method for creating an instance for the ORM that the current query uses:

public static OrmHelper Create<T>(IQueryable<T> queryable, object dataContext)
{
  if (dataContext is DbContext dbContext)
  {
    var efCoreHelper = new EFCoreHelper(dbContext.Database.ProviderName);
    efCoreHelper.Initialize(queryable);
    return efCoreHelper;
  }

  var queryType = queryable.GetType();

  var dataQueryType = queryType.Assembly.GetType("System.Data.Linq.DataQuery`1");
  var tableQueryType = queryType.Assembly.GetType("System.Data.Linq.Table`1");

  var queryGenericType = queryType.GetGenericTypeDefinition();
  if (queryGenericType == dataQueryType || queryGenericType == tableQueryType)
  {
    var contextField = queryType.GetField("context", BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.GetField);
    var context = contextField?.GetValue(queryable);

    if (context != null)
    {
      var linqToSqlHelper = new LinqToSqlHelper(context);
      linqToSqlHelper.Initialize(queryable);
      return linqToSqlHelper;
    }
  }

  return null;
}

The dataContext object is the data context that LIQNPad exposes via Util.CurrentDataContext.

The constructor of EFCoreHelper sets DatabaseProvider and PlanProcessor based on the target database:

public EFCoreHelper(string provider) : base(CreateParameters(provider))
{
}

public static (DatabaseProvider provider, PlanProcessor planConvertor) CreateParameters(string provider)
{
  return provider switch
  {
    "Microsoft.EntityFrameworkCore.SqlServer" => (new SqlServerDatabaseProvider(), new SqlServerPlanProcessor()),
    "Npgsql.EntityFrameworkCore.PostgreSQL" => (new PostgresDatabaseProvider(), new PostgresPlanProcessor()),
    _ => (null, null)
  };
}

The LinqToSqlHelper always passes SqlServerDatabaseProvider and SqlServerPlanProcessor to the base class as it only supports SQL Server.

Extracting and Formatting Query Plan from SQL Server

To extract query plan for SQL Server, the SqlServerDatabaseProvider class executes SET STATISTICS XML ON command before running the actual query and captures the plan returned by the database:

protected override string ExtractPlanInternal(DbCommand command)
{
  using var setStatisticsCommand = command.Connection.CreateCommand();
  setStatisticsCommand.CommandText = "SET STATISTICS XML ON";
  setStatisticsCommand.ExecuteNonQuery();

  using var reader = command.ExecuteReader();
  while (reader.NextResult())
  {
    if (reader.GetName(0) == "Microsoft SQL Server 2005 XML Showplan")
    {
      reader.Read();
      return reader.GetString(0);
    }
  }

  return null;
}

Next, SqlServerPlanProcessor uses html-query-plan to display the plan as Html.

Extracting Query Plan from PostgreSQL

For PostgreSQL, the PostgresPlanProcessor class prepends EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) to the query text to get the plan from a database:

protected override string ExtractPlanInternal(DbCommand command)
{
  command.CommandText = "EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) " + command.CommandText;

  using var reader = command.ExecuteReader();
  var plan = string.Join(Environment.NewLine, reader.Cast<IDataRecord>().Select(r => r.GetString(0)));

  return plan;
}

The PostgreSQL query plan is then displayed directly as plain text.

Conclusion

LINQPad is a handy tool for prototyping and testing Linq queries. LINQPad.QueryPlanVisualizer takes it further by showing query plan and adding other helpful features. If you have questions or suggestions please leave a comment, create an issue and star the repository.