EF CORE - IDENTITY AUTHENTICATION WITH SQLITE

ENTITY FRAMEWORK CORE (EF CORE) — PART 3: IDENTITY AUTHENTICATION WITH SQLITE

PART 2 added Product CRUD pages. This tutorial adds ASP.NET Core Identity to dotnetef. Guests can open public pages, visitors can register and log in, and only authenticated users can open the new Members page.

1. What will be added?

Feature	Route	Access
Home	/	Guest and authenticated users
Privacy	/Privacy	Guest and authenticated users
Products	/Products	Guest and authenticated users for now
Register	/Identity/Account/Register	Guests
Login	/Identity/Account/Login	Guests
Members	/Members	Authenticated users only

This part uses a second SQLite database, Data/identity.db. Product records remain in Data/products.db.

2. Confirm the project starting point

cd ~/dotnetprojects/dotnetef
dotnet build
sqlite3 Data/products.db ".tables"

The build must succeed and the Product database should contain Products.

3. Install the ASP.NET Core Identity packages

dotnet add package \
  Microsoft.AspNetCore.Identity.EntityFrameworkCore \
  --version 8.0.30

dotnet add package \
  Microsoft.AspNetCore.Identity.UI \
  --version 8.0.30

dotnet restore
dotnet build

These packages provide the Identity database model, Entity Framework Core stores and the built-in Register, Login, Logout and account-management pages.

4. Add the Identity connection string

Open appsettings.json. Keep ProductDatabase and add IdentityDatabase:

