(16/30)Learning Blazor Together: Creating a Database

We now have basic logging, but every time we input and reload the page, the data resets because it only exists in the browser and hasn’t been saved to a database. To persist it, we need to connect to a database.

(Note: Blazor WebAssembly cannot directly interact with a database, but Microsoft provides the ASP.NET Core hosted option, which allows creating an ASP.NET Core Web API project alongside the Blazor WebAssembly project.)

Entity Framework Core

First, add a connection string in appsettings.json. For testing, we use a local SQL Server database.

{
  "ConnectionStrings": {
    "DBConnection": "Server=(localdb)\\MSSQLLocalDB;database=Blog;integrated security=true;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Then, use NuGet to download two packages: Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Tools. These are ORM components for interacting with SQL Server. What is ORM?

Project file: BlazorServer.csproj

<Project Sdk="Microsoft.NET.Sdk.Web">

	<PropertyGroup>
		<TargetFramework>net6.0</TargetFramework>
		<Nullable>enable</Nullable>
		<ImplicitUsings>enable</ImplicitUsings>
	</PropertyGroup>

	<ItemGroup>
	  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.1" />
	  <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.1">
	    <PrivateAssets>all</PrivateAssets>
	    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
	  </PackageReference>
	</ItemGroup>

</Project>

In the early days, developers had to learn SQL statements to retrieve data from SQL Server. Later, the .NET platform (including VB and C#) developed ADO.NET as a convenient tool to retrieve data and map it to the program. However, ADO.NET was not ideal for data mapping, so Microsoft introduced Entity Framework, the original ORM (Object-Relational Mapper). It allows developers to treat data as entities without directly writing SQL statements, and provided a designer to improve usability.

Some developers found Entity Framework too heavyweight and switched to Dapper, a lighter tool. The author has used Dapper as well; it's convenient for those who prefer to write their own SQL statements.

For the cross-platform .NET Core, the ORM used is Entity Framework Core for data mapping.

After installing the packages, create a new class AppDbContext that inherits from DbContext. In its constructor, pass DbContextOptions<AppDbContext> options to the base class.

AppDbContext.cs

using Microsoft.EntityFrameworkCore;

namespace BlazorServer.Models;

public class AppDbContext : DbContext
{
	public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
	{
	}

	public DbSet<BlogModel>? Blogs { get; set; }

	public DbSet<PostModel>? Posts { get; set; }
}

Next, open Program.cs and register the database. Here we use UseSqlServer(). Of course, UseMySql() and UseOracle() are also available after installing the corresponding packages.

Open the Package Manager Console and run the command Add-Migration Init, telling Entity Framework Core to create a database migration. A migration folder Migration will be created. This is a feature of Entity Framework Core: it generates a migration intermediary between the program and the database. One advantage of migrations is that you can modify them multiple times before confirming, and only then update the database.

Relation between Blog and Post

Next, add BlogId to the Post model. For Post, Blog is the parent table to the child table. If you don't include a foreign key reference to the parent table when adding a migration, EF Core will add one automatically, but the field name will be ModelKey, e.g., BlogModelBlogId. We want to simplify the name.

Add two properties to PostModel: BlogId and Blog. It is important to add them exactly this way. BlogId is for the database, and Blog provides an entity for navigation so we don't have to manually join tables. This will be explained later.

Then use Remove-Migration to delete the previous migration, and add a new migration using the command Add-Migration Init. The new migration now has the simplified name BlogId. At this point, update the database.

Run the command Update-Database to update the database. Then open SQL Server Object Explorer to find the newly created database Blog, named after the database in the connection string. The database is built without using any SQL statements or SSMS interface.

One important point: if you change databases midway, the existing migration is very likely to cause problems because different databases have different data types. It's best to decide which database to use from the start.

Besides the common AddDbContext<T> approach, there is also AddDbContextFactory<T>, which creates a new DbContext for individual components. Because AddDbContext<T> has a scoped lifetime, in Blazor Server the DbContext won't be disposed until the system is closed. If you want the lifetime to be limited to the component only, you can use AddDbContextFactory<T>.

References:

1. Entity Framework
2. Dapper
3. Database Providers
4. New DbContext instances

Note: This article code has been refactored with .NET 6 + Visual Studio 2022. You can compare the original link with the refactored code. Thank you for reading and supporting the original author.