Links

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 dotnet-ef --global

Package Manager Console in Visual Studio

# 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

# mysql
dotnet add package Pomelo.EntityFrameworkCore.MySql

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

InMemory

Program.cs

builder.Services.AddDbContext<JobListDbContext>(options =>
    options.UseInMemoryDatabase("job-list")
        .UseAsyncSeeding(async (context, _, cancellationToken) =>
        {
             if (!await context.Set<Job>().AnyAsync(cancellationToken))
             {
                context.Set<Job>().AddRange(
                    new Job { Id = 1, Description = "Job 1" },
                    new Job { Id = 2, Description = "Job 2" },
                    new Job { Id = 3, Description = "Job 3" }
                );
                await context.SaveChangesAsync(cancellationToken);
            }
         }));

// Needed to seed the in-memory database
using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<JobListDbContext>();
    await context.Database.EnsureCreatedAsync();
}

Postgresql

Use the EFCore.NamingConventions nuget package to change the naming convention.

Program.cs

builder.Services.AddDbContext<JobListDbContext>(options =>
    options
        .UseNpgsql(
            builder.Configuration.GetConnectionString("JobList"),
            o => o.SetPostgresVersion(17, 5))
        .UseSnakeCaseNamingConvention());

Code example with Code First and a Postgresql database

DataAccess/Entities/Job.cs

public class Job
{
    public required int Id { get; set; }
    public required string Description { get; set; }
}

DataAccess/JobListDbContext.cs

public class JobListDbContext(DbContextOptions<JobListDbContext> options) : DbContext(options)
{
    public DbSet<Job> Jobs { get; set; }
}

Program.cs

builder.Services.AddDbContext<JobListDbContext>(options =>
    options
        .UseNpgsql(
            builder.Configuration.GetConnectionString("JobList"),
            o => o.SetPostgresVersion(17, 5))
        .UseSnakeCaseNamingConvention());

Services/JobService.cs

public class JobService(JobListDbContext dbContext) : IJobService
{
    public async Task<IReadOnlyCollection<Job>> GetJobsAsync()
        => await dbContext.Jobs.AsNoTracking().ToListAsync();

    public async Task<Job?> GetJobAsync(int id)
        => await dbContext.Jobs.AsNoTracking().FirstOrDefaultAsync(x => x.Id == id);

    public async Task<Job> AddJobAsync(Job job)
    {
        var jobEntityEntry = await dbContext.Jobs.AddAsync(job);
        var numberOfAdds = await dbContext.SaveChangesAsync();

        return jobEntityEntry.Entity;
    }

    public async Task<bool> UpdateJobAsync(int id, Job job)
    {
        // var numberOfUpdates = await dbContext.Jobs
        //     .Where(x => x.Id == id)
        //     .ExecuteUpdateAsync(setters =>
        //         setters
        //             .SetProperty(j => j.Description, job.Description));

        var jobToUpdate = await dbContext.Jobs.FindAsync(id);
        if (jobToUpdate == null)
            return false;

        jobToUpdate.Description = job.Description;
        var numberOfUpdates = await dbContext.SaveChangesAsync();

        return numberOfUpdates == 1;
    }

    public async Task<bool> DeleteJobsAsync(IReadOnlyCollection<int> ids)
    {
        var numberOfDeletions = await dbContext.Jobs
            .Where(x => ids.Contains(x.Id))
            .ExecuteDeleteAsync();

        // var jobsToDelete = await dbContext.Jobs
        //     .Where(job => ids.Contains(job.Id))
        //     .ToListAsync();
        //
        // if (jobsToDelete.Count != ids.Count)
        //     return false;
        //
        // dbContext.Jobs.RemoveRange(jobsToDelete);
        // var numberOfDeletions = await dbContext.SaveChangesAsync();

        return numberOfDeletions == ids.Count;
    }
}

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)

PostgreSQL

Program.cs

builder.Services.AddDbContext<JobListDbContext>(options =>
    options
        .UseNpgsql(builder.Configuration.GetConnectionString("JobList"),
            o => o
                .SetPostgresVersion(17, 5)));

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));

MySQL .NET Connection String Options

SSL Authentication Error

On Windows, if named pipe authentication is used you may have this issue.
Disable SSL in the connection string to workaround it: sslmode=none

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

Log underlying SQL query

Program.cs

builder.Services.AddDbContext<AppContext>(
    options => options
        .LogTo(Console.WriteLine, LogLevel.Information)  // simple logging
        .UseLoggerFactory(LoggerFactory.Create(loggingBuilder =>
            loggingBuilder
                .AddConfiguration(builder.Configuration.GetSection("Logging"))  // log the configuration
                .AddConsole()  // log into the console if there is one
                .AddDebug()))  // log into VS output Window and Linux journal
        .EnableSensitiveDataLogging(  // include the values of data in exception messages
            builder.Configuration.GetValue<bool>("DbContextLogOptions:EnableSensitiveDataLogging", false))
        .EnableDetailedErrors(        // wrap each call to read a value in a try-catch block
            builder.Configuration.GetValue<bool>("DbContextLogOptions:EnableDetailedErrors", false))
        .UseMySql(connectionString, serverVersion)
);

appsettings.json

"DbContextLogOptions": {
  "EnableSensitiveDataLogging": true,
  "EnableDetailedErrors":  true
},

Code first

Ensure you have installed the nuget package Microsoft.EntityFrameworkCore.Design

Migration

# create a new migration
dotnet ef migrations add <migration name>

# check the generated code
# generate the SQL code and check if

# if everything is ok, then update the database
dotnet ef database update

EntityTypeConfiguration with Fluent API

DataAccess/DbModelConfiguration/ItemConfiguration.cs

internal sealed class ItemConfiguration : IEntityTypeConfiguration<Item>
{
    public void Configure(EntityTypeBuilder<Item> builder)
    {
        builder.ToTable("item");  // by convention same name as the DbSet property

        builder.Property(e => e.Id)
               .HasColumnName("id")  // bc same name as property name
               .HasColumnType("smallint")
               .ValueGeneratedOnAdd() // bc if the column is identified as the PK
               .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);
        builder.HasKey(e => e.Id)  // bc a primary key is automatically generated on property named \w*Id
               .HasName("pk_item");  // bc PK_item

        builder.Property(e => e.Name)
               .HasColumnName("name")
               .HasMaxLength(50)  // varchar(50) CHARACTER SET utf8mb4
               .HasColumnType("nvarchar(50)")
               .HasCharSet("utf8mb4")  // if not specified the database character set and collation are used
               .HasCollation("utf8mb4_unicode_ci");
               .IsRequired();

        builder.Property(e => e.CreationDate)
               .HasColumnName("creation_date")
               .HasColumnType("date");

        builder.Property(e => e.Price)
               .HasColumnName("price")
               .HasColumnType("decimal(5,2)");

        builder.HasIndex(e => e.Name)
               .HasName("ix_item_name")
               .IsUnique();

        // foreign key
        builder.Property(e => e.GroupId)
               .HasColumnName("group_id")
               .HasColumnType("tinyint");

        builder.HasIndex(e => e.GroupId)
               .HasName("ix_items_group_id");

        builder.HasOne(e => e.Group)  // navigation property: Item.Group
               .WithMany(e => e.Items)  // with navigation property: Group.Items
               .WithMany()              // without navigation property: Group.Items
               .HasForeignKey(e => e.GroupId)  // foreign key property: Item.GroupId
               .OnDelete(DeleteBehavior.ClientSetNull)
               .HasConstraintName("fk_items_groups_group_id");

Unsigned types are not supported.

DataAccess/ItemContext.cs

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // specify the configuration
    modelBuilder.ApplyConfiguration(new ItemConfiguration());

    // look for EntityTypeConfiguration in the current assembly
    modelBuilder.ApplyConfigurationsFromAssembly(typeof(ItemContext).Assembly);
}

Data Annotations Attributes

Data/Entities/Item.cs

// définit le nom de la table, par défaut le nom de la classe est utilisées
[Table("Items")]
public class Item
{
    public int Id { get; set; }
    [Column("My_Name")]  // mapping
    public string Name { get; set; }
    // définit le type SQL qui sera utilisé pour le stockage de la donnée, ici un décimal de 7 chiffres dont 2 après la virgule
    [Column(TypeName = "decimal(7, 2)")]
    public decimal Price { get; set; }
    [Column(TypeName = "date")]
    public DateTime Date { get; set; }
}

Nécessite le package nuget System.ComponentModel.DataAnnotations

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 ?

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

# MariaDB
Scaffold-DbContext 'ConnectionStrings:MyConnectionString' Pomelo.EntityFrameworkCore.MySql -OutputDir DataAccess/Entities -ContextDir DataAccess -Context "BourseContext" -Force

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

Avoid using AsNoTracking and Include

AsNoTracking is used to improve performances when you don't need the entities to be tracked for changes. But a better way to improve performances is to use projection so you don't return the whole entity but only the needed fields. This way you don't need AsNoTracking because there is no more entities to track.
Include is used to retrieve other entities linked by a foreign key / navigation property. If you use a projection with a navigation property, you don't have to use Include.

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)

left join

Perform left outer joins

// with navigation property
context.Users.Select(u => new { u.Name, u.Group?.Name });

// method syntax
context.Users
    .GroupJoin(
        context.Groups,
        user => user.GroupId,
        group => group.Id,
        (user, grouping) => new { user, grouping }
    )
    .SelectMany(z => z.grouping.DefaultIfEmpty(), (z, grp) => new { userName = z.user.Name, groupName = grp.Name })
    .Select(x => new { x.userName, x.groupName });

// query syntax
from user in context.Users
join group in context.Groups
on user.GroupId equals group.Id into grouping
from grp in grouping.DefaultIfEmpty()
select new { userName = user.Name, groupName = grp.Name };

SELECT u.Name AS userName, g.Name AS groupName 
FROM User AS u
LEFT JOIN Group AS g
ON u.GroupId = g.Id

Pagination

public async Task<IReadOnlyCollection<Item>> GetItemsAsync(ItemQuery query, CancellationToken cancellationToken)
{
    var items = await this.context.Items
        .Skip((query.PageIndex - 1) * query.PageSize)
        .Take(query.PageSize)
        .AsNoTracking()
        .ToListAsync(cancellationToken);
    return items;
}