Version du 26 juillet 2023 à 12:53

Liens

Description

Réécriture complète d'EF
Plus de nécessite d'utilisé des BdD relationnelles uniquement

Ajouter les packages au projet

cd MyProject

# 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 --version 3.1.12
# Pomelo.EntityFrameworkCore.MySql 3.2.4 -> Microsoft.EntityFrameworkCore.Relational (>= 3.1.8 && < 5.0.0)

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;
Sqlite	Microsoft.EntityFrameworkCore.Sqlite	Data Source=/tmp/file.db
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"

Entity Framework Core Tools

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

# tester si Entity Framework Core Tools est bien installé et fonctionnel
dotnet ef
# bien se mettre dans le dossier du projet
# faire un dotnet restore au cas où la restoration n'aurait pas fonctionnée

# 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

# màj de dotnet-ef
dotnet tool update --global dotnet-ef

# ajouter le paquet nuget suivant
dotnet add package Microsoft.EntityFrameworkCore.Design

Create DbContext

Creating and configuring a model

DataAccess/MyAppContext.cs

public class MyAppContext : DbContext
{
    public DbSet<Item> Items => Set<Item>();
}

Configure entity types

Conventions
Target	Convention
Primary key	property named Id or <type_name>Id will be configured as the primary key of an entity with the name PK_<type_name>.
Foreign key	a relationship will be created when there is a navigation property discovered on a type.
required	property whose .NET type can contain null will be configured as optional, whereas properties whose .NET type cannot contain null will be configured as required.
Table name	same name as the DbSet property that exposes the entity or same as class name.

Links:

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

Vue / view

Model/BlogPostsCount.cs

public class BlogPostsCount
{
    public int BlogId { get; set; }
    public int PostCount { get; set; }
}

Model/Configuration/BlogPostsCountConfiguration

public class BlogPostsCountConfiguration : IEntityTypeConfiguration<BlogPostsCount>
{
    public void Configure(EntityTypeBuilder<BlogPostsCount> builder)
    {
        builder.ToView("View_BlogPostCounts");
        builder.HasKey(x => x.BlogId);
    }
}

Model/BloggingContext.cs

public class BloggingContext : DbContext
{
    public DbSet<BlogPostsCount> BlogPostCounts { get; set; }

# create a new migration
dotnet ef migrations add View_BlogPostCounts

Migration/YYYYMMDDHHmmss_View_BlogPostCounts.cs

public partial class View_BlogPostCounts : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            @"CREATE VIEW View_BlogPostCounts AS
            SELECT b.BlogId, Count(p.PostId) as PostCount
            FROM Blogs b
            JOIN Posts p on p.BlogId = b.BlogId
            GROUP BY b.BlogId;");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP VIEW View_BlogPostCounts;");
    }
}

# update the database
dotnet ef database update

Controllers/BlogController.cs

[HttpGet]
public async Task<IEnumerable<BlogDto>> Get()
{
    var query = from b in this.dbContext.Blogs
                join c in this.dbContext.BlogPostCounts
                on b.BlogId equals c.BlogId
                select new BlogDto
                {
                    BlogId = b.BlogId,
                    Url = b.Url,
                    PostsCount = c.PostCount,
                    Posts = b.Posts.Select(y => new PostDto
                    {
                        PostId = y.PostId,
                        Title = y.Title,
                        Content = y.Content
                    })
                };
                    
    var dto = await query.ToListAsync();

QueryType

Data/MyAppContext.cs

public class MyAppContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<MyQueryType>().ToView("MyView");

Connection string

Configuration file and secrets

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

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)
    { }

Dependency injection (ASP.Net Core)

MariaDb / MySql

Program.cs

// MariaDb
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());

Old

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

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

Migrations

# création du fichier de migration
dotnet ef migrations add InitialCreate
# un fichier YYYYMMDDHHMMSSS_IntialCreate.cs est créé dans le dossier Migration

# supprimer la dernière migration
dotnet ef migrations remove

# lister les migration
dotnet ef migrations list

# il faut créer une migration avant de pouvoir passer à la génération

# génération d'un script SQL pour voir ce que va faire la migration
dotnet ef migrations script <FROM> <TO>
# From: 0 par défaut
# To: dernière migration par défaut

# génération et exécution du script SQL
dotnet ef database update

