Conquer ASP.Net Boilerplate Query Performance in LINQPad, (Introducing LINQPad.ABP)

How to use LINQPad, and a new plugin called LINQPad.ABP, to diagnose performance problems in ASP.Net Boilerplate applications.

Conquer ASP.Net Boilerplate Query Performance in LINQPad, (Introducing LINQPad.ABP)

Ever made it to production only to realize your code fails miserably at scale? When performance problems rear their gnarly head and there name is EntityFramework, there is but one blade to slice that gordeon knot: LINQPad.  

However, if you're using the ASP.Net Boilerplate (ABP) framework, the situation is a tad more dire. That's because ABP uses a repository pattern, which, it turns out, is less than compatible with LINQPad's DataContext centric approach.

In this post I'll describe two ways to use LINQPad with ABP apps to solve performance problems:

1. Rewrite repository pattern based queries to run in LINQPad with a data context.  This works well for small problems.

2. Enable LINQPad to call directly into your code, support authentication, multi-tenancy, and the unit of work and repository patterns with the help of a NuGet packge I just released called LINQPad.ABP. This helps immensely for more complex performance problems.

Repository Pattern 101

The Repository and Unit of Work patterns used in ASP.Net Boilerplate apps are a wonderful abstraction for simplifying unit testing, enabling annotation based transactions, and handling database connection management.  As described in this article from the Microsoft MVC docs:

The repository and unit of work patterns are intended to create an abstraction layer between the data access layer and the business logic layer of an application. Implementing these patterns can help insulate your application from changes in the data store and can facilitate automated unit testing or test-driven development (TDD).

That document gives a nice diagram to show the difference between an architecture with and without a repository pattern:

The "No Repository" architecture shows direct access to database context from a controller. The "With Repository" architecture shows an abstraction layer between the controller and the database context and shows how unit tests can use a custom persistence layer to facilitate testing

However, these abstractions present a problem for LINQPad.  When using LINQPad to diagnose performance problems it would often be super convenient to call directly into your app's code to see the queries translated into SQL and executed.  However, even if LINQPad understood dependency injection, it would have no idea how to populate a IRepository<Thing, int>, what to do with a [UnitOfWork(TransactionScopeOption.RequiresNew)] attribute, or what value to return for IAbpSession.UserId or IAbpSession.TenantId.  Fortunately, I just released a NuGet Package to make that easy.  

But first, the simplest way to solve the problem for single queries is just to rewrite the query without a repository pattern and paste it into LINQPad.

Undoing Repository Pattern

This is where this blog post gets into the weeds.  If you'd rather watch me perform the following steps please check out my latest episode of Code Hour:

Otherwise, here it is in written form:

Step one is to enable ABP'S data context to support a constructor that takes a connection string.  If you add the following code to your DataContext:

#if DEBUG
    private string _connectionString;  

    /// <summary>
    /// For LINQPad
    /// </summary>
    public MyAppDbContext(string connectionString) : base(new DbContextOptions<MyAppDbContext>())
    {
        _connectionString = connectionString;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (_connectionString == null)
        {
            base.OnConfiguring(optionsBuilder); // Normal operation
            return;
        }

        // We have a connection string
        var dbContextOptionsBuilder = new DbContextOptionsBuilder();
        optionsBuilder.UseSqlServer(_connectionString);
        base.OnConfiguring(dbContextOptionsBuilder);
    }
#endif

Then in LINQPad you can:

  1. Add a connection
  2. "Use a typed data context from your own assembly"
  3. Select a Path to Custom Assembly like: "MyProjSolution\server\src\MyProj.Web.Host\bin\Debug\netcoreapp2.1\MyProj.EntityFrameworkCore.dll"
  4. Enter a Full Type Name of Typed DbContext like: "MyProj.EntityFrameworkCore.MyAppDbContext"
  5. LINQPad should instantiate your DbContext via a constructor that accepts a string, then provide your connection string

Now, when you start a new query you can write:

var thing = this.Things.Where(t => t.Id == 1);
thing.Dump();

And if you run it you'll see the resulting SQL statement.

Not bad.  If you paste in any real code you'll need to add using statements and replace _thingRepository.GetAll() with this.Things and you'll be translating LINQ to SQL in no time.

Pretty cool.  It certainly works for simple queries.

However, in my experience performance problems rarely crop up in the easy parts of the system.  Performance problems always seem to happen in places where multiple classes interact because there was simply too much logic for the author to have stuffed it all into one class and have been able to sleep at night.

Enter: LINQPad.ABP

To enable LINQPad to call directly into ABP code you'll need to set up dependency injection, define a module that starts up your core module, specify a current user and tenant to impersonate, and somehow override the default unit of work to use LINQPad's context rather than ABP's.  That's a lot of work.

Fortunately, I just published LINQPad.ABP, an Open Source NuGet Package that does all this for you.  To enable it:

  1. In LINQPad add a reference to "LINQPad.ABP"
  2. Add a custom Module that's dependent on your project's specific EF Module
  3. Create and Cache a LINQPad ABP Context
  4. Start a UnitOfWork and specify the user and tenant you want to impersonate

The code will look like this:

// you may need to depend on additional modules here eg MyProjApplicationModule
[DependsOn(typeof(MyProjEntityFrameworkModule))]
// this is a lightweight custom module just for LINQPad
public class LinqPadModule : LinqPadModuleBase {
	public LinqPadModule(MyProjEntityFrameworkModule abpProjectNameEntityFrameworkModule)
	{
        // tell your project's EF module to refrain from seeding the DB
		abpProjectNameEntityFrameworkModule.SkipDbSeed = true;
	}
	
	public override void InitializeServices(ServiceCollection services) {
        // add any custom dependency injection registrations here
		IdentityRegistrar.Register(services);
	}
}

async Task Main()
{
    // LINQPad.ABP caches (expensive) module creation in LINQPad's cache
	var abpCtx = Util.Cache(LinqPadAbp.InitModule<LinqPadModule>, "LinqPadAbp");

	// specify the tenant or user you want to impersonate here
    using (var uowManager = abpCtx.StartUow(this, tenantId: 5, userId: 8))
	{
        // retrieve what you need with IocManager in LINQPad.ABP's context
		var fundService = abpCtx.IocManager.Resolve<IFundService>();
		var entity = await thingService.GetEntityByIdAsync(1045);
		entity.Dump();
	}
}

That may look like a lot of code, but trust me, it's way simpler than it would otherwise be.  And now you can call into your code and watch every single query and how it gets translated to SQL.  

Summary

I hope this helps you track down a hard bug faster.  If maybe, then please subscribe, like and/or let me know on Twitter.