Or any of its equivalent – FindByEmail/FindById etc.
Why?
Reason? It’s slow. Slow enough to effectively kill your database, and therefore, your website.
If you want dig into the default implementation (which is using EntityFramework), this is what you end up with, either if you are using FindByEmailAsync, or its synchronous equivalent FindByEmail
public virtual Task<TUser> FindByEmailAsync(string email)
{
this.ThrowIfDisposed();
return this.GetUserAggregateAsync((Expression<Func<TUser, bool>>) (u => u.Email.ToUpper() == email.ToUpper()));
}
It finds user by matching the email, but not before it uses ToUpper in both sides of the equation. This is to ensure correctness because an user can register with “[email protected]” but then try to login with “[email protected]”. If the database is set to be CS – case sensitive collation, that is not a match.
That is fine for C#/.NET, but it is bad for SQL Server. When it reaches database, this query is generated
(@p__linq__0 nvarchar(4000))SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[NewsLetter] AS [NewsLetter],
[Extent1].[IsApproved] AS [IsApproved],
[Extent1].[IsLockedOut] AS [IsLockedOut],
[Extent1].[Comment] AS [Comment],
[Extent1].[CreationDate] AS [CreationDate],
[Extent1].[LastLoginDate] AS [LastLoginDate],
[Extent1].[LastLockoutDate] AS [LastLockoutDate],
[Extent1].[Email] AS [Email],
[Extent1].[EmailConfirmed] AS [EmailConfirmed],
[Extent1].[PasswordHash] AS [PasswordHash],
[Extent1].[SecurityStamp] AS [SecurityStamp],
[Extent1].[PhoneNumber] AS [PhoneNumber],
[Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed],
[Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled],
[Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc],
[Extent1].[LockoutEnabled] AS [LockoutEnabled],
[Extent1].[AccessFailedCount] AS [AccessFailedCount],
[Extent1].[UserName] AS [UserName]
FROM [dbo].[AspNetUsers] AS [Extent1]
WHERE ((UPPER([Extent1].[Email])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent1].[Email]) IS NULL) AND (UPPER(@p__linq__0) IS NULL))
If you can’t spot the problem – don’t worry because I have seen experienced developers made the same mistake. By using the TOUPPER function on the column you are effectively remove any performance benefit of the index that might be on Email column. That means this query will do an index scan every time it is called. We have the TOP(1) statement which somewhat reduces the impact (it can stop as soon as it finds a match), but if there is no match – e.g. no registered email, it will be a full index scan.
If you have a lot of registered customers, frequent calls to that query can effectively kill your database.
And how to fix it
Fixing this issue will be a bit cumbersome, because the code is well hidden inside the implementation of AspNetIdentity EntityFramework. But it’s not impossible. First we need an UserStore which does not use the Upper for comparison:
public class FoundationUserStore<TUser> : UserStore<TUser> where TUser : IdentityUser, IUIUser, new()
{
public FoundationUserStore(DbContext context)
: base(context)
{ }
public override Task<TUser> FindByEmailAsync(string email)
{
return GetUserAggregateAsync(x => x.Email == email);
}
public override Task<TUser> FindByNameAsync(string name)
{
return GetUserAggregateAsync(x => x.UserName == name);
}
}
And then a new UserManager to use that new UserStore
public class CustomApplicationUserManager<TUser> : ApplicationUserManager<TUser> where TUser : IdentityUser, IUIUser, new()
{
public CustomApplicationUserManager(IUserStore<TUser> store)
: base(store)
{
}
public static new ApplicationUserManager<TUser> Create(IdentityFactoryOptions<ApplicationUserManager<TUser>> options, IOwinContext context)
{
var manager = new ApplicationUserManager<TUser>(new FoundationUserStore<TUser>(context.Get<ApplicationDbContext<TUser>>()));
// Configure validation logic for usernames
manager.UserValidator = new UserValidator<TUser>(manager)
{
AllowOnlyAlphanumericUserNames = false,
RequireUniqueEmail = true
};
// Configure validation logic for passwords
manager.PasswordValidator = new PasswordValidator
{
#if DEBUG
RequiredLength = 2,
RequireNonLetterOrDigit = false,
RequireDigit = false,
RequireLowercase = false,
RequireUppercase = false
#else
RequiredLength = 6,
RequireNonLetterOrDigit = true,
RequireDigit = true,
RequireLowercase = true,
RequireUppercase = true
#endif
};
// Configure user lockout defaults
manager.UserLockoutEnabledByDefault = true;
manager.DefaultAccountLockoutTimeSpan = TimeSpan.FromMinutes(5);
manager.MaxFailedAccessAttemptsBeforeLockout = 5;
var provider = context.Get<ApplicationOptions>().DataProtectionProvider.Create("EPiServerAspNetIdentity");
manager.UserTokenProvider = new DataProtectorTokenProvider<TUser>(provider);
return manager;
}
}
And then a way to register our UserManager
public static IAppBuilder AddCustomAspNetIdentity<TUser>(this IAppBuilder app, ApplicationOptions applicationOptions) where TUser : IdentityUser, IUIUser, new()
{
applicationOptions.DataProtectionProvider = app.GetDataProtectionProvider();
// Configure the db context, user manager and signin manager to use a single instance per request
app.CreatePerOwinContext<ApplicationOptions>(() => applicationOptions);
app.CreatePerOwinContext<ApplicationDbContext<TUser>>(ApplicationDbContext<TUser>.Create);
app.CreatePerOwinContext<ApplicationRoleManager<TUser>>(ApplicationRoleManager<TUser>.Create);
app.CreatePerOwinContext<ApplicationUserManager<TUser>>(CustomApplicationUserManager<TUser>.Create);
app.CreatePerOwinContext<ApplicationSignInManager<TUser>>(ApplicationSignInManager<TUser>.Create);
// Configure the application
app.CreatePerOwinContext<UIUserProvider>(ApplicationUserProvider<TUser>.Create);
app.CreatePerOwinContext<UIRoleProvider>(ApplicationRoleProvider<TUser>.Create);
app.CreatePerOwinContext<UIUserManager>(ApplicationUIUserManager<TUser>.Create);
app.CreatePerOwinContext<UISignInManager>(ApplicationUISignInManager<TUser>.Create);
// Saving the connection string in the case dbcontext be requested from none web context
ConnectionStringNameResolver.ConnectionStringNameFromOptions = applicationOptions.ConnectionStringName;
return app;
}
Finally, replace the normal app.AddAspNetIdentity with this:
app.AddCustomAspNetIdentity<SiteUser>(new ApplicationOptions
{
ConnectionStringName = commerceConectionStringName
});
As I mentioned, this is cumbersome to do. If you know a better way to do, I’m all ear ;).
We are also skipping the case sensitivity part. In most of the cases, it’ll be fine as you are most likely using CI collation instead. But it’s better to be sure than leave it to chance. We will address that in the second part of this blog post.