# revert the last already applied migration
dotnet ef database update <previous-migration-to-keep>
dotnet ef migrations remove

Add data update during migration

After having added a migration, you can edit the Migrations/YYYYMMDDHHmmss_MigrationName.cs file:

Migrations/YYYYMMDDHHmmss_MigrationName.cs

public partial class MigrationName : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        /* ... */

        // update data
        migrationBuilder.UpdateData(
            table: "MyTable",
            keyColumn: "Id",
            keyValue: 1,
            column: "Column1",
            value: 1);

        // execute SQL command
        migrationBuilder.Sql("UPDATE `MyTable` SET `Column1` = `Column2`;");
    }

Apply migrations at runtime

ASP.Net web API

Startup.cs

public static void Configure(IApplicationBuilder app, IWebHostEnvironment env, MyDbContext context)
{
    // ensures your existing migrations are applied to the database, create it if needed
    context.Database.Migrate();

    // create the db if it doesn't exist
    context.Database.EnsureCreated();
}

Data Seeding

Permet de remplir la bdd avec un jeu initial de données.

Data/MyAppContext.cs

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Item>().HasData(
        new Item { Id = 1, Name = "Item1" },
        new Item { Id = 2, Name = "Item2" }
    );
}

set the ids even if they belong to an auto-incremented column
Use the ids to link objects.

OLD

Data/MyAppSeeder.cs

public class MyAppSeeder
{
    private readonly MyAppContext _context;

    public MyAppSeeder(MyAppContext context)
    {
        _context = context;
    }

    public void Seed()
    {
        _context.Database.EnsureCreated();

        if (!_context.Items.Any())
        {
            _context.Items.Add(new Item() { Name = "Item 1" });  // ne pas définir l'Id
            _context.Items.Add(new Item() { Name = "Item 2" });
            _context.SaveChanges();
        }

Sartup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddTransient<MyAppSeeder>();
}

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
    using (var scope = app.ApplicationServices.CreateScope())
    {
        var seeder = scope.ServiceProvider.GetService<MyAppSeeder>();
        seeder.Seed();
    }
}

Log underlying SQL query

Program.cs

public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder => { builder.AddConsole(); });

builder.Services.AddDbContext<AppContext>(
    options => options
        .LogTo(Console.WriteLine, LogLevel.Information)  // simple logging
        .UseLoggerFactory(MyLoggerFactory)               // LoggerFactory
        .EnableSensitiveDataLogging()  // include the values of data in exception messages
        .EnableDetailedErrors()        // wrap each call to read a value in a try-catch block
        .UseMySql(connectionString, serverVersion));

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<AppContext>(options =>
        options.UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()))
               .UseSqlServer(Configuration.GetConnectionString("SqlServer"))
    );

Program.cs

protected override void OnConfiguring(DbContextOptionsBuilder options)
{
    // log in addition in the Console
    options.LogTo(Console.WriteLine);

Using Microsoft.Extensions.Logging in EF Core

Database first: scaffold database to model entities

# generate entity classes and context class
dotnet ef dbcontext scaffold "Server=localhost;Database=MyDb;User=sa;Password=pwd;" Microsoft.EntityFrameworkCore.SqlServer
    --output-dir 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

View → Other Windows → Package Manager Console

# verify the installation
Get-Help about_EntityFrameworkCore

# install if not yet installed
Install-Package Microsoft.EntityFrameworkCore.Tools

# scaffold
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 par classe: ItemsRepository
1 DbContext pour toute l'application

Data/ItemsRepository.cs

public class ItemsRepository : IItemsRepository
{
    private readonly MyAppContext context;

    public ItemsRepository(MyAppContext context)
    {
        this.context = context;
    }

    public async Task<IReadOnlyList<Item>> GetAllAsync()
    {
        return await context.Set<Item>()
                            .Include(x => x.SubClass)
                            .ToListAsync();
    }

    public async Task<Item> GetByIdAsync(int id)
    {
        var item = await context.FindAsync<Item>(id);
        // use Include with Find
        this.context.Entry(item).Reference(x => x.User).Load();
        return item;
    }

    public async Task<Item> CreateAsync(Item item)
    {
        var createdItemEntry = await context.AddAsync(item);
        await context.SaveChangesAsync();
        return createdItemEntry.Entity;
    }

    public async Task UpdateAsync(Item itemToUpdate, Item item)
    {
        itemToUpdate.Name = item.Name;
        await context.SaveChangesAsync();
    }

    public async Task DeleteAsync(Item item)
    {
        context.Remove(item);
        await context.SaveChangesAsync();
    }
}

La création d'une interface permettra d'utiliser d'autres sources de données pour faire des tests.

Data\IItemsRepository.cs

public interface IItemsRepository
{
    Task<IReadOnlyList<Item>> GetAllAsync();
    Task<Item> GetByIdAsync(int id);
    Task<Item> CreateAsync(Item item);
    Task UpdateAsync(Item itemToUpdate, Item item);
    Task DeleteAsync(Item item);
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddScoped<IItemsRepository, ItemsRepository>();

Use Include with Find

Include for navigation properties

await this.dbContext.Blogs
                    .Include(x => x.Posts) // load Blog.Posts
                    // .ThenInclude(x => x.AnotherProperty)
                    .ToListAsync();

SELECT [b].[BlogId], [b].[Url], [p].[PostId], [p].[BlogId], [p].[Content], [p].[Title]
FROM [Blogs] AS [b]
LEFT JOIN [Posts] AS [p] ON [b].[BlogId] = [p].[BlogId]
ORDER BY [b].[BlogId], [p].[PostId]

Datetime

this.context.Item
    .Where(x => EF.Functions.DateDiffDay(x.Date2, x.Date1) > 0)
    .ToListAsync();

N+1 selects problem

EF fait ses requêtes en lazy loading, ce qui veut dire que les requêtes SQL ne sont exécutées que lorsqu'on a besoin des données.

// 1 requête pour récupérer tous les contacts
IEnumerable<Contact> allContacts = context.Contacts;
foreach (var contact in allContacts)
{
    // pour récupérer l'age de chaque contact il faut faire une nouvelle requête pour chaque contact
    // ce qui donne de mauvaises performances
    if (contact.age > 30) { /* ... */ }
}

// inclure Age lors de la récupération des contacts
// ainsi tous se fait en une seule requête
IEnumerable<Contact> allContacts = context.Contacts.Include(c => c.Age);

Executing Raw SQL Queries

using System.Data.SqlClient;  // pour SqlParameter

var books = context.Books.FromSql("SELECT Id, Title FROM Books").ToList();

// passage de paramètre avec l'interpolation de string
var book = context.Books.FromSql($"SELECT Id, Title FROM Books WHERE Title = {title}").FirstOrDefault();

// passage de paramètre avec DbParameter
var p1 = new SqlParameter("@Id", id);
var book = db.Books.FromSql("SELECT * FROM Books WHERE Id = @Id", p1).FirstOrDefault();

// opérations INSERT, UPDATE, DELETE
var commandText = "INSERT Authors (AuthorName) VALUES (@AuthorName)";
var name = new SqlParameter("@AuthorName", "Jean-Christophe Grangé");
context.Database.ExecuteSqlCommand(commandText, name);

// procédure stockée
var authorId = new SqlParameter("@AuthorId", 1);
var books = context.Books.FromSql("EXEC GetBooksByAuthor @AuthorId" , authorId).ToList();

// procédure stockée avec plusieurs paramètres
var parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter(name, SqlDbType.Int) { Value = (object)value?.ToInt32(CultureInfo.InvariantCulture) ?? DBNull.Value });
var parameterNamesList = string.Join(", ", parameters.Select(p => p.ParameterName));
var storedProcedureResultRows = await context.Database.SqlQuery<StoredProcedureResultRow>(
    $"{StoredProcedureName} {parameterNamesList}",
    parameters.Cast<object>().ToArray()).ToListAsync(cancellationToken);

Pagination

private const int PageSize = 10;

public async Task<IReadOnlyList<Item>> GetAsync(int pageIndex)
{
    var items = await this.context.Item
                                  .AsNoTracking()
                                  .Skip((pageIndex - 1) * PageSize)
                                  .Take(PageSize)
                                  .ToListAsync();
    return items;
}

Ignore accent in string comparaison (MySql - Pomelo)

Use the like operator.

// doesn't return Nicolàs
person.Where(x => x.Name.Contains("Nicolas", StringComparison.CurrentCultureIgnoreCase));

// return Nicolàs
person.Where(x => EF.Functions.Like(x.Name, "%Nicolas%"));

Inheritance

By default, EF maps the inheritance using the table-per-hierarchy (TPH) pattern.
TPH uses a single table to store the data for all types in the hierarchy, and a discriminator column is used to identify which type each row represents.

Id	Discriminator	OwnerId	BorrowerId
1	SimpleItem	Paul	null
2	BorrowedItem	Jean	Luc

ItemBase.cs

public abstract class ItemBase
{
    public int Id { get; set; }
    public ItemType Type { get; set; }
    public int OwnerId { get; set; }
    public Person Owner { get; set; }
}

SimpleItem.cs

public class SimpleItem : ItemBase
{ }

BorrowedItem.cs

public class BorrowedItem : ItemBase
{
    public int BorrowerId { get; set; }
    public Person Borrower { get; set; }
}

ItemContext.cs

public class ItemContext : DbContext
{
    public DbSet<ItemBase> Item { get; set; }
    public DbSet<SimpleItem> SimpleItem { get; set; }
    public DbSet<BorrowedItem> BorrowedItem { get; set; }

Examples

One to one relationship

One Address per User.

public class Address
{
    public int Id { get; set; }
    [Required, StringLength(40)]
    public string City { get; set; }
    public User User { get; set; }
}

public class User
{
    public int Id { get; set; }
    [Required, StringLength(40)]
    public string Name { get; set; }
    public Address Address { get; set; }
    public int AddressId { get; set; }
}

public class MyAppContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // allow to have both User.Address and Address.User
        modelBuilder.Entity<Address>()
                    .HasOne(x => x.User)
                    .WithOne(user => user.Address)
                    .HasForeignKey<User>(x => x.AddressId);

CREATE TABLE [Addresses] (
    [Id] int NOT NULL IDENTITY,
    [City] nvarchar(40) NOT NULL,
    CONSTRAINT [PK_Addresses] PRIMARY KEY ([Id])
);

CREATE TABLE [Users] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(40) NOT NULL,
    [AddressId] int NOT NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Users_Addresses_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [Addresses] ([Id]) ON DELETE CASCADE
);

CREATE UNIQUE INDEX [IX_Users_AddressId] ON [Users] ([AddressId]);

One to many relationship

Many Task per User.

public class User
{
    public int Id { get; set; }
    [Required, StringLength(40)]
    public string Name { get; set; }
    public Task[] Tasks { get; set; }  // can be remove without changing the generated sql if Task.User exists
}

public class Task
{
    public int Id { get; set; }
    [Required, StringLength(40)]
    public string Name { get; set; }
    [Required]
    public User User { get; set; }  // can be remove without changing the generated sql if User.Tasks exists
}

public class MyAppContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
                    .HasMany(x => x.Tasks)
                    .WithOne(task => task.User);

CREATE TABLE [Users] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(40) NOT NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY ([Id])
);

