ASP.NET Core 3.1 - User Entity


Ken Haggerty
Created 01/24/2020 - Updated 04/24/2020 15:00

This article will demonstrate the implementation of storing a user entity in a MS SQL database. I will assume you have created a new ASP.NET Core 3.1 Razor Pages project and implemented Cookie Authentication. See the first article of this series and Tutorial: Get started with Razor Pages in ASP.NET Core .

Users Without Identity Project and Article Series


Access to the research project source code may be purchased on KenHaggerty.Com at Manage > Assets.

A project which implements users without Identity has been published to demo.kenhaggerty.com.

I enjoy writing these articles. It often enhances and clarifies my coding. The research project is a result of a lot of refactoring and hopefully provides logical segues for the articles. Thank you for supporting my efforts.

For this article, I will store the user's password in plain text. The next article will migrate the user entity and store a hashed password. Using System. ComponentModel. DataAnnotations on the entity model will define the database structure. Key is required for CRUD. I will create an unique index on LoginNameUppercase in OnModelCreating for lookups by LoginName. This series will add and remove properties from the user entity and describe the database migrations.

Edit Entities > AppUser.cs:
public class AppUser
{
    [Key]
    public int Id { get; set; }

    [Required]
    [Display(Name = "Login Name")]
    [StringLength(32, ErrorMessage = "The {0} must be at least {2} and at max {1} characters long.", MinimumLength = 6)]
    public string LoginName { get; set; }

    [Required]
    [StringLength(32)]
    public string LoginNameUppercase { get; set; }

    [Required]
    [StringLength(32, ErrorMessage = "The {0} must be at least {2} and at max {1} characters long.", MinimumLength = 8)]
    public string Password { get; set; }
}

Now use the Nuget Manager to install new packages. Right click on the project then click Manage NuGet Packages. The Microsoft. EntityFrameworkCore. Tools package is required to update the database from the entity.

On the Browse tab, search and install the following packages:
  • Microsoft. EntityFrameworkCore
  • Microsoft. EntityFrameworkCore. SqlServer
  • Microsoft. EntityFrameworkCore. Tools
Installed NuGet Packages

Create a new root folder named Data. Add a new class named ApplicationDbContext to the Data folder.

ApplicationDbContext.cs:
public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    public DbSet<AppUser> AppUsers { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        // Set a unique constraint for LoginNameUppercase
        builder.Entity<AppUser>()
            .HasIndex(b => b.LoginNameUppercase)
            .IsUnique();
    }
}

Resolve all using statements. You should be able to build and run without errors.

Add a DefaultConnection to appsettings.json. I use LocalDB and a Trusted Connection.

Edit appsettings.json:
"ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb; Database=UsersWithoutIdentity; Trusted_Connection=True; MultipleActiveResultSets=true"
}

Inject ApplicationDbContext with the UseSqlServer option in ConfigureServices.

Edit Startup.cs > ConfigureServices:
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

    services.AddRazorPages(options =>
    {
        options.Conventions.AuthorizeFolder("/Admin");
    });

    services.AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
        .AddCookie();
}

Resolve all using statements. You should be able to build and run without errors.

I use the Package Manager Console in Visual Studio to execute my database migrations. I also store the migrations in the Data folder. PMC commands are different from the DotNet CLI commands. The CLI commands for migrations in EF Core v3.0 requires installing the dotnet-ef tool. See The EF Core command-line tool, dotnet ef, is no longer part of the .NET Core SDK .

Install dotnet-ef:
dotnet tool install --global dotnet-ef

Add the InitialUser migration.

Package Manager Console:
add-migration InitialUser -o Data/Migrations
DotNet CLI:
dotnet-ef migrations add InitialUser -o Data/Migrations

The add migration produces a MigrationBuilder which will create the AppUsers table with an auto incrementing primary key and unique index on LoginNameUppercase. Notice the DataAnnotations have defined nullable and maxLength. The file name prepends a date time stamp to InitialUser.

