Wednesday 9 April 2014

Altering the schema name of ASP.NET Identity tables

I like keeping my database schema as clean and readable as possible, and one of the things I do is use a schema name to group related functionality.

I’ve been doing a fair bit of work with ASP.NET Identity recently, and one of the things was to add the generated tables for identity into their own schema. Moreover, I also wanted to change the name of the tables. This is a snap using Code First – all you need to do (in your IdentityDbContext derived class) is the following…

    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<DbUser>().ToTable("Users", "AUTH");
    modelBuilder.Entity<IdentityUserRole>().ToTable("UserRoles", "AUTH");
    modelBuilder.Entity<IdentityUserLogin>().ToTable("ExternalLogins", "AUTH");
    modelBuilder.Entity<IdentityUserClaim>().ToTable("UserClaims", "AUTH");
    modelBuilder.Entity<IdentityRole>().ToTable("Roles", "AUTH");

Make sure you call the base class OnModelCreating method before including these changes. With that you’ll get a nice set of tables inside SQL server…

image

I realise that this will mean that I need to be careful when installing a new version of ASP.NET Identity (to ensure that the override still works), but I’d rather pay that price and have a ‘clean’ schema.

Monday 7 April 2014

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!