CREATE TABLE [Task] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(40) NOT NULL,
    [UserId] int NOT NULL,
    CONSTRAINT [PK_Task] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Task_Users_UserId] FOREIGN KEY ([UserId]) REFERENCES [Users] ([Id]) ON DELETE CASCADE
);

CREATE INDEX [IX_Task_UserId] ON [Task] ([UserId]);

Many to many relationship

Many User per Group and many Group per User.

Configuring Many To Many Relationships in Entity Framework Core

Short way

public class Group
{
    public int Id { get; set; }
    [Required, StringLength(40)]
    public string Name { get; set; }
    public IReadOnlyCollection<User> Users { get; set; }
}

public class User
{
    public int Id { get; set; }
    [Required, StringLength(40)]
    public string Name { get; set; }
    public IReadOnlyCollection<User> Groups { get; set; }
}

public class MyAppContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // seed
        var users = new [] { new User { Id = 1, Name = "User1" } };
        modelBuilder.Entity<User>().HasData(users);

        var groups = new [] { new Group { Id = 1, Name = "Group1" } };
        modelBuilder.Entity<Group>().HasData(groups);

        var userGroups = new [] { new { UserId = 1, GroupId = 1 } };
        modelBuilder.Entity<User>()
                    .HasMany(x => x.Groups)
                    .WithMany(x => x.Users)
                    .UsingEntity(x => x.HasData(userGroups));
    }
}

