ASP.NET Core Identity database operations are extremely inefficient.

I am a big fan of ASP.NET Core but its only sickening part is its Identity. All the database operations related to ASP.NET Core Identity has been written in an extremely inefficient way, maybe the possible worst way which is very frustrating to see when it is come from ASP.NET Core team.

**The code for ASP.NET Core Identity was written in ASP.NET Core 1.0 and EF Core 1.0 is still same as in ASP.NET Core 6.0 and EF Core 6.0. Both ASP.NET Core and EF Core have been evolved greatly and ASP.NET Core Identity still in legacy mode.**

Here are a few examples:

1. Look at the following code for creating a User:

```C#
ApplicationUser applicationUser = new ApplicationUser { UserName = email, Email = email, EmailConfirmed = true };
IdentityResult userCreationResult = await _userManager.CreateAsync(applicationUser);
```

The above code generating the following SQL queries and commands:

```
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[@__normalizedUserName_0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [a].[Id], [a].[AccessFailedCount], [a].[ConcurrencyStamp], [a].[DialCode], [a].[Email], [a].[EmailConfirmed], [a].[FullName], [a].[IsDisabled], [a].[LanguageCulture], [a].[LastLoggedInAtUtc], [a].[LockoutEnabled], [a].[LockoutEnd], [a].[NormalizedEmail], [a].[NormalizedUserName], [a].[PasswordHash], [a].[PhoneNumber], [a].[PhoneNumberConfirmed], [a].[SecurityStamp], [a].[TwoFactorEnabled], [a].[UserName]
FROM [AspNetUsers] AS [a]
WHERE [a].[NormalizedUserName] = @__normalizedUserName_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[@__normalizedEmail_0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [a].[Id], [a].[AccessFailedCount], [a].[ConcurrencyStamp], [a].[DialCode], [a].[Email], [a].[EmailConfirmed], [a].[FullName], [a].[IsDisabled], [a].[LanguageCulture], [a].[LastLoggedInAtUtc], [a].[LockoutEnabled], [a].[LockoutEnd], [a].[NormalizedEmail], [a].[NormalizedUserName], [a].[PasswordHash], [a].[PhoneNumber], [a].[PhoneNumberConfirmed], [a].[SecurityStamp], [a].[TwoFactorEnabled], [a].[UserName]
FROM [AspNetUsers] AS [a]
WHERE [a].[NormalizedEmail] = @__normalizedEmail_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (3ms) [Parameters=[@p0='?' (DbType = Guid), @p1='?' (DbType = Int32), @p2='?' (Size = 4000), @p3='?' (Size = 4), @p4='?' (Size = 50), @p5='?' (DbType = Boolean), @p6='?' (Size = 100), @p7='?' (DbType = Boolean), @p8='?' (Size = 4), @p9='?' (DbType = DateTime2), @p10='?' (DbType = Boolean), @p11='?' (DbType = DateTimeOffset), @p12='?' (Size = 50), @p13='?' (Size = 50), @p14='?' (Size = 4000), @p15='?' (Size = 15), @p16='?' (DbType = Boolean), @p17='?' (Size = 4000), @p18='?' (DbType = Boolean), @p19='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [AspNetUsers] ([Id], [AccessFailedCount], [ConcurrencyStamp], [DialCode], [Email], [EmailConfirmed], [FullName], [IsDisabled], [LanguageCulture], [LastLoggedInAtUtc], [LockoutEnabled], [LockoutEnd], [NormalizedEmail], [NormalizedUserName], [PasswordHash], [PhoneNumber], [PhoneNumberConfirmed], [SecurityStamp], [TwoFactorEnabled], [UserName])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19);
```

**Look at the above commands, it made two completely unnecessary queries and pulling the same big object twice just to check whether a User already exists or not by UserName which could have been done by a single simple query and getting 1 bit of data.**

2. Look at the following code:

```C#
IdentityResult addExternalLoginResult = await _userManager.AddLoginAsync(applicationUser, externalLoginInfo);
```

The above code generating the following SQL queries and commands:

```C#
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[@__normalizedUserName_0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [a].[Id], [a].[AccessFailedCount], [a].[ConcurrencyStamp], [a].[DialCode], [a].[Email], [a].[EmailConfirmed], [a].[FullName], [a].[IsDisabled], [a].[LanguageCulture], [a].[LastLoggedInAtUtc], [a].[LockoutEnabled], [a].[LockoutEnd], [a].[NormalizedEmail], [a].[NormalizedUserName], [a].[PasswordHash], [a].[PhoneNumber], [a].[PhoneNumberConfirmed], [a].[SecurityStamp], [a].[TwoFactorEnabled], [a].[UserName]
FROM [AspNetUsers] AS [a]
WHERE [a].[NormalizedUserName] = @__normalizedUserName_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[@__normalizedEmail_0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SELECT TOP(2) [a].[Id], [a].[AccessFailedCount], [a].[ConcurrencyStamp], [a].[DialCode], [a].[Email], [a].[EmailConfirmed], [a].[FullName], [a].[IsDisabled], [a].[LanguageCulture], [a].[LastLoggedInAtUtc], [a].[LockoutEnabled], [a].[LockoutEnd], [a].[NormalizedEmail], [a].[NormalizedUserName], [a].[PasswordHash], [a].[PhoneNumber], [a].[PhoneNumberConfirmed], [a].[SecurityStamp], [a].[TwoFactorEnabled], [a].[UserName]
FROM [AspNetUsers] AS [a]
WHERE [a].[NormalizedEmail] = @__normalizedEmail_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[@p0='?' (Size = 450), @p1='?' (Size = 450), @p2='?' (Size = 4000), @p3='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [AspNetUserLogins] ([LoginProvider], [ProviderKey], [ProviderDisplayName], [UserId])
VALUES (@p0, @p1, @p2, @p3);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (4ms) [Parameters=[@p19='?' (DbType = Guid), @p0='?' (DbType = Int32), @p1='?' (Size = 4000), @p20='?' (Size = 4000), @p2='?' (Size = 4), @p3='?' (Size = 50), @p4='?' (DbType = Boolean), @p5='?' (Size = 100), @p6='?' (DbType = Boolean), @p7='?' (Size = 4), @p8='?' (DbType = DateTime2), @p9='?' (DbType = Boolean), @p10='?' (DbType = DateTimeOffset), @p11='?' (Size = 50), @p12='?' (Size = 50), @p13='?' (Size = 4000), @p14='?' (Size = 15), @p15='?' (DbType = Boolean), @p16='?' (Size = 4000), @p17='?' (DbType = Boolean), @p18='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [AspNetUsers] SET [AccessFailedCount] = @p0, [ConcurrencyStamp] = @p1, [DialCode] = @p2, [Email] = @p3, [EmailConfirmed] = @p4, [FullName] = @p5, [IsDisabled] = @p6, [LanguageCulture] = @p7, [LastLoggedInAtUtc] = @p8, [LockoutEnabled] = @p9, [LockoutEnd] = @p10, [NormalizedEmail] = @p11, [NormalizedUserName] = @p12, [PasswordHash] = @p13, [PhoneNumber] = @p14, [PhoneNumberConfirmed] = @p15, [SecurityStamp] = @p16, [TwoFactorEnabled] = @p17, [UserName] = @p18
WHERE [Id] = @p19 AND [ConcurrencyStamp] = @p20;
SELECT @@ROWCOUNT;
```

**This is even more disastrous! Just for single insert and related checking, it's making a complete massacre. This is in no way acceptable. Even the following command from the above code is completely unnecessary:**

```C#
UPDATE [AspNetUsers] SET [AccessFailedCount] = @p0, [ConcurrencyStamp] = @p1, [DialCode] = @p2, [Email] = @p3, [EmailConfirmed] = @p4, [FullName] = @p5, [IsDisabled] = @p6, [LanguageCulture] = @p7, [LastLoggedInAtUtc] = @p8, [LockoutEnabled] = @p9, [LockoutEnd] = @p10, [NormalizedEmail] = @p11, [NormalizedUserName] = @p12, [PasswordHash] = @p13, [PhoneNumber] = @p14, [PhoneNumberConfirmed] = @p15, [SecurityStamp] = @p16, [TwoFactorEnabled] = @p17, [UserName] = @p18
WHERE [Id] = @p19 AND [ConcurrencyStamp] = @p20;
SELECT @@ROWCOUNT;
```

3. Another instance, I have already submitted here: https://github.com/dotnet/aspnetcore/issues/32933
4. Every UserManager method is expecting the IdentityUser while the only UserId is sufficient.

Last but not the least, I am sorry but to say, the whole ASP.NET Core Identity database operation code has been written in a possible worst way. It could have been accepted if was written by any other third party but it's from Microsoft where we believe the world's best software engineers do work.

Thank you.