UsersTable override doesn't work

[belenos12345]

I've implemented a custom UsersTable to handle our custom IdentityUser:

public class AppUser : IdentityUser
    {
        [LogReportColumn(HeaderText = "User Name")]
        [ExcelMap(SourceFileMappingField = "User Name")]
        [Display(Name = "UserName", ResourceType = typeof(Resources.SharedResource))]
        public override string UserName { get; set; }

        [LogReportColumn(HeaderText = "Email")]
        [ExcelMap(SourceFileMappingField = "Email")]
        [ProtectedPersonalData, Required(ErrorMessageResourceName = "Required", ErrorMessageResourceType = typeof(Resources.SharedResource)), StringLength(256)]
        [EmailAddress(ErrorMessageResourceName = "EmailWrongFormat", ErrorMessageResourceType = typeof(Resources.SharedResource))]
        [Display(Name = "Email", ResourceType = typeof(Resources.SharedResource))]
        public override string Email { get; set; }

        [LogReportColumn(HeaderText = "First Name")]
        [ExcelMap(SourceFileMappingField = "First Name")]
        [PersonalData, Required(ErrorMessageResourceName = "Required", ErrorMessageResourceType = typeof(Resources.SharedResource)), StringLength(250)]
        [Display(Name = "FirstName", ResourceType = typeof(Resources.SharedResource))]
        public string FirstName { get; set; }

        [LogReportColumn(HeaderText = "Last Name")]
        [ExcelMap(SourceFileMappingField = "Last Name")]
        [PersonalData, Required(ErrorMessageResourceName = "Required", ErrorMessageResourceType = typeof(Resources.SharedResource)), StringLength(250)]
        [Display(Name = "LastName", ResourceType = typeof(Resources.SharedResource))]
        public string LastName { get; set; }
    }