// query
var users = await context.Users
                         .Include(x => x.Groups)
                         .ToListAsync();

Detailed way

public class Group
{
    public int Id { get; set; }
    [Required, StringLength(40)]
    public string Name { get; set; }
    public UserGroup[] UserGroups { get; set; }
}

public class User
{
    public int Id { get; set; }
    [Required, StringLength(40)]
    public string Name { get; set; }
    [Required]
    public Address Address { get; set; }
    public Task[] Tasks { get; set; }
    public UserGroup[] UserGroups { get; set; }
}

public class UserGroup
{
    public int UserId { get; set; }
    public User User { get; set; }
    public int GroupId { get; set; }
    public Group Group { get; set; }
}

public class MyAppContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserGroup>()
                    .HasKey(x => new { x.UserId, x.GroupId });
        modelBuilder.Entity<UserGroup>()
                    .HasOne(x => x.User)
                    .WithMany(user => user.UserGroups)
                    .HasForeignKey(x => x.UserId);
        modelBuilder.Entity<UserGroup>()
                    .HasOne(x => x.Group)
                    .WithMany(group => group.UserGroups)
                    .HasForeignKey(x => x.GroupId);

        // seed
        var users = new [] { new User { Id = 1, Name = "User1" } };
        modelBuilder.Entity<User>().HasData(users);

        var groups = new [] { new Group { Id = 1, Name = "Group1" } };
        modelBuilder.Entity<Group>().HasData(groups);

        var userGroups = new [] { new UserGroup { UserId = 1, GroupId = 1 } };
        modelBuilder.Entity<UserGroup>().HasData(userGroups);
    }
}

// query
var users = await context.Users
                         .Include(x => x.UserGroups)
                         .ThenInclude(x => x.Group)
                         .ToListAsync();

Unit tests

Use the InMemory database provider to test the repositories and the services.

MyServiceTest.cs

private DbContextOptions<MyContext> options;

public MyServiceTest()
{
    options = new DbContextOptionsBuilder<MyContext>()
        .UseInMemoryDatabase(databaseName: "test")
        .Options;

    // Insert seed data into the database using one instance of the context
    using (var context = new MyContext(options))
    {
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        var entity = new MyEntity { Id = 1 };
        context.MyEntity.Add(expense);
        context.SaveChanges();
    }
}

[Fact]
public async Task MyMethodAsync_Input_Output()
{
    // Use a clean instance of the context to run the test
    using (var context = new MyContext(options))
    {
        var repository = new MyRepository(context);

        var service = new MyService(repository);

        var result = await service.MyMethod();

        Assert.Collection(
            result,
            item => Assert.Equal(1, item.Id));
    }
}

Erreurs

Cannot authenticate using Kerberos

Erreur sur Linux durant l'exécution de dotnet ef database update. Impossible de se connecter au serveur SQL.
Passer Trusted_Connection à False et ajouter le user et password à la connection string corrige le problème.

appsettings.json

"ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyDb;Trusted_Connection=False;MultipleActiveResultSets=true;User=sa;Password=xxx"
  },

Specified key was too long; max key length is 767 bytes

Les index InnoDB (MySQL) ont une taille limitée. Il faut donc limité la taille des index à la création de la bdd.

Data/MyAppContext.cs

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder); 

    // Shorten key length for Identity
    builder.Entity<IdentityRole>(entity => {
        entity.Property(m => m.NormalizedName).HasMaxLength(127);
    });
    builder.Entity<IdentityUser>(entity => {
        entity.Property(m => m.NormalizedUserName).HasMaxLength(127);
    });
}

The host localhost does not support SSL connections

Ajouter SslMode=none à la ConnectionString MySQL.

OLD - No executable found matching command "dotnet-ef"

Installer les packages Nuget:

Microsoft.EntityFrameworkCore.Tools
Microsoft.EntityFrameworkCore.Tools.DotNet

MyProject.csproj

<Project Sdk="Microsoft.NET.Sdk">
  <!-- Ajouter le groupe suivant -->
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.0" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" /> 
  </ItemGroup>

dotnet restore

Examples of generated queries

left join

context.Users.Select(u => new { u.Group, u });

from user in context.Users
join grp in context.Groups
on user.GroupId equals grp.Id into grouping
from grp in grouping.DefaultIfEmpty()
select new { grp, user };

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

