Entity Framework Core Tools

# test if Entity Framework Core Tools has been installed
dotnet ef
# be sure to run the previous command in the folder of the project where EF has been added

# dotnet ef must be installed as a global or local tool
dotnet tool install --global dotnet-ef
# installed in ~/.dotnet/tools
# Add ~/.dotnet/tools to PATH

# update
dotnet tool update --global dotnet-ef

Add Entity Framework Core package

# sql server
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

# mysql
dotnet add package Pomelo.EntityFrameworkCore.MySql
dotnet add package Microsoft.EntityFrameworkCore.Design

Data Providers

Provider	Package NuGet	Connection String
SQL Server	Microsoft.EntityFrameworkCore.SqlServer	Server=(localdb)\\MSSQLLocalDB;Database=MyDb;Integrated Security=True;MultipleActiveResultSets=True; Server=localhost;Database=MyDb;User=sa;Password=pwd;
MySQL / MariaDB	Pomelo.EntityFrameworkCore.MySql	server=localhost;database=MyDb;user=root;password=pwd
PostgreSQL	Npgsql.EntityFrameworkCore.PostgreSQL	Host=localhost;Database=MyDb;Username=root;Password=pwd
InMemory	Microsoft.EntityFrameworkCore.InMemory	databaseName: "test_database"
Sqlite	Microsoft.EntityFrameworkCore.Sqlite	Data Source=/tmp/file.db

Connection string

The connection string could be stored in the secret store in dev environnement
It could also be stored in appsettings.Development.json in dev environnement

appsettings.json

{
  "ConnectionStrings": {
    "MariaDb": "server=localhost;database=test;user=test;password=***"
  }
}

Dependency injection (ASP.Net Core)

MariaDb / MySql

Program.cs

var connectionString = builder.Configuration.GetConnectionString("MariaDb");  // get the connection string from the appsettings.json or the secret store
var serverVersion = new MariaDbServerVersion(new Version(10, 11, 4));

builder.Services.AddDbContext<MyAppContext>(
    dbContextOptions => dbContextOptions
        .UseMySql(connectionString, serverVersion)
        // TODO The following three options help with debugging, but should be changed or removed for production.
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging()
        .EnableDetailedErrors());

SQL Server

SqlConnectionStringBuilder

Program.cs

var connectionStringWithoutPassword = builder.Configuration.GetConnectionString("SqlServer");
var connectionStringBuilder = new SqlConnectionStringBuilder(connectionStringWithoutPassword);  // Package: System.Data.SqlClient
connectionStringBuilder.Password = builder.Configuration["SqlServerPassword"];
var connectionString = connectionStringBuilder.ConnectionString;

DbContext OnConfiguring

DataAccess/MyAppContext.cs

public class MyAppContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("server=localhost;database=test;user=test;password=***"); // hard-coded connection string
        optionsBuilder.UseMySql("name=ConnectionStrings:MariaDb", ServerVersion.Parse("10.11.4-mariadb")); // get it from the appsettings.json or the secret storage
        optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString); // WPF

Database first: scaffold database to model entities

# generate entity classes and context class
dotnet ef dbcontext scaffold
  "Server=localhost;Database=MyDb;User=sa;Password=***;"  # an harcoded connection string
  "Name=ConnectionStrings:SqlServer"                      # get the connection string from the appsettings.json or the secret storage
  Microsoft.EntityFrameworkCore.SqlServer  # the database provider
  --output-dir DataAccess/Entities  # output folder for entities
  --context-dir DataAccess          # output folder for DbContext
  --context "MyDbContext"           # default context name: DbNameContext
  --force                           # overwrite all the class files
  --table table1 --table table2     # scaffold only table1 and table2

Visual Studio

View → Other Windows → Package Manager Console

# verify the installation
Get-Help about_EntityFrameworkCore

# install if not yet installed
Install-Package Microsoft.EntityFrameworkCore.Tools

# scaffold
Scaffold-DbContext 'Data Source=MY-PC;Initial Catalog=MyDb' Microsoft.EntityFrameworkCore.SqlServer -OutputDir Entities

Error	Titre colonne 2
The certificate chain was issued by an authority that is not trusted	Add Encrypt=False to the connection string
Login failed for user	Add Integrated Security=True to the connection string

Custom Reverse Engineering Templates