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

Repository Pattern

• 1 Repository per resource: ItemRepository
• 1 DbContext for the whole application

Repositories/ItemRepository.cs

public class ItemRepository : IItemRepository { private readonly MyAppContext context; public ItemRepository(MyAppContext context) { this.context = context; } public async Task<IReadOnlyCollection<Item>> GetAllItemsAsync(CancellationToken cancellationToken) { var items = await context.Items .Include(x => x.NavigationProperty) .AsNoTracking() .ToListAsync(cancellationToken); return items; } public async Task<Item?> GetItemByIdAsync(int id, CancellationToken cancellationToken) { var item = await context.Items .AsNoTracking() .SingleOrDefaultAsync(x => x.Id == id, cancellationToken); return item; } public async Task<IReadOnlyCollection<Item>> GetItemsByNameAsync(string name, CancellationToken cancellationToken) { var items = await context.Items .Where(x => EF.Functions.Like(x.Name, $"%{name}%")) .AsNoTracking() .ToListAsync(cancellationToken); return items; } public async Task<Item> CreateItemAsync(Item item, CancellationToken cancellationToken) { await context.AddAsync(item, cancellationToken); await context.SaveChangesAsync(cancellationToken); return item; // it now contains its id } public async Task<bool> UpdateItemAsync(int id, Item item, CancellationToken cancellationToken) { ArgumentNullException.ThrowIfNull(item); var itemToUpdate = await context.Items.SingleOrDefaultAsync(x => x.Id == id, cancellationToken); if (itemToUpdate is null) { return false; // send not found from controller then } itemToUpdate.Name = item.Name; await context.SaveChangesAsync(); return true; } public async Task<bool> DeleteItemAsync(int id, CancellationToken cancellationToken) { if (!await context.Items.AnyAsync(x => x.Id == id, cancellationToken)) // do not fetch the item, just test if it exists { return false; // send not found from controller then } context.Remove(new Item { Id = id }); // do not fetch the item await context.SaveChangesAsync(cancellationToken); return true; }

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

any

context.Items.Any(x => x.Id == 1);

SELECT EXISTS ( SELECT 1 FROM `item` AS `i` WHERE `i`.`id` = @__id_0)