inner join

from user in context.Users
join grp in context.Groups
on user.GroupId equals grp.Id
select new { grp, user };

SELECT [g].[Id], [g].[Name], [u].[Id], [u].[GroupId], [u].[Name]
FROM [User] AS[u]
INNER JOIN [Group] AS [g] ON [u].[GroupId] = [g].[Id]

cross join

from grp in context.Groups
from user in context.Users
select new { grp, user };

SELECT [g].[Id], [g].[Name], [u].[Id], [u].[GroupId], [u].[Name]
FROM [Group] AS [g]
CROSS JOIN [User] AS [u]

find

var item = await context.Items.FindAsync(id);

SELECT TOP(1) [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE [i].[Id] = @__p_0

where

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

SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE [i].[Name] = @__query_Name_0

string contains

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

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

like

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

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

int contains

context.Items.Where(x => ids.Contains(x.Id));

SELECT [i].[Id], [i].[Name]
FROM [Items] AS [i]
WHERE [i].[Id] IN (1, 2)

many to many

context.Items.Include(x => x.Categories).ToListAsync();

SELECT [i].[Id], [i].[Name], [t].[CategoriesId], [t].[ItemsId], [t].[Id], [t].[Name]
FROM [Items] AS [i]
LEFT JOIN (
    SELECT [c].[CategoriesId], [c].[ItemsId], [c0].[Id], [c0].[Name]
    FROM [CategoryItem] AS [c]
    INNER JOIN [Categories] AS [c0] ON [c].[CategoriesId] = [c0].[Id]
) AS [t] ON [i].[Id] = [t].[ItemsId]
ORDER BY [i].[Id], [t].[CategoriesId], [t].[ItemsId], [t].[Id]

update without fetch

// create a new item
var itemToUpdate = new Item { Id = 1, Name = "new name" }
// attach it then mark the property as modified
context.Attach(itemToUpdate).Property(x => x.Name).IsModified = true;

await context.SaveChangesAsync();

UPDATE [Items]
SET [Name] = @p0
WHERE [Id] = @p1;

Entity Framework Plus

Batch update

context.Items.Where(x => ids.Contains(x.Id))
             .UpdateAsync(x => new Item { Name = "new name" },
                          x =>
                          {
                              x.Executing = command => Console.WriteLine(command.CommandText);
                          });

UPDATE A 
SET A.[Name] = @zzz_BatchUpdate_0
FROM [Items] AS A
INNER JOIN (
  SELECT [i].[Id], [i].[Name]
  FROM [Items] AS [i]
  WHERE [i].[Id] IN (1, 2)
) AS B
ON A.[Id] = B.[Id]

Updates multiples rows
- in a single database roundtrip
- without loading entities in the context

Limitations:

Do not support Complex Type
Do not support TPC
Do not support TPH
Do not support TPT

If you need to use one of this feature, you need to use the paying library Entity Framework Extensions

Batch delete

Deletes multiples rows
- in a single database roundtrip
- without loading entities in the context

Query Future

Allow to reduce database roundtrip by batching multiple queries in the same sql command.

var futureItems = dbContext.Items.Future();
var futureCategories = dbContext.Categories.Future();

// TRIGGER all pending queries (futureItems & futureCategories)
var items = await futureItems.ToListAsync();

// for immediate methods (count, first, single, contains, any), use a query Deferred
var futureItemCount = await context.Items.DeferredCount().FutureValue();
var count = futureItemCount.Value;

Query cache

var items = await context.Items.FromCacheAsync();
// The first call perform a database round trip
// The following calls will take the value from the memory instead

// for immediate methods (count, first, single, contains, any), use a query Deferred
var count = await context.Items.DeferredCount().FromCacheAsync();

Audit

Allow to easily track changes, exclude/include entity or property and auto save audit entries in the database.

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

Version du 26 juillet 2023 à 12:53

Liens

Description

Configure entity types

EntityTypeConfiguration with Fluent API

Connection string

DbContext OnConfiguring

Constructor parameter

Old

Add data update during migration

OLD

Visual Studio

Datetime

N+1 selects problem

Examples

Short way

Detailed way

Erreurs

The host localhost does not support SSL connections

left join

inner join

cross join

find

where

string contains

like

int contains

many to many

update without fetch

Batch delete

Menu de navigation

Rechercher

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