{
  "ConnectionStrings": {
    "ProductDatabase": "Data Source=Data/products.db",
    "IdentityDatabase": "Data Source=Data/identity.db"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

5. Create ApplicationDbContext

Create Data/ApplicationDbContext.cs:

using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;

namespace dotnetef.Data;

public class ApplicationDbContext
    : IdentityDbContext
{
    public ApplicationDbContext(
        DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
}

IdentityDbContext supplies the EF Core model for users, roles, claims, logins and tokens.

6. Configure Identity in Program.cs

Replace Program.cs with the following complete code:

using dotnetef.Data;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;

SQLitePCL.Batteries_V2.Init();

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddRazorPages();

builder.Services.AddDbContext<ProductDbContext>(options =>
    options.UseSqlite(
        builder.Configuration.GetConnectionString(
            "ProductDatabase"
        )
    )
);

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite(
        builder.Configuration.GetConnectionString(
            "IdentityDatabase"
        )
    )
);

builder.Services
    .AddDefaultIdentity<IdentityUser>(options =>
    {
        options.SignIn.RequireConfirmedAccount = false;
        options.Password.RequiredLength = 6;
        options.Password.RequireNonAlphanumeric = false;
    })
    .AddEntityFrameworkStores<ApplicationDbContext>();

var app = builder.Build();

if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Error");
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthentication();
app.UseAuthorization();

app.MapRazorPages();

app.Run();

UseAuthentication() must appear before UseAuthorization(). The first identifies the user; the second checks whether that user may open a resource.

7. Create the Identity migration and database

dotnet ef migrations add CreateIdentitySchema \
  --context ApplicationDbContext \
  --output-dir Migrations/Identity

dotnet ef database update \
  --context ApplicationDbContext

Verify the new database:

sqlite3 Data/identity.db ".tables"

The output should include tables such as AspNetUsers, AspNetRoles, AspNetUserClaims and __EFMigrationsHistory.

8. Add the Login and Register navigation

Create Pages/Shared/_LoginPartial.cshtml:

@using Microsoft.AspNetCore.Identity
@inject SignInManager<IdentityUser> SignInManager
@inject UserManager<IdentityUser> UserManager

<ul class="navbar-nav">
@if (SignInManager.IsSignedIn(User))
{
    <li class="nav-item">
        <a class="nav-link text-dark"
           asp-area="Identity"
           asp-page="/Account/Manage/Index">
            Hello @User.Identity?.Name!
        </a>
    </li>
    <li class="nav-item">
        <form method="post"
              asp-area="Identity"
              asp-page="/Account/Logout"
              asp-route-returnUrl="@Url.Page("/Index")">
            <button type="submit"
                    class="nav-link btn btn-link text-dark">
                Logout
            </button>
        </form>
    </li>
}
else
{
    <li class="nav-item">
        <a class="nav-link text-dark"
           asp-area="Identity"
           asp-page="/Account/Register">Register</a>
    </li>
    <li class="nav-item">
        <a class="nav-link text-dark"
           asp-area="Identity"
           asp-page="/Account/Login">Login</a>
    </li>
}
</ul>

Open Pages/Shared/_Layout.cshtml. Inside the navigation container, place this immediately after the main navigation list:

<partial name="_LoginPartial" />

9. Create an authenticated Members page

Create Pages/Members.cshtml:

@page
@model dotnetef.Pages.MembersModel
@{
    ViewData["Title"] = "Members";
}

<h1>Members Area</h1>
<p>Welcome, @User.Identity?.Name.</p>
<p>Only authenticated users can view this page.</p>

Create Pages/Members.cshtml.cs:

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace dotnetef.Pages;

[Authorize]
public class MembersModel : PageModel
{
    public void OnGet()
    {
    }
}

The [Authorize] attribute protects the page.

10. Add Members and Products to the navigation bar

In the main navigation list in Pages/Shared/_Layout.cshtml, add:

<li class="nav-item">
    <a class="nav-link text-dark"
       asp-page="/Products/Index">Products</a>
</li>
<li class="nav-item">
    <a class="nav-link text-dark"
       asp-page="/Members">Members</a>
</li>

The Members link may be visible to guests, but the page itself remains protected. This is useful for demonstrating automatic redirection to Login.

11. Build and run the application

dotnet build
dotnet run

Open the HTTP or HTTPS address displayed in the terminal.

12. Test guest pages

While logged out, test:

• /
• /Privacy
• /Products

These pages should open without requiring an account.

13. Test the protected page

While logged out, browse to:

/Members

Identity should redirect the browser to a URL similar to:

/Identity/Account/Login?ReturnUrl=%2FMembers

The ReturnUrl tells Identity where to return after a successful login.

14. Register a user

1. Click Register.
2. Enter an email address.
3. Enter and confirm a password.
4. Click Register.

Because account confirmation is disabled for this local tutorial, the new user can sign in immediately.

15. Login, open Members and logout

1. Click Login and enter the registered credentials.
2. Open /Members. The protected content should now appear.
3. Click Logout.
4. Try /Members again. The application should return to Login.

16. Verify the registered user in SQLite

Stop the web application with Ctrl+C, then run:

sqlite3 Data/identity.db \
  "SELECT Id, UserName, Email FROM AspNetUsers;"

Identity stores a password hash, not the user's original password. Never attempt to store plain-text passwords.

17. Understand authentication and authorization

Concept	Question	Example
Authentication	Who is the user?	Login verifies the account
Authorization	May the user open this page?	[Authorize] protects Members
Guest page	Is login optional?	Home, Privacy and Products
Protected page	Is login required?	Members

18. Common problems

Problem	Remedy
ApplicationDbContext cannot be found	Confirm the file is under Data, its namespace is dotnetef.Data, and Program.cs imports that namespace
Identity migration uses the wrong context	Include --context ApplicationDbContext
no such table: AspNetUsers	Run dotnet ef database update --context ApplicationDbContext
Register or Login gives 404	Install Microsoft.AspNetCore.Identity.UI, call AddDefaultIdentity(), and keep app.MapRazorPages()
Members opens without login	Confirm [Authorize] is above MembersModel and authentication middleware is configured
Repeated redirect to Login	Confirm UseAuthentication() appears before UseAuthorization()
Password is rejected	Use at least six characters; the tutorial does not require a symbol

What has been achieved?

• ASP.NET Core Identity uses a dedicated SQLite database.
• Guests can access Home, Privacy and Products.
• Visitors can register, log in and log out.
• The Members page requires authentication.
• Identity redirects guests to Login and returns them afterward.
• Registered accounts can be inspected in SQLite.

PART 3 complete: dotnetef now supports guest pages, registration, login, logout and an authenticated Members page.

Official references

• Microsoft Learn: Introduction to Identity on ASP.NET Core
• Microsoft Learn: Simple authorization
• Microsoft Learn: EF Core SQLite provider

Next: ENTITY FRAMEWORK CORE (EF CORE) — PART 4 protects Product Create, Edit and Delete while allowing guests to browse the Product list and details.