Cannot override table names
Opened this issue · 0 comments
Danieliessen commented
Grabbed the test code from here: #14
I tried overriding database names by using the class AppUser.cs and AppUsersTable.cs and setting the table name from AspNetUsers to Users:
public class AppUser : IdentityUser
{
public override string Email { get; set; }
public override string UserName { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
AppUsersTable.cs:
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].[Users] " +
"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].[Users] " +
"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].[Users] 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].[Users] 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;
}
Startup.cs - ConfigureServices:
services.AddIdentity<IdentityUser, ExtendedIdentityRole>()
.AddDapperStores(options => {
options.AddRolesTable<ExtendedRolesTable, ExtendedIdentityRole>();
options.AddUsersTable<AppUsersTable, AppUser>();
})
.AddDefaultUI()
.AddDefaultTokenProviders();
//services.AddDefaultIdentity<IdentityUser>()
// .AddDapperStores();
services.Configure<IdentityOptions>(options =>
{
options.Password.RequireDigit = false;
options.Password.RequiredLength = 5;
options.Password.RequireLowercase = true;
options.Password.RequireNonAlphanumeric = false;
options.Password.RequireUppercase = false;
});
services.AddRazorPages();
services.AddServerSideBlazor();
services
.AddScoped<AuthenticationStateProvider, RevalidatingIdentityAuthenticationStateProvider<IdentityUser>
>();
services.AddSingleton<WeatherForecastService>();
Error:
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
An unhandled exception has occurred while executing the request.
System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'dbo.AspNetUsers'.
at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__126_0(Task`1 result)
at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object
state)
--- End of stack trace from previous location where exception was thrown ---
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
at Dapper.SqlMapper.QueryRowAsync[T](IDbConnection cnn, Row row, Type effectiveType, CommandDefinition
command) in /_/Dapper/SqlMapper.Async.cs:line 483
at AspNetCore.Identity.Dapper.UsersTable`6.FindByNameAsync(String normalizedUserName)
at AspNetCore.Identity.Dapper.UserStore`8.FindByNameAsync(String normalizedUserName, CancellationToken
cancellationToken)
at Microsoft.AspNetCore.Identity.UserManager`1.FindByNameAsync(String userName)
at Microsoft.AspNetCore.Identity.UserValidator`1.ValidateUserName(UserManager`1 manager, TUser user, ICollection`1
errors)
at Microsoft.AspNetCore.Identity.UserValidator`1.ValidateAsync(UserManager`1 manager, TUser user)
at Microsoft.AspNetCore.Identity.UserManager`1.ValidateUserAsync(TUser user)
at Microsoft.AspNetCore.Identity.UserManager`1.CreateAsync(TUser user)
at Microsoft.AspNetCore.Identity.UserManager`1.CreateAsync(TUser user, String password)
at Microsoft.AspNetCore.Identity.UI.V4.Pages.Account.Internal.RegisterModel`1.OnPostAsync(String returnUrl)
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory.GenericTaskHandlerMethod.Convert[T](Object
taskAsObject)
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory.GenericTaskHandlerMethod.Execute(Object receiver,
Object[] arguments)
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeHandlerMethodAsync()
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeNextPageFilterAsync()
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Rethrow(PageHandlerExecutedContext context)
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Next(State& next, Scope& scope, Object& state,
Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeInnerFilterAsync()
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker
invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker
invoker, Task lastTask, Stat
e next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean&
isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker
invoker, Task lastTask, Sta
te next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker,
Task
task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task
requestTask, ILogger logger)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
ClientConnectionId:0506a842-1daa-449b-af88-6630ae741bcb
Error Number:208,State:1,Class:16