20200106213737_InitialUser.cs:
public partial class InitialUser : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "AppUsers",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                LoginName = table.Column<string>(maxLength: 32, nullable: false),
                LoginNameUppercase = table.Column<string>(maxLength: 32, nullable: false),
                Password = table.Column<string>(maxLength: 32, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_AppUsers", x => x.Id);
            });

        migrationBuilder.CreateIndex(
            name: "IX_AppUsers_LoginNameUppercase",
            table: "AppUsers",
            column: "LoginNameUppercase",
            unique: true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "AppUsers");
    }
}

If all looks good, update the database.

Package Manager Console:
update-database
DotNet CLI:
dotnet-ef database update

We should guarantee an Administrator user is always created in the database. I attempted this in Startup.cs > Configure, but could not execute asynchronous. Research found Running async tasks on app startup in ASP.NET Core 3.0 which described IHostedService.

Create a new root folder named Services.

Add a new class named EnsureAdministrator to the Services folder:
public class EnsureAdministrator : IHostedService
{
    // We need to inject the IServiceProvider so we can create the scoped service, ApplicationDbContext
    private readonly IServiceProvider _serviceProvider;
    public EnsureAdministrator(IServiceProvider serviceProvider)
    {
        _serviceProvider = serviceProvider;
    }

    public async Task StartAsync(CancellationToken cancellationToken)
    {
        // Create a new scope to retrieve scoped services
        using var scope = _serviceProvider.CreateScope();
        // Get the DbContext instance
        var applicationDbContext = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();

        //Add Administrator if not found
        string adminLoginName = "Administrator";
        int adminId = await applicationDbContext.AppUsers
            .AsNoTracking()
            .Where(a => a.LoginNameUppercase == adminLoginName.ToUpper())
            .Select(a => a.Id)
            .FirstOrDefaultAsync()
            .ConfigureAwait(false);

        if (adminId == 0)
        {
            var password = "P@ssw0rd";
            var adminUser = new AppUser
            {
                LoginName = adminLoginName,
                LoginNameUppercase = adminLoginName.ToUpper(),
                Password = password
            };

            applicationDbContext.AppUsers.Add(adminUser);
            try
            {
                applicationDbContext.SaveChanges();
            }
            catch (DbUpdateConcurrencyException)
            {
                throw new InvalidOperationException($"DbUpdateConcurrencyException occurred creating new admin user in EnsureAdminHostedService.cs.");
            }
            catch (DbUpdateException)
            {
                throw new InvalidOperationException($"DbUpdateException occurred creating new admin user in EnsureAdminHostedService.cs.");
            }
        }
    }

    // noop
    public Task StopAsync(CancellationToken cancellationToken) => Task.CompletedTask;
}

Resolve all using statements. Now we can use AddHostedService to call EnsureAdministrator which will execute asynchronous.

Edit Startup.cs > ConfigureServices:
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

    services.AddHostedService<EnsureAdministrator>();

    services.AddRazorPages(options =>
    {
        options.Conventions.AuthorizeFolder("/Admin");
    });

    services.AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
        .AddCookie();
}

Inject the ApplicationDbContext to LoginModel and update the AuthenticateUser function to verify credentials against the database.

Edit Login.cshtml.cs:
private readonly ApplicationDbContext _context;
public LoginModel(ApplicationDbContext context)
{
    _context = context;
}
Edit Login.cshtml.cs > AuthenticateUser:
private async Task<AppUser> AuthenticateUser(string login, string password)
{
    if (string.IsNullOrEmpty(login) || string.IsNullOrEmpty(password))
        return null;

    var user = await _context.AppUsers
        .AsNoTracking()
        .Where(a => a.LoginNameUppercase == login.ToUpper())
        .FirstOrDefaultAsync()
        .ConfigureAwait(false);

    if (user == null)
        return null;

    if (password != user.Password)
        return null;

    return user;
}

Resolve all using statements. Build, run and test. Login with Login Name = Administrator and Password = P@ssw0rd.

Update 04/24/2020

I published the User Database Service article and updated the article links. I implemented a UserService in the Users Without Identity Project at v1.0.6 and have updated the project's NuGet packages, details and download.


Article Tags:

Claims EF Core Model

Comment Count = 0

Please log in to comment or follow.

Login Register
Follow to get web notifications when new comments are posted to this article.
Logged in users receive web notifications for new articles, topics and assets.
Web Notifications