Entity Framework Core 7

Entity Framework Core Tools

.NET Core CLI

# 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

Package Manager Console in Visual Studio

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

# test if Entity Framework Core Tools has been installed Get-Help about_EntityFrameworkCore # install Install-Package Microsoft.EntityFrameworkCore.Tools # update Update-Package Microsoft.EntityFrameworkCore.Tools

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)

By default the DbContext is registered as a scoped service: a new DbContext is created on a new thread for each API request.

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

• Ensure Entity Framework Core Tools have been installed
• View → Other Windows → Package Manager Console
• Default Project = the one containing the entity configurations ?
• Startup Project = the one containing the sql server configuration ?

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

Query generation

string contains vs like

context.Items.Where(x => x.Name.Contains("item1"));

SELECT [i].[Id], [i].[Name] FROM [Items] AS [i] WHERE (@__query_Name_0 LIKE N'') OR (CHARINDEX(@__query_Name_0, [i].[Name]) > 0)

context.Items.Where(x => EF.Functions.Like(x.Name, "%item1%"));

SELECT [i].[Id], [i].[Name] FROM [Items] AS [i] WHERE [i].[Name] LIKE @__Format_1