This article will demonstrate the implementation of storing a user entity in a MS SQL database. I will assume you have downloaded the ASP.NET Core 3.1 - Users Without Identity Project or created a new ASP.NET Core 3.1 Razor Pages project. See Tutorial: Get started with Razor Pages in ASP.NET Core. You should review the earlier articles of the Users Without Identity Project series.

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

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 02/23/2021

I added the Enhanced User Series' article links.

Article references: