AspIdentity EF and UserName

Question

I've implemented what i consider a fairly standard ASP Identity using Entity Framework. I've added a couple of properties to the standard AspNetUser table but I have the username column as a non nullable column.

However when the SignInManager processes the PasswordSignInAsync it generated the following sql:

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[FullName] AS [FullName], 
[Extent1].[ClientId] AS [ClientId], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[DateCreated] AS [DateCreated], 
[Extent1].[UserOid] AS [UserOid], 
[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].[UserName]) = UPPER(@p__linq__0)
) 
OR 
(
    (UPPER([Extent1].[UserName]) IS NULL) 
    AND (UPPER(@p__linq__0) IS NULL)
)

Can anyone tell me the following: 1. Why is EF generating the Is NULL clauses in the where clause when the username cannot be null? 2. How to stop this. 3. Is there anyway to stop the Upper statement for the Username?

Answer 1

1) this extra clause will be rulled out by SQL Server (presuming this is what you use) optimisation engine. But I suspect this is generated this way because your EF model does not have [Required] decorating Username property. And Username is a string and strings in .Net can be null. And when EF generates queries it does not check every column type - it looks on the the models(classes), so in EF eyes your Username column is nullable.

This is a good reason to use migrations and not modify the underlying DB by hand.

2) You see Upper because this is the way the linq query is written in the underlying storage in Identity (source, search for FindByNameAsync):

    public virtual Task<TUser> FindByNameAsync(string userName)
    {
        ThrowIfDisposed();
        return GetUserAggregateAsync(u => u.UserName.ToUpper() == userName.ToUpper());
    }

.ToUppse() here is getting translated by EF into Upper in your SQL.

You can avoid this by implementing your own UserStore. But this might be too much work for what it is worth.