Optimizing ASP.Net Identity Database Access

I have to say I’m a bit obsessive about issuing SQL requests as they take time, and so the fewer requests I can make the better.

Recently I’ve installed the latest version of ASP.NET Identity (2.0.0.0) and whilst running SQL Profiler I noticed something odd being executed for every instance of my DbContext class. What I was expecting was one SQL statement (that being the one I was explicitly issuing), however what I actually saw in the profiler was three…

(1) SELECT Count(*) FROM sys.databases WHERE [name]=N'VPC'


(2) exec sp_executesql N'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
         where TABLE_NAME=@Table',N'@Table nvarchar(11)',@Table=N'AspNetUsers'

(3) SELECT 
    [Extent1].[PublisherId] AS [PublisherId], 
    [Extent1].[ApiKey] AS [ApiKey], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Description] AS [Description]
    FROM [VPC].[Publisher] AS [Extent1]
    ORDER BY [Extent1].[Name] ASC

The one I was expecting was #3, the other two were unexpected and also unwanted. So I had to find out where they were coming from.

When I start hunting I always think “it’s my fault, I’ve caused this somehow” – and to be honest it almost always is caused by my code. But I was pretty sure that this wasn’t my code – or at least, it wasn’t cause by my code directly. What followed was an hour or so of fruitless searching in my project – I began with an EF profiler, no change, then I added some extra debug, no change, wired up some logging for Owin to see if it was one of the bits of middleware added into the pipeline, still no change, none was telling me what I needed to know.

I then started hunting on the web, and happened to find just what I needed on www.symbolsource.org, specifically the code for the IdentityDbContext class (which was where I’d hazarded a guess the problem was). I was (as most people will do) deriving from IdentityDbContext<TUser> which I’d assumed was benign but it wasn’t. It contains the following code in the default constructor…

  public IdentityDbContext(string nameOrConnectionString)
    : this(nameOrConnectionString, true)
  {
  }

And looking at the next constuctor...

  public IdentityDbContext(string nameOrConnectionString, bool throwIfV1Schema)
    : base(nameOrConnectionString)
  {
    if (throwIfV1Schema && IsIdentityV1Schema(this))
    {
       throw new InvalidOperationException(IdentityResources.IdentityV1SchemaError);
    }
  }

Not much going on there - or is there?. If you look at the code for the IsIdentityV1Schema function it reveals all - it's checking if the database exists, and it's also doing the column name lookup.

So, to remove the two additional SQL statements from your code, you need to pass false as the second parameter to the IdentityDbContext constructor…

  public class DbApplicationRepository 
  : IdentityDbContext, IDbApplicationRepository
  {
    public DbApplicationRepository(string connectionString) 
      : base(connectionString, false)
    {
    }

    ...
  }

Hopefully this will help someone!