Les tables temporelles – Partie 2 : Utilisation avec Entity Framework Core

Nous avons vu dans la première partie de l’article ce que sont les tables temporelles, quels sont leurs intérêts et comment les manipuler via des commandes SQL. Dans cette seconde partie nous allons voir comment les utiliser via l’ORM Entity Framework Core (EF Core).

Quel est le support d’Entity Framework Core pour les tables temporelles ?

Entity Framework est un ORM très complet de Microsoft et très souvent la solution utilisée dans les projets .NET. Etant donné que SQL Server supporte depuis sa version 2016 les tables temporelles et notre projet étant en full stack Microsoft on va naturellement penser que EF Core supporte cette fonctionnalité et commencer à regarder dans sa documentation comment l’effectuer.

Une recherche de « temporal » dans cette documentation ne nous donne qu’un seul résultat qui n’est autre qu’un lien vers une librairie tierce nommée EFCore.TemporalSupport. Cette librairie est très simple, elle permet seulement de convertir une table traditionnelle en table temporelle via les migrations EF (encore faut-il utiliser cette fonctionnalité), mais donc pas de nous aider dans le requêtage de ces tables. Dans cet article nous ne couvrirons donc pas les migrations EF pour les tables temporelles, il y a suffisamment de packages NuGet qui le font très bien.

On peut en déduire qu’il n’y a actuellement aucun support des tables temporelles dans EF Core. Vous pouvez suivre l’évolution des tickets liés à cette implémentation sur le GitHub EF Core (#2229, #4693). La bonne nouvelle est que l’on peut toutefois continuer d »utiliser EF Core pour toutes nos requêtes temporelles.

Si notre application n’utilisait aucun ORM et que toutes les requêtes seraient écrites « en dur » dans le code alors la question ne se poserait pas, il suffirait de reprendre exactement les mêmes requêtes que la première partie de cet article.

Que permet EF Core ?

Fort heureusement EF Core est un outil très complet et nous permet d’exécuter des requêtes directement en pur SQL via la fonction « FromSql() ». Une fois habitué à un ORM il n’est pas forcément judicieux de le mixer avec du SQL pur, surtout avec Entity Framework qui impose que le résultat de la requête soit d’un type entité (ex : « DbSet<T> ») et que la requête ne puisse pas retourner de données liées comme par exemple avec des jointures (il faut utiliser « Include() »).

Depuis EF Core 3.0, les deux surcharges de « FromSql » ont été remplacées par « FromSqlRaw » et « FromSqlInterpolated ». « FromSqlInterpolated » permet de convertir simplement les paramètres en DbParameter et ne sont donc plus vulnérables aux injections SQL.

Création du DbContext

Nous allons créer un nouveau projet C# de type Console en utilisant .NET Core 3.0 :

dotnet new console

Une fois le projet initialisé, et comme nous avons déjà créé le modèle de base de données dans la partie 1 de l’article, nous allons récupérer notre modèle via la méthode « Database First ».

Depuis EF Core 3.0, l’outil ligne de commande « dotnet ef » n’est plus inclus dans le SDK .NET Core. Pour exécuter des migrations EF Core ou commande de scaffolding il faudra au préalable installer la package « dotnet-ef » de façon globale ou locale. Par exemple pour installer la dernière version de façon globale il faudra exécuter la commande :

dotnet tool install --global dotnet-ef --version 3.0.0

Cette nouvelle version est compatible avec les anciennes versions d’EF Core, en revanche les anciennes versions ne fonctionneront plus avec EF Core 3.0+

Dans notre nouveau projet C#, ajouter dans Visual Studio les packages :

• EntityFrameworkCore.SqlServer
• EntityFrameworkCore.Design

Vous pouvez également le réaliser en ligne de commande :

dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 3.0.0
dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.0.0

Voici ce que vous devez avoir dans Visual Studio :

La version 3.0.1 est disponible mais un bug empêche dotnet ef 3.0.1 d’être installé. Nous utiliserons donc la version 3.0.0.

Maintenant que les packages nécessaires sont installés, nous allons créer notre modèle à partir de notre base de données existante. Pour ce faire, ouvrir la ligne de commande et se placer sur le répertoire racine de votre projet. Exécuter ensuite la commande :

dotnet ef dbcontext scaffold "data source=.;initial catalog=DemoTemporelle;Integrated Security=SSPI" Microsoft.EntityFrameworkCore.SqlServer --context DemoTemporelleContext --output-dir Database\Model --context-dir Database

Descriptif des paramètres :

• data source : Chaîne de connexion de notre base de données (ici base locale avec authentification Windows)
• EntityFrameworkCore.SqlServer : Provider EF Core pour SQL Server
• –context : Nom du DbContext à utiliser au sein de notre projet
• –outpur-dir : Répertoire relatif du modèle
• –context-dir : Répertoire relatif du contexte

Une fois exécuté vous obtiendrez la structure suivante :

DbContext :

public partial class DemoTemporelleContext : DbContext
{
    public DemoTemporelleContext()
    {
    }

    public DemoTemporelleContext(DbContextOptions<DemoTemporelleContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Employe> Employe { get; set; }
    public virtual DbSet<Entreprise> Entreprise { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer("data source=.;initial catalog=DemoTemporelle;Integrated Security=SSPI");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employe>(entity =>
        {
            entity.Property(e => e.Nom)
                .IsRequired()
                .HasMaxLength(80);

            entity.Property(e => e.Prenom)
                .IsRequired()
                .HasMaxLength(80);

            entity.HasOne(d => d.Entreprise)
                .WithMany(p => p.Employe)
                .HasForeignKey(d => d.EntrepriseId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Employe_Entreprise");
        });

        modelBuilder.Entity<Entreprise>(entity =>
        {
            entity.Property(e => e.Adresse).HasMaxLength(80);
            entity.Property(e => e.Nom).HasMaxLength(80);

        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

Nous retrouvons bien nos deux DbSet ainsi que le mapping réalisé.

public partial class Employe
{
    public int Id { get; set; }
    public string Prenom { get; set; }
    public string Nom { get; set; }
    public int EntrepriseId { get; set; }

    public virtual Entreprise Entreprise { get; set; }
}

public partial class Entreprise
{
    public Entreprise()
    {
        Employe = new HashSet<Employe>();
    }

    public int Id { get; set; }
    public string Nom { get; set; }
    public string Adresse { get; set; }
    public DateTime SysStartTime { get; set; }
    public DateTime SysEndTime { get; set; }

    public virtual ICollection<Employe> Employe { get; set; }
}

On remarque que l’entité Employe ne contient aucune notion de temporalité, contrairement à Entreprise qui possède les propriétés « SysStartTime » et « SysEndTime ». C’est tout à fait normal puisque dans la première partie de l’article nous avions défini ces champs système à « HIDDEN » pour la table Employe. Ces champs ne sont pas modifiables par l’utilisateur (EF Core retournera une exception pour les requêtes autres que sélection), donc si vous n’avez pas spécifier « HIDDEN » pour toutes vos tables il va falloir spécifier à EF Core que ces colonnes sont de type « Computed » (générées par la base de données) ou sont ignorées. Vous pouvez également les retirer du modèle. Voici comment ignorer les colonnes :

modelBuilder.Entity<Entreprise>(entity =>
{
    //...
    entity.Ignore(x => x.SysStartTime);
    entity.Ignore(x => x.SysEndTime);
});

Il peut être utile de conserver la colonne SysStartTime en computed si vous souhaitez connaître la date de dernière modification de la ligne.

Requêtage sans temporalité

Créons une fonction « Compter » qui va compter le nombre de lignes actuelles dans nos deux tables.

class Program
{
    static void Main(string[] args)
    {
        using (var db = new DemoTemporelleContext())
        {
            Compter(db);
            Console.ReadLine();
        }
    }

    static void Compter(DemoTemporelleContext db)
    {
        Console.WriteLine($"Employés : {db.Employe.Count()}");
        Console.WriteLine($"Entreprises : {db.Entreprise.Count()}");
    }
}

Résultats :

• Employés : 2
• Entreprises : 1

C’est le même résultat que l’on obtient dans SQL Server :

On remarque donc que nos requêtes usuelles sur les tables temporelles ne changent pas à condition que l’on n’utilise pas la temporalité.

Testons l’ajout :

static void Main(string[] args)
{
    using (var db = new DemoTemporelleContext())
    {
        Compter(db);
        AjoutEmploye(db);
        Compter(db);
        Console.ReadLine();
    }
}

static void Compter(DemoTemporelleContext db)
{
    Console.WriteLine($"Employés : {db.Employe.Count()}");
    Console.WriteLine($"Entreprises : {db.Entreprise.Count()}");
}

static void AjoutEmploye(DemoTemporelleContext db)
{
    var employe = new Employe
    {
        Prenom = "Jacques",
        Nom = "Dupont",
        EntrepriseId = 1
    };

    db.Employe.Add(employe);
    db.SaveChanges();
}

Résultat :

• Employés : 2
• Entreprises : 1
• Employés : 3
• Entreprises : 1

Que ce soit pour la lecture ou modification de données, nous pouvons continuer d’utiliser nos requêtes habituelles sans le moindre risque, le tout étant transparent.

Requêtage simple avec temporalité

Reprenons la requête de la partie 1 listant l’ensemble des employés ayant déjà appartenu à l’entreprise et leurs modifications, donc y compris les supprimés :

static void Main(string[] args)
{
    using (var db = new DemoTemporelleContext())
    {
        ListerHistoriqueEmploye(db);
        Console.ReadLine();
    }
}

static void ListerHistoriqueEmploye(DemoTemporelleContext db)
{
    var sql = @"
SELECT *
FROM Employe
FOR SYSTEM_TIME ALL";

    var employes = db.Employe
        .FromSqlRaw(sql)
        .OrderBy(i => i.Id)
        .ToList();

    employes.ForEach(i =>
        Console.WriteLine($"{i.Id}- {i.Prenom} {i.Nom}")
    );
}

Résultat :

Nous avons dû écrire notre requête SQL à la main dans notre fichier C# et l’utiliser avec EF Core via la fonction « FromSqlRaw ». Dans le résultat on peut voir tous les salariés et leurs modifications avec les données telles qu’elles ont été dans le temps.

A noter que malgré le fait que la requête soit à la main nous pouvons utiliser toutes les fonctionnalités d’EF Core, comme « OrderBy() » ci-avant ou encore « Include() » comme ici :

static void ListerHistoriqueEmploye(DemoTemporelleContext db)
{
    var sql = @"
SELECT *
FROM Employe
FOR SYSTEM_TIME ALL";

    var employes = db.Employe
        .FromSqlRaw(sql)
        .Include(i => i.Entreprise)
        .OrderBy(i => i.Id)
        .ToList();

    employes.ForEach(i =>
        Console.WriteLine($"{i.Id}- {i.Prenom} {i.Nom}: {i.Entreprise.Nom}")
    );
}

Résultat :

Le problème de l’Include() précédent est que l’on récupère une donnée historisée des employés et on affiche les valeurs actuelles de leur entreprise. Pour récupérer les valeurs de l’entreprise au moment où de la création/modification de l’employé, il faudra les valeurs SysStartTime et SysEndTime et donc inclure ces propriétés à notre model et les marquer en tant que Computed. Comme démontré dans la partie 1 il n’est pas possible d’inclure dynamiquement un SYSTEM_TIME provenant d’une autre table, mais il faudra passer par un « foreach » sur la liste des employés et à l’intérieur refaire une requête pour chaque ligne afin de récupérer la valeur. Pour 11 résultats on aura exécuté 12 requêtes ce qui peut très vite poser problème.

Si on souhaite récupérer les employés et leur entreprise à une date précise, voici comment faire :

class Program
{
    static void Main(string[] args)
    {
        ListerEmployeDate(new DateTime(2019, 10, 23, 15, 40, 0));
        ListerEmployeDate(DateTime.UtcNow);
        Console.ReadLine();
    }

    static void ListerEmployeDate(DateTime date)
    {
        Console.WriteLine($"Données au {date.ToString("g")}");
        using (var db = new DemoTemporelleContext())
        {
            FormattableString sqlEmployes = $@"
SELECT *
FROM Employe
FOR SYSTEM_TIME AS OF {date}";

            FormattableString sqlEntreprise = $@"
SELECT *
FROM Entreprise
FOR SYSTEM_TIME AS OF {date}";

            var employes = db.Employe.FromSqlInterpolated(sqlEmployes);
            var entreprises = db.Entreprise.FromSqlInterpolated(sqlEntreprise);

            var results = from employe in employes
                            join entreprise in entreprises on employe.EntrepriseId equals entreprise.Id
                            select new
                            {
                                Employe = employe,
                                Entreprise = entreprise
                            };

            foreach (var result in results)
            {
                Console.WriteLine($"{result.Employe.Id}- {result.Employe.Prenom} {result.Employe.Nom}: {result.Employe.Entreprise.Nom} ({result.Employe.Entreprise.Adresse})");
            }
        }
    }
}

Résultat :

On peut voir dans les résultats qu’au 23/10 l’adresse ne contenait pas de virgule, mais qu’au 26/11 elle en avait une. Pierre Dupont a également quitté l’entreprise tandis que Jacques Dupont l’a rejoint.

Il ne faut pas exécuter deux fois successives cette requête avec une date système différente dans le même contexte sinon le critère ne sera pas réactualisé.

Refactorisation

Notre code précédent est efficace mais pas forcément plaisant à écrire dans nos requêtes. L’idéal serait que notre DbSet puisse être automatiquement converti en Temporal via une méthode et éviter d’écrire tout ce SQL à la main.

On va donc créer une méthode d’extension « AsTemporal() » pour les DbSet<T> afin de récupérer le SQL et ce de façon dynamique :

public static class Extensions
{
    public static IQueryable<T> AsTemporal<T>(this DbSet<T> dbSet, DateTime date) where T : class
    {
        var context = (DemoTemporelleContext)dbSet.GetService<ICurrentDbContext>().Context;
        var table = context.GetTableName<T>();
        var selectSql = $"SELECT * FROM {table}";
        var sql = FormattableStringFactory.Create(selectSql + " FOR SYSTEM_TIME AS OF {0}", date);
        return dbSet.FromSqlInterpolated(sql);
    }

    private static string GetTableName<T>(this DemoTemporelleContext context) where T : class
    {
        var entityType = context.Model.FindEntityType(typeof(T));
        var schema = entityType.GetSchema() ?? "dbo";
        var tableName = entityType.GetTableName();
        return $"[{schema}].[{tableName}]";
    }
}

Grâce à la méthode GetTableName on peut dynamiquement récupérer le nom de la table SQL en fonction du modèle de DbSet. On aurait également pu récupérer la liste de toutes les colonnes de la même manière, pour simplifier nous utilisons ici « * ».

La requête est construite en deux instructions et en utilisant « FormattableStringFactory ». Contrairement aux exemples précédents on ne peut plus caster ici en « FormattableString » en utilisant « {table} » car le nom de table est dynamique, sinon ce nom de table serait injecté dans requête en tant que paramètre DbParameter et provoquera une erreur. On aurait toutefois pu écrire « var sql = FormattableStringFactory.Create(« SELECT * FROM  » + table +  » FOR SYSTEM_TIME AS OF {0} », date); ».

Maintenant que nos extensions sont créées, regardons comment le code est refactorisé :

static void ListerEmployeDate(DateTime date)
{
    Console.WriteLine($"Données au {date.ToString("g")}");
    using (var db = new DemoTemporelleContext())
    {
        var results = from employe in db.Employe.AsTemporal(date)
                        join entreprise in db.Entreprise.AsTemporal(date) on employe.EntrepriseId equals entreprise.Id
                        select new
                        {
                            Employe = employe,
                            Entreprise = entreprise
                        };
            
        foreach (var result in results)
        {
            Console.WriteLine($"{result.Employe.Id}- {result.Employe.Prenom} {result.Employe.Nom}: {result.Employe.Entreprise.Nom} ({result.Employe.Entreprise.Adresse})");
        }
    }
}

Le résultat obtenu est bien le même. Grâce à notre extension nous pouvons également bénéficier de toute la puissance de Linq puisque nous renvoyons un IQueryable<T> :

var result = db.Employe
    .AsTemporal(date)
    .Include(i => i.Entreprise)
    .FirstOrDefault(i => i.Nom == "Lautrou");

Console.WriteLine($"{result.Id}- {result.Prenom} {result.Nom}: {result.Entreprise.Nom} ({result.Entreprise.Adresse})");

Par exemple ci-dessus on récupère les informations d’un employé spécifique à une date donnée, mais on affiche les informations actuelles de son entreprise. Le SQL généré intègrera automatiquement le « SELECT TOP(1) » et la clause sur le nom en plus de la date historique. Voici la requête générée :

exec sp_executesql N'SELECT TOP(1) [e].[Id], [e].[EntrepriseId], [e].[Nom], [e].[Prenom], [e0].[Id], [e0].[Adresse], [e0].[Nom]
FROM (
    SELECT * FROM [dbo].[Employe] FOR SYSTEM_TIME AS OF @p0
) AS [e]
INNER JOIN [Entreprise] AS [e0] ON [e].[EntrepriseId] = [e0].[Id]
WHERE [e].[Nom] = N''Lautrou''',N'@p0 datetime2(7)',@p0='2019-11-27 17:26:10.1256588'

En retirant AsTemporal on aurait :

SELECT TOP(1) [e].[Id], [e].[EntrepriseId], [e].[Nom], [e].[Prenom], [e0].[Id], [e0].[Adresse], [e0].[Nom]
FROM [Employe] AS [e]
INNER JOIN [Entreprise] AS [e0] ON [e].[EntrepriseId] = [e0].[Id]
WHERE [e].[Nom] = N'Lautrou'

Notre librairie open-source

Jusqu’à présent nous n’avons utilisé que le requêtage pour une date spécifique. Afin de vous permettre de prendre en charge l’ensemble des requêtes temporelles (ALL, AS OF, BETWEEN, …) avec EF Core vous pouvez installer notre package NuGet EfCoreTemporalTable dont le code source et la documentation sont sur son GitHub :

dotnet add package EfCoreTemporalTable

Conclusion

Entity Framework Core ne supporte malheureusement pas nativement les tables temporelles mais, comme nous avons pu le voir dans cet article, il est très simple de le gérer avec une prise en charge complète via des méthodes d’extension. Grâce à la puissance des temporelles et à la souplesse d’Entity Framework Core, nous avons très facilement pu l’implémenter avec succès sur les projets réalisés au sein de Webnet.