Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cannot override table names #17

Open
Danieliessen opened this issue Aug 28, 2020 · 0 comments
Open

Cannot override table names #17

Danieliessen opened this issue Aug 28, 2020 · 0 comments

Comments

@Danieliessen
Copy link

Danieliessen commented Aug 28, 2020

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant