« Entity Framework Core 7 » : différence entre les versions

De Banane Atomic
Aller à la navigationAller à la recherche
 
(60 versions intermédiaires par le même utilisateur non affichées)
Ligne 1 : Ligne 1 :
[[Category:.NET Core]]
[[Category:.NET Core]]
= [https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet Entity Framework Core Tools] =
= Links =
* [https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew#executeupdate-and-executedelete-bulk-updates What's New in EF Core 7.0]
* [[Entity_Framework_Plus|Entity Framework Plus]]
* [https://linq2db.github.io/index.html linq2db]
 
= Entity Framework Core Tools =
== [https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet .NET Core CLI] ==
<kode lang='powershell'>
<kode lang='powershell'>
# test if Entity Framework Core Tools has been installed
# test if Entity Framework Core Tools has been installed
Ligne 12 : Ligne 18 :


# update
# update
dotnet tool update --global dotnet-ef
dotnet tool update dotnet-ef --global
# --version 7.0.14
</kode>
</kode>


== [https://learn.microsoft.com/en-us/ef/core/cli/powershell Package Manager Console in Visual Studio] ==
{{info | 1=Visual Studio
{{info | 1=Visual Studio
* View → Other Windows → Package Manager Console
* View → Other Windows → Package Manager Console
* Default Project = the one containing the entity configurations
* Default Project = the one containing the entity configurations
* Startup Project = the one containing the sql server configuration}}
* Startup Project = the one containing the sql server configuration}}
<kode lang='ps'>
# 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
</kode>


= [https://learn.microsoft.com/en-us/ef/core/get-started/overview/first-app?tabs=netcore-cli#install-entity-framework-core Add Entity Framework Core package] =
= [https://learn.microsoft.com/en-us/ef/core/get-started/overview/first-app?tabs=netcore-cli#install-entity-framework-core Add Entity Framework Core package] =
Ligne 24 : Ligne 43 :
# sql server
# sql server
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design


# mysql
# mysql
dotnet add package Pomelo.EntityFrameworkCore.MySql
dotnet add package Pomelo.EntityFrameworkCore.MySql
dotnet add package Microsoft.EntityFrameworkCore.Design
</kode>
</kode>
{{info | {{boxx|Microsoft.EntityFrameworkCore.Design}} allows to scaffold and has to be installed on the executed project.}}


== [https://docs.microsoft.com/en-us/ef/core/providers Data Providers] ==
== [https://docs.microsoft.com/en-us/ef/core/providers Data Providers] ==
Ligne 49 : Ligne 67 :


= Connection string =
= Connection string =
* [[Asp.net_core_5#Configuration|Configuration file and secrets]]
* The connection string could be stored in the [[Asp.net_core_7#Safe_storage_of_app_secrets_in_development_in_ASP.NET_Core|secret store]] in dev environnement
 
* It could also be stored in {{boxx|appsettings.Development.json}} in dev environnement
<filebox fn='appsettings.json'>
<filebox fn='appsettings.json'>
{
{
Ligne 59 : Ligne 77 :
</filebox>
</filebox>


== Dependency injection (ASP.Net Core) ==
== [https://learn.microsoft.com/en-us/ef/core/dbcontext-configuration Dependency injection (ASP.Net Core)] ==
{{info | By default the DbContext is registered as a scoped service: a new DbContext is created on a new thread for each API request.}}
 
=== [https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#2-services-configuration MariaDb / MySql] ===
=== [https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#2-services-configuration MariaDb / MySql] ===
<filebox fn='Program.cs'>
<filebox fn='Program.cs'>
var connectionString = builder.Configuration.GetConnectionString("MariaDb");
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));
var serverVersion = new MariaDbServerVersion(new Version(10, 11, 4));


builder.Services.AddDbContext<MyAppContext>(
builder.Services.AddDbContext<MyAppContext>(
     dbContextOptions => dbContextOptions
     dbContextOptions => dbContextOptions
         .UseMySql(connectionString, serverVersion)
         .UseMySql(connectionString, serverVersion));
        // TODO The following three options help with debugging, but should be changed or removed for production.
</filebox>
        .LogTo(Console.WriteLine, LogLevel.Information)
 
        .EnableSensitiveDataLogging()
* [https://mysqlconnector.net/connection-options/ MySQL .NET Connection String Options]
        .EnableDetailedErrors());
 
==== SSL Authentication Error ====
On Windows, if named pipe authentication is used you may have this issue.<br>
Disable SSL in the connection string to workaround it: {{boxx|1=sslmode=none}}
 
=== SQL Server ===
==== SqlConnectionStringBuilder ====
<filebox fn='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;
</filebox>
</filebox>


Ligne 81 : Ligne 112 :
     {
     {
         optionsBuilder.UseSqlServer("server=localhost;database=test;user=test;password=***"); // hard-coded connection string
         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.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
         optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString); // WPF
</filebox>
</filebox>


== Constructor parameter ==
= [https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/extensions-logging?tabs=v3 Log underlying SQL query] =
<kode lang='cs'>
<filebox fn='Program.cs'>
var builder = new ConfigurationBuilder()
builder.Services.AddDbContext<AppContext>(
    .AddUserSecrets<MyAppContextFactory>();
    options => options
var configuration = builder.Build();
        .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)
);
</filebox>


var connectionStringBuilder = new SqlConnectionStringBuilder("server=localhost;database=test;user=test;");
<filebox fn='appsettings.json'>
connectionStringBuilder.Password = configuration["DbPassword"];
"DbContextLogOptions": {
  "EnableSensitiveDataLogging": true,
  "EnableDetailedErrors":  true
},
</filebox>


var connectionString = connectionStringBuilder.ConnectionString;
= [https://docs.microsoft.com/en-us/ef/core/managing-schemas/scaffolding?tabs=dotnet-core-cli Database first: scaffold database to model entities] =
var connectionString = "server=localhost;database=test;user=test;password=****";
<kode lang='bash'>
# 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
</kode>


var contextOptions = new DbContextOptionsBuilder<MyAppContext>()
== Visual Studio ==
    .UseMySql(
* Ensure [[Entity_Framework_Core_7#Package_Manager_Console_in_Visual_Studio|Entity Framework Core Tools]] have been installed
        connectionString,
* View → Other Windows → Package Manager Console
        mySqlOptions => mySqlOptions.ServerVersion("10.5.8-mariadb"))
* Default Project = the one containing the entity configurations ?
    .Options;
* Startup Project = the one containing the sql server configuration ?


using var context = new MyAppContext(contextOptions);
<kode lang='ps'>
Scaffold-DbContext 'Data Source=MY-PC;Initial Catalog=MyDb' Microsoft.EntityFrameworkCore.SqlServer -OutputDir Entities
</kode>
</kode>


<filebox fn='DataAccess/MyAppContext.cs'>
{| class="wikitable wtp"
public class MyAppContext : DbContext
! Error
! Titre colonne 2
|-
| The certificate chain was issued by an authority that is not trusted || Add {{boxx|1=Encrypt=False}} to the connection string
|-
| Login failed for user '' || Add {{boxx|1=Integrated Security=True}} to the connection string
|}
 
== [https://learn.microsoft.com/en-us/ef/core/managing-schemas/scaffolding/templates?tabs=dotnet-core-cli Custom Reverse Engineering Templates] ==
 
= [https://docs.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/infrastructure-persistence-layer-design#the-repository-pattern Repository Pattern] =
* 1 {{boxx|Repository}} per resource: {{boxx|ItemRepository}}
* 1 {{boxx|DbContext}} for the whole application
<filebox fn='Repositories/ItemRepository.cs' collapsed>
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)
    {
        var nbAffectedRows = await context.Items.Where(x => x.Id == id).ExecuteDeleteAsync(cancellationToken);
        return nbAffectedRows == 1;
    }
</filebox>
 
= [https://blog.dcube.fr/index.php/2019/09/05/generic-repository-unit-of-work-et-entity-framework Generic repository] =
<filebox fn='RepositoryBase.cs' collapsed>
public abstract class RepositoryBase<TEntity, TCreateUpdateQuery> where TEntity : class, new()
{
{
     public MyAppContext(DbContextOptions<ApplicationDbContext> options)
    private readonly AppContext context;
         : base(options)
 
     { }
    private DbSet<TEntity> dbEntities { get; }
 
    protected RepositoryBase(BourseContext context)
    {
        this.context = context ?? throw new ArgumentNullException(nameof(context));
        dbEntities = this.context.Set<TEntity>();
    }
 
    protected async Task<IReadOnlyCollection<TResult>> GetAsync<TResult>(
        Expression<Func<TEntity, TResult>> selector,
        Func<IQueryable<TEntity>, IIncludableQueryable<TEntity, object>>? include = null,
        Expression<Func<TEntity, bool>>? predicate = null,
        Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>>? orderBy = null,
        Func<IQueryable<TEntity>, IQueryable<TEntity>>? skipTake = null,
        bool disableTracking = true,
        CancellationToken cancellationToken = default)
        where TResult : class
    {
        IQueryable<TEntity> query = dbEntities;
 
        if (include is not null)
        {
            query = include(query);
        }
 
        if (predicate is not null)
        {
            query = query.Where(predicate);
        }
 
        if (orderBy is not null)
        {
            query = orderBy(query);
        }
 
        if (skipTake is not null)
        {
            query = skipTake(query);
        }
 
        if (disableTracking)
        {
            query = query.AsNoTracking();
        }
 
        return await query.Select(selector).ToListAsync(cancellationToken);
    }
 
    public async Task<TEntity> CreateAsync(
        TEntity entity, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(entity);
 
        await context.AddAsync(entity, cancellationToken);
        await context.SaveChangesAsync(cancellationToken);
        return entity;
    }
 
     public async Task<bool> UpdateAsync(
        Expression<Func<TEntity, bool>> filterByIdExpression,
        TCreateUpdateQuery query,
        CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(query);
 
        var entityToUpdate = await dbEntities
            .FirstOrDefaultAsync(filterByIdExpression, cancellationToken);
 
        if (entityToUpdate is null)
         {
            return false;
        }
 
        UpdateEntityFromCreateUpdateQuery(query, entityToUpdate);
        await context.SaveChangesAsync(cancellationToken);
        return true;
    }
 
    public async Task<bool> DeleteAsync(
        Expression<Func<TEntity, bool>> filterByIdExpression,
        CancellationToken cancellationToken)
     {
        var nbAffectedRows = await dbEntities
            .Where(filterByIdExpression)
            .ExecuteDeleteAsync(cancellationToken);
 
        return nbAffectedRows > 0;
    }
}
</filebox>
</filebox>
= [https://www.devtrends.co.uk/blog/avoid-asnotracking-and-include-when-querying-using-entity-framework-in-asp.net Avoid using AsNoTracking and Include] =
{{boxx|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 {{boxx|AsNoTracking}} because there is no more entities to track.<br>
{{boxx|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 {{boxx|Include}}.
= Query generation =
== string contains vs like ==
<kode lang='csharp'>
context.Items.Where(x => x.Name.Contains("item1"));
</kode>
<kode lang='tsql'>
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE (@__query_Name_0 LIKE N'') OR (CHARINDEX(@__query_Name_0, [i].[Name]) > 0)
</kode>
<kode lang='csharp'>
context.Items.Where(x => EF.Functions.Like(x.Name, "%item1%"));
</kode>
<kode lang='tsql'>
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE [i].[Name] LIKE @__Format_1
</kode>
== any ==
<kode lang='csharp'>
context.Items.Any(x => x.Id == 1);
</kode>
<kode lang='mysql'>
SELECT EXISTS (
    SELECT 1
    FROM `item` AS `i`
    WHERE `i`.`id` = @__id_0)
</kode>
== [https://learn.microsoft.com/en-us/ef/core/querying/complex-query-operators#left-join left join] ==
* [https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins Perform left outer joins]
<kode lang='csharp'>
// 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 };
</kode>
<kode lang='tsql'>
SELECT u.Name AS userName, g.Name AS groupName
FROM User AS u
LEFT JOIN Group AS g
ON u.GroupId = g.Id
</kode>
= [https://learn.microsoft.com/en-us/aspnet/core/data/ef-mvc/sort-filter-page?view=aspnetcore-7.0#add-paging-to-index-method Pagination] =
<kode lang='cs'>
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;
}
</kode>
= [https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew What's New in EF Core 7.0] =
== [https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew#basic-executedelete-examples Bulk delete] ==
<kode lang='cs'>
await context.Tags.Where(t => t.Text.Contains(".NET")).ExecuteDeleteAsync();
</kode>
<kode lang='tsql'>
DELETE FROM [t]
FROM [Tags] AS [t]
WHERE [t].[Text] LIKE N'%.NET%'
</kode>
== [https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew#basic-executeupdate-examples Bulk update] ==
<kode lang='cs'>
await context.Posts
    .Where(p => p.PublishedOn.Year < 2022)
    .ExecuteUpdateAsync(s => s
        .SetProperty(b => b.Title, b => b.Title + " (" + b.PublishedOn.Year + ")")
        .SetProperty(b => b.Content, b => b.Content + " ( This content was published in " + b.PublishedOn.Year + ")"));
</kode>
<kode lang='tsql'>
UPDATE [p]
SET [p].[Content] = (([p].[Content] + N' ( This content was published in ') + COALESCE(CAST(DATEPART(year, [p].[PublishedOn]) AS nvarchar(max)), N'')) + N')',
    [p].[Title] = (([p].[Title] + N' (') + COALESCE(CAST(DATEPART(year, [p].[PublishedOn]) AS nvarchar(max)), N'')) + N')'
FROM [Posts] AS [p]
WHERE DATEPART(year, [p].[PublishedOn]) < 2022
</kode>

Dernière version du 24 mars 2024 à 14:34

Links

Entity Framework Core Tools

.NET Core CLI

Powershell.svg
# 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
# --version 7.0.14

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
Ps.svg
# 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

Bash.svg
# sql server
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

# mysql
dotnet add package Pomelo.EntityFrameworkCore.MySql
Microsoft.EntityFrameworkCore.Design allows to scaffold and has to be installed on the executed project.

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

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
},

Database first: scaffold database to model entities

Bash.svg
# 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 ?
Ps.svg
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

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)
    {
        var nbAffectedRows = await context.Items.Where(x => x.Id == id).ExecuteDeleteAsync(cancellationToken);
        return nbAffectedRows == 1;
    }

Generic repository

RepositoryBase.cs
public abstract class RepositoryBase<TEntity, TCreateUpdateQuery> where TEntity : class, new()
{
    private readonly AppContext context;

    private DbSet<TEntity> dbEntities { get; }

    protected RepositoryBase(BourseContext context)
    {
        this.context = context ?? throw new ArgumentNullException(nameof(context));
        dbEntities = this.context.Set<TEntity>();
    }

    protected async Task<IReadOnlyCollection<TResult>> GetAsync<TResult>(
        Expression<Func<TEntity, TResult>> selector,
        Func<IQueryable<TEntity>, IIncludableQueryable<TEntity, object>>? include = null,
        Expression<Func<TEntity, bool>>? predicate = null,
        Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>>? orderBy = null,
        Func<IQueryable<TEntity>, IQueryable<TEntity>>? skipTake = null,
        bool disableTracking = true,
        CancellationToken cancellationToken = default)
        where TResult : class
    {
        IQueryable<TEntity> query = dbEntities;

        if (include is not null)
        {
            query = include(query);
        }

        if (predicate is not null)
        {
            query = query.Where(predicate);
        }

        if (orderBy is not null)
        {
            query = orderBy(query);
        }

        if (skipTake is not null)
        {
            query = skipTake(query);
        }

        if (disableTracking)
        {
            query = query.AsNoTracking();
        }

        return await query.Select(selector).ToListAsync(cancellationToken);
    }

    public async Task<TEntity> CreateAsync(
        TEntity entity, CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(entity);

        await context.AddAsync(entity, cancellationToken);
        await context.SaveChangesAsync(cancellationToken);
        return entity;
    }

    public async Task<bool> UpdateAsync(
        Expression<Func<TEntity, bool>> filterByIdExpression,
        TCreateUpdateQuery query,
        CancellationToken cancellationToken)
    {
        ArgumentNullException.ThrowIfNull(query);

        var entityToUpdate = await dbEntities
            .FirstOrDefaultAsync(filterByIdExpression, cancellationToken);

        if (entityToUpdate is null)
        {
            return false;
        }

        UpdateEntityFromCreateUpdateQuery(query, entityToUpdate);
        await context.SaveChangesAsync(cancellationToken);
        return true;
    }

    public async Task<bool> DeleteAsync(
        Expression<Func<TEntity, bool>> filterByIdExpression,
        CancellationToken cancellationToken)
    {
        var nbAffectedRows = await dbEntities
            .Where(filterByIdExpression)
            .ExecuteDeleteAsync(cancellationToken);

        return nbAffectedRows > 0;
    }
}

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

Csharp.svg
context.Items.Where(x => x.Name.Contains("item1"));
Tsql.svg
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE (@__query_Name_0 LIKE N'') OR (CHARINDEX(@__query_Name_0, [i].[Name]) > 0)
Csharp.svg
context.Items.Where(x => EF.Functions.Like(x.Name, "%item1%"));
Tsql.svg
SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE [i].[Name] LIKE @__Format_1

any

Csharp.svg
context.Items.Any(x => x.Id == 1);
Mysql.svg
SELECT EXISTS (
    SELECT 1
    FROM `item` AS `i`
    WHERE `i`.`id` = @__id_0)

left join

Csharp.svg
// 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 };
Tsql.svg
SELECT u.Name AS userName, g.Name AS groupName 
FROM User AS u
LEFT JOIN Group AS g
ON u.GroupId = g.Id

Pagination

Cs.svg
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;
}

What's New in EF Core 7.0

Bulk delete

Cs.svg
await context.Tags.Where(t => t.Text.Contains(".NET")).ExecuteDeleteAsync();
Tsql.svg
DELETE FROM [t]
FROM [Tags] AS [t]
WHERE [t].[Text] LIKE N'%.NET%'

Bulk update

Cs.svg
await context.Posts
    .Where(p => p.PublishedOn.Year < 2022)
    .ExecuteUpdateAsync(s => s
        .SetProperty(b => b.Title, b => b.Title + " (" + b.PublishedOn.Year + ")")
        .SetProperty(b => b.Content, b => b.Content + " ( This content was published in " + b.PublishedOn.Year + ")"));
Tsql.svg
UPDATE [p]
SET [p].[Content] = (([p].[Content] + N' ( This content was published in ') + COALESCE(CAST(DATEPART(year, [p].[PublishedOn]) AS nvarchar(max)), N'')) + N')',
    [p].[Title] = (([p].[Title] + N' (') + COALESCE(CAST(DATEPART(year, [p].[PublishedOn]) AS nvarchar(max)), N'')) + N')'
FROM [Posts] AS [p]
WHERE DATEPART(year, [p].[PublishedOn]) < 2022