Entity Framework Core 7

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

Visual Studio View → Other Windows → Package Manager Console Default Project = the one containing the entity configurations Startup Project = the one containing the sql server configuration

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

• Configuration file and secrets

Dependency injection (ASP.Net Core)

MariaDb / MySql

Program.cs

var connectionString = builder.Configuration.GetConnectionString("MariaDb"); 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

Startup.cs

public void ConfigureServices(IServiceCollection services) { // SQL Server var builder = new SqlConnectionStringBuilder(Configuration.GetConnectionString("SqlServer")); // read the appsettings.json builder.Password = Configuration["DbPassword"]; // use the Secret Manager in dev, and an environment variable in prod services.AddDbContext<MyAppContext>(options => options.UseSqlServer(builder.ConnectionString) ); // MySql services.AddDbContext<MyAppContext>(options => options.UseMySql( Configuration.GetConnectionString("MySql"), mySqlOptions => mySqlOptions.ServerVersion( new ServerVersion(new Version(10, 5, 8), ServerType.MariaDb))); // mySqlOptions => mySqlOptions.ServerVersion("10.5.8-mariadb"); ); // In Memory services.AddDbContext<MyAppContext>(options => options.UseInMemoryDatabase("WebApplicationCoreMemoryDb"); );

DataAccess/MyAppContext.cs

public class MyAppContext : DbContext { // permet de passer des options à la construction du DbContext public MyAppContext(DbContextOptions<MyAppContext> options) : base(options) { }

MyController.cs

public class MyController { private readonly MyAppContext context; public MyController(MyAppContext context) { this.context = context; }

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")); // connection string in the appsettings.json optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString); // WPF

appsettings.json

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

Constructor parameter

var builder = new ConfigurationBuilder() .AddUserSecrets<MyAppContextFactory>(); var configuration = builder.Build(); var connectionStringBuilder = new SqlConnectionStringBuilder("server=localhost;database=test;user=test;"); connectionStringBuilder.Password = configuration["DbPassword"]; var connectionString = connectionStringBuilder.ConnectionString; var connectionString = "server=localhost;database=test;user=test;password=****"; var contextOptions = new DbContextOptionsBuilder<MyAppContext>() .UseMySql( connectionString, mySqlOptions => mySqlOptions.ServerVersion("10.5.8-mariadb")) .Options; using var context = new MyAppContext(contextOptions);

DataAccess/MyAppContext.cs

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

Design time DbContext factory

• Explain how to create a Context which doesn't have a parameterless ctor.
• Separate the EF code needed for generating database tables at design-time from EF code used by your application at runtime.

DataAccess/MyAppContextFactory

public class MyAppContextFactory : IDesignTimeDbContextFactory<MyAppContext> { private string connectionString; public MyAppContextFactory() { var builder = new ConfigurationBuilder() .SetBasePath(Directory.GetCurrentDirectory()) .AddJsonFile("appsettings.json") .AddUserSecrets<MyAppContextFactory>(); // read stored secrets var configuration = builder.Build(); connectionString = configuration.GetConnectionString("SqlServer"); } public MyAppContext CreateDbContext(string[] args) { var builder = new DbContextOptionsBuilder<MyAppContext>(); builder.UseSqlServer(connectionString); var dbContext = new MyAppContext(builder.Options); return dbContext; } }

DataAccess/MyAppContext.cs

public class MyAppContext : DbContext { // permet de passer des options à la construction du DbContext public MyAppContext(DbContextOptions<MyAppContext> options) : base(options) { }

var dbContextFactory = new MyAppContextFactory(); using var dbContext = dbContextFactory.CreateDbContext(args);