EFCore.Scaffolding 2.0.0

EFCore.Scaffolding is a configurable alternative to the dotnet ef dbcontext scaffold command for generating a DbContext and its entities.

Getting started

Add the EFCore.Scaffolding NuGet package to your project using the NuGet Package Manager or run the following command:

dotnet add package EFCore.Scaffolding

Reverse engineering a database, also known as scaffolding, is usually performed with the dotnet ef dbcontext scaffold command. This command has a few shortcomings that this project aims to address.

EF Core providers support

The following EF Core providers are supported out of the box. Pull requests are welcome to support additional providers.

• EntityFrameworkCore.Jet (on Windows only)
• Microsoft.EntityFrameworkCore.Sqlite
• Microsoft.EntityFrameworkCore.SqlServer
• Npgsql.EntityFrameworkCore.PostgreSQL
• Oracle.EntityFrameworkCore
• Pomelo.EntityFrameworkCore.MySql

Scaffolding requires to use a typed connection string builder so that the scaffolder knows which provider to use.

using EFCore.Scaffolding;
using Npgsql;

var connectionStringBuilder = new NpgsqlConnectionStringBuilder
{
    Host = "localhost",
    Database = "postgres",
    Username = "postgres",
    Password = "postgres",
};
Scaffolder.Run(new ScaffolderSettings(connectionStringBuilder));

Filtering

Both tables/entities and columns/properties can be filtered programmatically with the FilterTable and FilterColumn predicates of the ScaffolderSettings object. Sometimes it's easier to exclude a few tables rather than explicitly list dozen of tables.

Renaming

Databases in the real world are not perfect and often table and/or column names are less than ideal. The RenameEntity and RenameProperty functions are here to tweak the names as appropriate.

Sorting properties

By default, properties are scaffolded in the same order as columns appear in the tables. If you prefer to keep them sorted alphabetically, it's possible by setting SortColumnsComparer = new ColumnNameComparer(). It's even possible to write your own comparer if you need to sort them in any other way.

And more…

The ScaffolderSettings class has a few more properties that will help you generate a perfect DbContext and its entities. All the public API is documented with XML comments.

Sample code

Here's how to scaffold a real PostgreSQL database using the EFCore.Scaffolding package. This demonstrates how to filter out the fax columns and rename Fulltext with proper FullText casing. This also demonstrates how the files can be saved relative to the current C# file.

using System;
using System.IO;
using System.Runtime.CompilerServices;
using EFCore.Scaffolding;
using Npgsql;

var connectionStringBuilder = new NpgsqlConnectionStringBuilder
{
    Host = "ep-square-wildflower-00220644.us-east-2.aws.neon.tech",
    Database = "chinook",
    Username = "AzureDiamond",
    Password = "correct horse battery staple",
};

var settings = new ScaffolderSettings(connectionStringBuilder)
{
    OutputDirectory = GetOutputDirectory(),
    FilterColumn = column => column.Name != "fax",
    RenameProperty = (propertyName, _) => propertyName.Replace("Fulltext", "FullText"),
};

Scaffolder.Run(settings);

Console.WriteLine($"✅ The database was scaffolded in {new Uri(settings.OutputDirectory.FullName)}");

return;

static DirectoryInfo GetOutputDirectory([CallerFilePath] string path = "")
    => new(Path.GetFullPath(Path.Combine(Path.GetDirectoryName(path)!, "..", "ChinookDatabase")));

You can actually try to run this code, I have hosted the Chinook sample database on the free Neon tier.

Database creation

Here's how to create the Chinook database on Neon. These instructions are adapted from the Neon documentation but using the latest version of the Chinook database that uses snake_case table and column names.

1. Checkout the chinook-database repository

git clone https://github.com/lerocha/chinook-database/

2. Run a docker container to load the database (performing so many inserts directly on Neon is impossibly slow)

pg_container_id=`docker run -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_HOST_AUTH_METHOD=trust -d --rm --mount type=bind,source=${PWD}/ChinookDatabase/DataSources/Chinook_PostgreSql_AutoIncrementPKs.sql,destination=/docker-entrypoint-initdb.d/chinook.sql,readonly postgres:alpine`

3. Dump the database into chinook_dump.sql

docker exec ${pg_container_id} pg_dump --username postgres --no-owner --file chinook_dump.sql

ℹ️ This generates an SQL script with COPY statements which are much faster than a series of INSERT statements.

4. Optionally extract this file out of the Docker container to have a look at it

docker cp ${pg_container_id}:/chinook_dump.sql .

5. Execute the generated script after creating a database named chinook in the Neon console and replacing the connection string with the one provided in the Neon dashboard

docker exec ${pg_container_id} psql -d "postgres://[user]:[password]@[neon_hostname]/chinook" -f chinook_dump.sql

6. Dispose the Docker container after checking that the database has been successfully imported

docker stop ${pg_container_id}

Database permissions

-- Create the database (can also be done interactively in the Neon console)
CREATE DATABASE chinook;

-- Create the user and grant SELECT
CREATE USER "AzureDiamond" LOGIN PASSWORD 'correct horse battery staple';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "AzureDiamond";

Additional commands to check privileges and drop the user.

-- Verify the privileges
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'AzureDiamond';

-- Drop user
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "AzureDiamond";
-- REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM "AzureDiamond";
-- REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM "AzureDiamond";
-- REVOKE ALL PRIVILEGES ON SCHEMA public FROM "AzureDiamond";
-- REVOKE ALL PRIVILEGES ON DATABASE chinook FROM "AzureDiamond";
DROP USER "AzureDiamond";