public class AppUsersTable: UsersTable<AppUser, string, IdentityUserClaim<string>, IdentityUserRole<string>, IdentityUserLogin<string>, IdentityUserToken<string>>
    {
        public AppUsersTable(IDbConnectionFactory dbConnectionFactory) : base(dbConnectionFactory) { }

        public override async Task<bool> CreateAsync(AppUser user) {
            const string sql = "INSERT INTO [dbo].[AspNetUsers] " +
                               "VALUES (@Id, @UserName, @NormalizedUserName, @Email, @NormalizedEmail, @EmailConfirmed, @PasswordHash, @SecurityStamp, @ConcurrencyStamp, " +
                                       "@PhoneNumber, @PhoneNumberConfirmed, @TwoFactorEnabled, @LockoutEnd, @LockoutEnabled, @AccessFailedCount, @FirstName, @LastName);";
            var rowsInserted = await DbConnection.ExecuteAsync(sql, new {
                user.Id,
                user.UserName,
                user.NormalizedUserName,
                user.Email,
                user.NormalizedEmail,
                user.EmailConfirmed,
                user.PasswordHash,
                user.SecurityStamp,
                user.ConcurrencyStamp,
                user.PhoneNumber,
                user.PhoneNumberConfirmed,
                user.TwoFactorEnabled,
                user.LockoutEnd,
                user.LockoutEnabled,
                user.AccessFailedCount,
                user.FirstName,
                user.LastName
            });
            return rowsInserted == 1;
        }

        public override async Task<bool> UpdateAsync(AppUser user, IList<IdentityUserClaim<string>> claims, IList<IdentityUserRole<string>> roles, IList<IdentityUserLogin<string>> logins, IList<IdentityUserToken<string>> tokens) {
            const string updateUserSql =
                "UPDATE [dbo].[AspNetUsers] " +
                "SET [UserName] = @UserName, [NormalizedUserName] = @NormalizedUserName, [Email] = @Email, [NormalizedEmail] = @NormalizedEmail, [EmailConfirmed] = @EmailConfirmed, " +
                    "[PasswordHash] = @PasswordHash, [SecurityStamp] = @SecurityStamp, [ConcurrencyStamp] = @ConcurrencyStamp, [PhoneNumber] = @PhoneNumber, " +
                    "[PhoneNumberConfirmed] = @PhoneNumberConfirmed, [TwoFactorEnabled] = @TwoFactorEnabled, [LockoutEnd] = @LockoutEnd, [LockoutEnabled] = @LockoutEnabled, " +
                    "[AccessFailedCount] = @AccessFailedCount , [FirstName] = @FirstName, [LastName] = @LastName " +
                "WHERE [Id] = @Id;";
            using (var transaction = DbConnection.BeginTransaction()) {
                await DbConnection.ExecuteAsync(updateUserSql, new {
                    user.UserName,
                    user.NormalizedUserName,
                    user.Email,
                    user.NormalizedEmail,
                    user.EmailConfirmed,
                    user.PasswordHash,
                    user.SecurityStamp,
                    user.ConcurrencyStamp,
                    user.PhoneNumber,
                    user.PhoneNumberConfirmed,
                    user.TwoFactorEnabled,
                    user.LockoutEnd,
                    user.LockoutEnabled,
                    user.AccessFailedCount,
                    user.FirstName,
                    user.LastName,
                    user.Id
                }, transaction);
                if (claims?.Count() > 0) {
                    const string deleteClaimsSql = "DELETE " +
                                                   "FROM [dbo].[AspNetUserClaims] " +
                                                   "WHERE [UserId] = @UserId;";
                    await DbConnection.ExecuteAsync(deleteClaimsSql, new { UserId = user.Id }, transaction);
                    const string insertClaimsSql = "INSERT INTO [dbo].[AspNetUserClaims] (UserId, ClaimType, ClaimValue) " +
                                                   "VALUES (@UserId, @ClaimType, @ClaimValue);";
                    await DbConnection.ExecuteAsync(insertClaimsSql, claims.Select(x => new {
                        UserId = user.Id,
                        x.ClaimType,
                        x.ClaimValue
                    }), transaction);
                }
                if (roles?.Count() > 0) {
                    const string deleteRolesSql = "DELETE " +
                                                  "FROM [dbo].[AspNetUserRoles] " +
                                                  "WHERE [UserId] = @UserId;";
                    await DbConnection.ExecuteAsync(deleteRolesSql, new { UserId = user.Id }, transaction);
                    const string insertRolesSql = "INSERT INTO [dbo].[AspNetUserRoles] (UserId, RoleId) " +
                                                  "VALUES (@UserId, @RoleId);";
                    await DbConnection.ExecuteAsync(insertRolesSql, roles.Select(x => new {
                        UserId = user.Id,
                        x.RoleId
                    }), transaction);
                }
                if (logins?.Count() > 0) {
                    const string deleteLoginsSql = "DELETE " +
                                                   "FROM [dbo].[AspNetUserLogins] " +
                                                   "WHERE [UserId] = @UserId;";
                    await DbConnection.ExecuteAsync(deleteLoginsSql, new { UserId = user.Id }, transaction);
                    const string insertLoginsSql = "INSERT INTO [dbo].[AspNetUserLogins] (LoginProvider, ProviderKey, ProviderDisplayName, UserId) " +
                                                   "VALUES (@LoginProvider, @ProviderKey, @ProviderDisplayName, @UserId);";
                    await DbConnection.ExecuteAsync(insertLoginsSql, logins.Select(x => new {
                        x.LoginProvider,
                        x.ProviderKey,
                        x.ProviderDisplayName,
                        UserId = user.Id
                    }), transaction);
                }
                if (tokens?.Count() > 0) {
                    const string deleteTokensSql = "DELETE " +
                                                   "FROM [dbo].[AspNetUserTokens] " +
                                                   "WHERE [UserId] = @UserId;";
                    await DbConnection.ExecuteAsync(deleteTokensSql, new { UserId = user.Id }, transaction);
                    const string insertTokensSql = "INSERT INTO [dbo].[AspNetUserTokens] (UserId, LoginProvider, Name, Value) " +
                                                   "VALUES (@UserId, @LoginProvider, @Name, @Value);";
                    await DbConnection.ExecuteAsync(insertTokensSql, tokens.Select(x => new {
                        x.UserId,
                        x.LoginProvider,
                        x.Name,
                        x.Value
                    }), transaction);
                }
                try {
                    transaction.Commit();
                } catch {
                    transaction.Rollback();
                    return false;
                }
            }
            return true;
        }
        public override async Task<IEnumerable<AppUser>> GetUsersInRoleAsync(string roleName)
        {
            const string sql = "SELECT [u].* " +
                               "FROM [dbo].[AspNetUsers] AS [u] " +
                               "INNER JOIN [dbo].[AspNetUserRoles] AS [ur] ON [u].[Id] = [ur].[UserId] " +
                               "INNER JOIN [dbo].[AspNetRoles] AS [r] ON [ur].[RoleId] = [r].[Id] " +
                               "WHERE [r].[Name] = @RoleName;";
            var users = await DbConnection.QueryAsync<AppUser>(sql, new { RoleName = roleName });
            return users;
        }

        public override async Task<IEnumerable<AppUser>> GetUsersForClaimAsync(Claim claim)
        {
            const string sql = "SELECT [u].* " +
                               "FROM [dbo].[AspNetUsers] AS [u] " +
                               "INNER JOIN [dbo].[AspNetUserClaims] AS [uc] ON [u].[Id] = [uc].[UserId] " +
                               "WHERE [uc].[ClaimType] = @ClaimType AND [uc].[ClaimValue] = @ClaimValue;";
            var users = await DbConnection.QueryAsync<AppUser>(sql, new
            {
                ClaimType = claim.Type,
                ClaimValue = claim.Value
            });
            return users;
        }
    }

With Startup amended to:

var connectionString = Configuration.GetConnectionString("DefaultConnection");
           services.AddIdentity<AppUser, IdentityRole>(options =>
                {
                    options.SignIn.RequireConfirmedAccount = true;
                    options.User.RequireUniqueEmail = true;
                })
                .AddRoles<IdentityRole>()
                .AddDapperStores(options =>
                {
                    options.ConnectionString = connectionString;
                    options.AddUsersTable<AppUsersTable, AppUser>();
                })
                .AddDefaultTokenProviders();

everything works fine, except the insert and update of an AppUser. The overridden methods CreateUserAsync and UpdateAsync are not called. Do I miss something?

[giorgos07]

Hello @belenos12345 and thank you for using my package. Unfortunately there was a bug in the way that i was registering the custom tables in the DI mechanism. Your code is exactly what you should use. Please update to version 3.1.1 of the library and the problem should be resolved. If everything works as expected please let me know if i can close this issue.

[belenos12345]

Hi Georgios,

Thank you for the quick update and the effort! It's working like a charm now.

Best regards, Rayko

[Danieliessen]

Does anyone know how to rename table names, i tried overriding the Users table to rename the database name from AspNetUsers to Users, but the overriding still did not work..