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

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
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;
Sqlite	Microsoft.EntityFrameworkCore.Sqlite	Data Source=file.db
MySQL / MariaDB	Pomelo.EntityFrameworkCore.MySql	Server=localhost;Database=MyDb;User=root;Password=pwd

Entity Framework Core Tools

# 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

EF Core 3.x

# 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

ASP.NET Core 2.1+

Plus besoin depuis dotnet core 2.1 (lien)

Si le fichier de projet ne contient pas Microsoft.EntityFrameworkCore.Tools.DotNet, l'ajouter. (version)
À la sauvegarde NuGet va restaurer les paquets nécessaires.

MyProject.csproj

<ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.3" />
</ItemGroup>

Create DbContext and configure entity types

Creating and configuring a model

DataAccess/MyAppContext.cs

public class MyAppContext : DbContext
{
    public DbSet<Item> Items { get; set; }
}

Configure entity types

By convention:

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.
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>.
table name: name of the configuration file

Links:

EntityTypeConfiguration with Fluent API

DataAccess/DbModelConfiguration/ItemConfiguration.cs

internal sealed class ItemConfiguration : IEntityTypeConfiguration<Item>
{
    public void Configure(EntityTypeBuilder<Item> builder)
    {
        builder.Property(e => e.Id)
               .HasName("id")
               .HasColumnType("smallint");

        builder.Property(e => e.Name)
               .IsRequired()
               .HasMaxLength(50);

        builder.Property(e => e.CreationDate)
               .HasColumnType("DATE");

        builder.Property(e => e.Price)
               .HasColumnType("DECIMAL(5,2)");

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

        // foreign key items.group = groups.id
        builder.Property(e => e.GroupId)
               .HasColumnName("group")
               .HasColumnType("tinyint");

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

        builder.HasOne(e => e.Group)
               .WithMany()
               .HasForeignKey(e => e.GroupId)
               .OnDelete(DeleteBehavior.ClientSetNull)
               .HasConstraintName("fk_items_groups_id");

Unsigned types are not supported.

DataAccess/ItemContext.cs

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    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 et QueryType

Data/MyAppContext.cs

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

Configuration

appsettings.json

{
  "ConnectionStrings": {
    "SqlServerConnectionString1": "Server=(localdb)\\MSSQLLocalDB;Database=MyDb;Integrated Security=True;MultipleActiveResultSets=True",
    "SqlServerConnectionString2": "Server=localhost;Database=MyDb;User=sa;Password=pwd;",
    "MySqlConnectionString": "Server=localhost;Database=MyDb;User=root;Password=pwd;",
    "SqliteConnectionString": "Data Source=MyDb.db"
  }
}

Safe storage of app secrets in development in ASP.NET Core

WebHost

DataAccess/MyAppContext.cs

public class MyAppContext : DbContext
{
    // permet de passer des options à la construction du DbContext
    public MyAppContext(DbContextOptions<MyAppContext> options) : base(options)
    {}
}

Startup.cs

using Microsoft.EntityFrameworkCore;

public void ConfigureServices(IServiceCollection services)
{
    // SQL Server
    services.AddDbContext<AppContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("SqlServerConnectionString2"))
    );

    // In Memory
    services.AddDbContext<AppContext>(options =>
        options.UseInMemoryDatabase("WebApplicationCoreMemoryDb");
    );

    // MySql
    services.AddDbContext<AppDbContext>(options =>
        options.UseMySql(Configuration.GetConnectionString("MySqlConnectionString"), mySqlOptions => mySqlOptions.ServerVersion(
            new ServerVersion(new Version(10, 4, 11),
            ServerType.MariaDb)))
    );

OnConfiguring

DataAccess/MyAppContext.cs

public class MyAppContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString);
    }
}

IDesignTimeDbContextFactory

Explain how to create a Context which doesn't have a parameterless ctor.

DataAccess/MyAppContextFactory

public class MyAppContextFactory : IDesignTimeDbContextFactory<MyAppContext>
{
    private string connectionString;

    public MyAppContextFactory()
    {
        var builder = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json");
        var configuration = builder.Build();

        connectionString = configuration.GetConnectionString("SqlServerConnectionString");
    }

    public MyAppContext CreateDbContext(string[] args)
    {
        var builder = new DbContextOptionsBuilder<MyAppContext>();
        builder.UseSqlServer(connectionString);
        var context = new MyAppContext(builder.Options);
        return context;
    }
}

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

Apply migrations at runtime

ASP.Net web API

Startup.cs

public static void Configure(IApplicationBuilder app, IWebHostEnvironment env, MyDbContext context)
{
    if (env.IsDevelopment())
    {
        context.Database.EnsureCreated();
        // or
        context.Database.Migrate();
    }
}

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() { Name = "Item1" },
        new Item() { Name = "Item2" });
}

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

Database first: scaffold database to model entities

# generate entity classes and context class
dotnet ef dbcontext scaffold "Server=localhost;Database=Item;User=sa;Password=pwd;" Microsoft.EntityFrameworkCore.SqlServer -o Model

Utilisation dans le controller

MyController.cs

private readonly MyAppContext _context;

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

public IActionResult Index()
{
    var model = _context.Items.ToList();
    return View(model);
}

[HttpGet]
[ProducesResponseType(200, Type = typeof(IEnumerable<Item>))]
public IActionResult Get()
{
    return Ok(_context.Items);
}

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

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

Examples

1 to 1 relation

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

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

CREATE TABLE [Address] (
    [Id] int NOT NULL IDENTITY,
    [City] nvarchar(40) NOT NULL,
    CONSTRAINT [PK_Address] 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_Address_AddressId] FOREIGN KEY ([AddressId]) REFERENCES [Address] ([Id]) ON DELETE CASCADE
);

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

1 to many relation

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

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

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

CREATE TABLE [Users] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(40) NOT NULL,
    [GroupId] int NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Users_Groups_GroupId] FOREIGN KEY ([GroupId]) REFERENCES [Groups] ([Id]) ON DELETE NO ACTION
);

CREATE INDEX [IX_Users_GroupId] ON [Users] ([GroupId]);

many to many relation

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

Entity Framework Core

Liens

Description

EF Core 3.x

ASP.NET Core 2.1+

Configure entity types

EntityTypeConfiguration with Fluent API

Configuration

WebHost

OnConfiguring

IDesignTimeDbContextFactory

OLD

Database first: scaffold database to model entities

Utilisation dans le controller

N+1 selects problem

Examples

1 to 1 relation

1 to many relation

many to many relation

Erreurs

The host localhost does not support SSL connections

Menu de navigation

Rechercher