Asp-Net-Core Development Notes: Quickly Introducing EFCore into an Existing Project

Introduction

Many projects don't choose EFCore initially during the technology selection phase, but EFCore is indeed very useful. For various reasons, you may need to implement it later, and introducing EFCore at that point is also quite convenient.

This article uses StarBlog as an example. StarBlog currently uses FreeSQL as its ORM. The biggest advantage of introducing EFCore for me is the support for multiple databases. With FreeSQL, service registration uses the singleton pattern, which only allows connection to a single database. If you need to connect to multiple databases simultaneously using FreeSQL, you would have to do some additional work yourself.

The desired effect is to store access records in a separate database and manage them using EFCore.

Installation Tools

First, install the EFCore CLI tool

dotnet tool install --global dotnet-ef

Project Architecture

Let's first review the project architecture: Developing a Blog Project Based on .NetCore StarBlog - (2) Environment Preparation and Project Creation

StarBlog
├── StarBlog.Contrib
├── StarBlog.Data
├── StarBlog.Migrate
├── StarBlog.Web
└── StarBlog.sln

For decoupling, code related to data is placed under the StarBlog.Data project. Therefore, to introduce EFCore (Entity Framework Core), you only need to add the dependency in the StarBlog.Data project.

Add Dependencies

Add the following three dependencies in the StarBlog.Data project:

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.Sqlite
• Microsoft.EntityFrameworkCore.Tools

EFCore's support for SQLite is quite weak (the root cause is that the SQLite driver provided by Microsoft has too few features), so it is only suitable for local development and experimentation. For actual deployment, you must switch to a client/server architecture database (PgSQL/MySQL/SQL Server).

After adding them, the project's .csproj file will have new dependencies similar to this:

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

Currently, StarBlog is still using .Net6, so the EFCore version I added is 6.x. After the official release of .Net8, I will upgrade this project to .Net8.

Creating DbContext

DbContext is the entry point for EFCore to interact with the database. Typically, there is one for each database.

Now let's create one under the StarBlog.Data project.

using Microsoft.EntityFrameworkCore;
using StarBlog.Data.Models;

namespace StarBlog.Data;

public class AppDbContext : DbContext {
  public DbSet<VisitRecord> VisitRecords { get; set; }
  
  public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
}

Since we only need EFCore to manage visit records, a single DbSet is sufficient.

Entity Class Configuration

Then create a configuration. Although Data Annotations can also be used for configuration, EFCore recommends using the Fluent API approach to configure data tables and fields.

Create the file StarBlog.Data/Config/VisitRecordConfig.cs

public class VisitRecordConfig : IEntityTypeConfiguration<VisitRecord> {
  public void Configure(EntityTypeBuilder<VisitRecord> builder) {
    builder.ToTable("visit_record");
    builder.HasKey(e => e.Id);
    builder.Property(e => e.Ip).HasMaxLength(64);
    builder.Property(e => e.RequestPath).HasMaxLength(2048);
    builder.Property(e => e.RequestQueryString).HasMaxLength(2048);
    builder.Property(e => e.RequestMethod).HasMaxLength(10);
    builder.Property(e => e.UserAgent).HasMaxLength(1024);
  }
}

This primarily configures the primary key and the length of each field.

Regarding data types, EFCore handles the automatic mapping. Please refer to the official documentation for specifics.

Primary Key Type Selection

Let's take a brief detour to discuss how to choose the primary key type.

Currently, there are mainly several approaches:

• Auto-increment
• GUID
• Auto-increment + GUID
• Hi/Lo

Each of these has its own advantages and disadvantages.

• The advantage of auto-increment is its simplicity, but its drawback is that it can cause issues during database migration or in distributed systems, and it suffers from poor insertion performance under high concurrency.
• GUID also has the advantage of simplicity and convenience, and it is suitable for distributed systems. However, MySQL's InnoDB engine enforces the use of clustered indexes for primary keys, which means each newly inserted row must go through a process of finding the appropriate insertion position. This leads to very poor insertion performance when dealing with large volumes of data.
• The auto-increment + GUID approach uses the auto-increment field as the physical primary key and the GUID as the logical primary key. This can partially solve the problems associated with the two methods mentioned above.
• Hi/Lo can optimize the performance of auto-increment columns, but it is only supported by some databases, such as SQL Server. I haven't researched its support in other databases yet.

DesignTime Configuration

Since our project separates AspNetCore from the data layer, a DesignTime configuration is needed for EFCore to know how to execute migrations.

Create the AppDesignTimeDbContextFactory.cs file in StarBlog.Data

public class AppDesignTimeDbContextFactory : IDesignTimeDbContextFactory<AppDbContext> {
  public AppDbContext CreateDbContext(string[] args) {
    var builder = new DbContextOptionsBuilder<AppDbContext>();

    var connStr = Environment.GetEnvironmentVariable("CONNECTION_STRING");
    if (connStr == null) {
      var dbpath = Path.Combine(Environment.CurrentDirectory, "app.log.db");
      connStr = $"Data Source={dbpath};";
    }

    builder.UseSqlite(connStr);
    return new AppDbContext(builder.Options);
  }
}

This reads the database connection string from the environment variable, falling back to a default database file if it's not available.

Database Migration

This section mainly uses two sets of commands:

• migrations - Used to monitor database changes
• database - Syncs the changes to the database

Navigate to the StarBlog.Data directory and execute:

dotnet ef migrations add InitialCreate -o Migrations 

After this, you can see the migration code generated in the Migrations directory.

If you need to specify the database file, you can set the environment variable.

For Windows:

set CONNECTION_STRING = "Data Source=app.db;"

For Linux, it's similar, just replace set with export:

export CONNECTION_STRING = "Data Source=app.db;"

Run the following command to sync to the database:

dotnet ef database update

After execution, a SQLite database file will be generated under StarBlog.Data.

Integrating EFCore in an ASP.NET Core Project

First, write the database connection string in the configuration file appsettings.json.

{
  "ConnectionStrings": {
    "SQLite": "Data Source=app.db;Synchronous=Off;Cache Size=5000;",
    "SQLite-Log": "Data Source=app.log.db;"
  }
}

Register the service in Program.cs.

builder.Services.AddDbContext<AppDbContext>(options => {
  options.UseSqlite(builder.Configuration.GetConnectionString("SQLite-Log"));
});

Done!

Database-First

Generate entity classes from an existing database. This development approach is generally not recommended for new projects, but it is quite convenient for use on legacy projects. EF Core's CLI tools also provide rich code generation capabilities.

Here is an example:

• Use a PostgreSQL database to generate entity classes for all tables in the pe_shit_data database.
• Generate a DbContext class named ShitDbContext.
• The namespace for the DbContext class is PE.Data.
• Place the entity classes in the ShitModels directory with the namespace PE.Data.ShitModels.

The command is as follows:

dotnet ef dbcontext scaffold `
    "Host=cuc.dou3.net;Database=pe_shit_data;Username=postgres;Password=passw0rd" `
    Npgsql.EntityFrameworkCore.PostgreSQL `
    -f `
    -c ShitDbContext `
    --context-dir . `
    --context-namespace PE.Data `
    -o ShitModels `
    --namespace PE.Data.ShitModels `

This is a PowerShell command. If you are in a Linux environment, simply replace the backtick (\``) at the end of each line with a backslash (`).

References

• https://learn.microsoft.com/en-us/ef/core/cli/dotnet
• https://learn.microsoft.com/zh-cn/ef/core/managing-schemas/scaffolding