This post shares an experience of using EF Core scaffolding to change data access technology from ADO.NET to EF-Core in a small .NET CLI tool project.

The project is Achievement Quantifier., which tracks and manages achievements. It uses Spectre.Console commands for the user interface and ADO.NET for data access. Another post introduces its implementation from zero to the first deployment.

The motivation for this change is twofold:

• Experiment with and get to know EF-Core scaffolding features.
• Make future AQ data model changes easier to handle by relying on EF-Core data migrations.

Access the project’s source code on GitHub 📁, check the initial state 0️⃣, explore the pull request ➡️, and view the final state 1️⃣.

Scaffold

In EF-Core, to scaffold a database means to generate entity and database context classes from an existing database. This process is well-documented in the official EF Core documentation.

Prepare the Project

Currently, the solution is structured into three projects: AQ.Console, AQ.Data, and AQ.Models:

The plan is to add a new AQ.Domain project to hold the generated code, and then delete no longer necessary AQ.Models and AQ.Data projects. Separating the old and new code in this way helps avoid any interference between the two

EF-Core also requires the following two packages:

• Microsoft.EntityFrameworkCore.Design for AQ.Console to use dotnet ef CLI tool.
• Microsoft.EntityFrameworkCore.Sqlite for AQ.Domain to define DbContext with Sqlite provider.

The first commit applies these changes.

Get the Connection String

The Application Quantifier project keeps the database in the OS-dependent Application Data special folder.

On macOS, the full path can be obtained by locating the database file in Finder, right-clicking on it, and selecting "Copy as Pathname" while holding the Option key:

Once the full path is retrieved it can be set as the data source in the connection string:

> CONNECTIONSTRING='Data Source=...'

Execute the Scaffolding

Now everything is ready to execute the scaffolding, which can be done with the following command from the solution directory:

> dotnet ef dbcontext scaffold $CONNECTIONSTRING Microsoft.EntityFrameworkCore.Sqlite -s AQ.Console -p AQ.Domain

It creates the DataContext class (the name Data comes from the name of the database file data.db) and two entity classes - Achievement and AchievementClass.

The second commit shows the auto-generated files and also configures the connection string.

Enable Database Migrations

Review Entity Models

The generated entity classes require a few adjustments. In particular, the Id properties are detected as int but they must be long. The partial keyword is used for repeated scaffolding to keep generated code and manually added code in separate files. It does not apply to once-off scaffolding like here, and is thus removed. The redundant using directives are also removed. And finally, the ToString() method is copied from old entities.

Once entities are ready, the first migration can be generated with the following command:

> dotnet ef migrations add InitialMigration -s AQ.Console -p AQ.Domain

The third commit shows the result.

Resolve First Migration Mismatch

The initial migration takes the database from zero to its current state; however, the scaffolded database is already up to date, causing the migration to fail. Furthermore, migrations are applied sequentially, future migrations cannot proceed until the initial one succeeds.

Here are two ways to solve this problem.

The first way is to make the migration idempotent, in this case the tables can be created only if they don't yet exist:

protected override void Up(MigrationBuilder migrationBuilder)
{
	migrationBuilder.Sql(
		"""
		CREATE TABLE IF NOT EXISTS AchievementClass (
			Id INTEGER PRIMARY KEY AUTOINCREMENT,
			Name TEXT NOT NULL UNIQUE,
			Unit TEXT NOT NULL
		);
		
		CREATE TABLE IF NOT EXISTS Achievement (
			Id INTEGER PRIMARY KEY AUTOINCREMENT,
			AchievementClassId INTEGER NOT NULL,
			CompletedDate TEXT NOT NULL,
			Quantity INTEGER NOT NULL,
			FOREIGN KEY (AchievementClassId) REFERENCES AchievementClass(Id)
		);
		"""
	);
}

The second way is to manually mark the migration as already applied. This requires running the following script against the database:

INSERT INTO [__EFMigrationsHistory] ([MIGRATIONID], [PRODUCTVERSION])
VALUES ('<full_migration_timestamp_and_name>', '<EF_version>');

In this case the script is:

INSERT INTO [__EFMigrationsHistory] ([MIGRATIONID], [PRODUCTVERSION])
VALUES ('20250114033536_InitialMigration', '9.0.0');

The fourth commit implements the first approach.

Conclude the Transition

At this stage, the two data access technologies - ADO.NET and EF-Core - coexist in the project. It's time to complete the transition.

The fifth commit removes the two old project (AQ.Models and AQ.Data), and the sixth commit fixes AQ.Console by replacing all IRepository usage with DataContext EF-Core calls.

The app now functions identically to before but uses EF-Core instead of ADO.NET as its data provider.

Deploy

Compared to before, the deployment process requires one extra step - the database update:

> export DOTNET_ENVIRONMENT=Production
> dotnet ef database update -s AQ.Console -p AQ.Domain

Then the tool can be packed and installed as before:

> cd AQ.Console
> dotnet pack
> dotnet tool update AQ --global --add-source ./packages

Conclusion

The app remains identical from the perspective of a user, functioning the same with EF-Core as it did previously with ADO.NET. The Achievement Quantifier project is now ready for data model iterations as enabled by EF-Core migrations.