Accueil > .Net, C#, Développement > [C#] Utiliser des tables temporaires avec Entity Framework

[C#] Utiliser des tables temporaires avec Entity Framework

Ça fait maintenant quelques temps que je travaille avec Entity Framework et, s’il est très bien, il pêche majoritairement sur un point (dans le cadre de mon usage) : le traitement du volume.
Donc, dans ce billet, je vais présenter une petite solution montrant ce que j’ai fait pour combler ce problème.

 

Situation

 
Je travaille sur des Echanges de Données Informatisées (EDI), donc je reçois un fichier que je dois traiter, créer des objets puis insérer le résultat en base de données.
Dans mon traitement (qu’on va nommer Flux #1), une ligne en entrée va générer deux lignes (dans deux tables différentes), j’ai donc un volume doublé. Ce qui ne pose pas de problème quand je reçois un fichier de 50-500 lignes. Mais pour s’assurer qu’on a une monté en charges qui permet de ne pas trop toucher au cœur pendant quelques années, on veut pouvoir lui faire manger plus que le volume actuel (les 50-500 lignes). On a donc choisit de tester sur 50 000 lignes. Un facteur 100, c’est pas mal🙂

Du coup, quand EF va vouloir insérer les 50 000 lignes, il va faire…50 000 insert. Le problème a été résolu avec le BulkInsert (voir : [C#] Entity Framework et Bulk Insert).

Mais sur un autre flux (nommé Flux #2), je vais devoir aller modifier ces lignes (les 50 000) pour dire qu’elles ont été effectivement traitées (en mettant un flag [OK/KO], une date de traitement…, peu importe).

Grosso modo, le Flux #1 permet de dire : « Ok, on a vu vos lignes, on va les traiter » et le Flux #2 : « On a bien traités ces lignes ».

 

Résolution

 
Et là, j’ai eu une réflexion assez fourbe (c’était un matin, avant le café) : je vais charger le minium de données dans une table temporaire (la clef primaire de la ligne que je veux modifier ou quelque chose permettant de la trouver) ainsi que mes informations à modifier (date de traitement, valeur du flag, valeurs des champs d’audit…). Cette table temporaire, je vais ensuite l’utiliser au sein d’une procédure stockée pour effectivement modifier mes champs.
Il faut donc être dans une même connexion et, le cas échéant, dans une même transaction.

Procédure qui va ressembler peu ou prou à ça :

UPDATE MaTable
SET 
	MaTable.DateTraitement = #TempExample.DateTraitement,
	MaTable.Traite = #TempExample.Flag
	MaTable.Audit = #TempExample.Chaine
FROM MaTable
JOIN #TempExample
	ON #TempExample.Numerique = MaTable.Id

 

Le Code

 

Je vais donc créer un objet simple qui va modéliser ma table :

using Kr.Samples.Helpers;
using System;

namespace Kr.Samples.TempTables
{
    public class TempExample
    {
        [DbColumn(Type = "INT IDENTITY(1,1)")]
        public int Id { get; set; }

        [DbColumn(Type = "VARCHAR(50)")]
        public string Chaine { get; set; }

        [DbColumn(Type = "DATETIME")]
        public DateTime DateTraitement { get; set; }

        [DbColumn(Type = "NUMERIC(5)")]
        public decimal Numerique { get; set; }

        [DbColumn(Type = "BIT")]
        public bool Flag { get; set; }
    }
}

Les attributs positionnés sont uniquement présents pour générer le script de création de la table ainsi que pour le BulkInsert.
La variable Type est très importante, il doit y avoir correspondance entre le type côté .Net et le type côté Sql Server. Pour cela, je vous recommande chaudement ceci : Mapping CLR Parameter Data.

namespace Kr.Samples.Helpers
{
    /// <summary>
    /// Attribut à positionner sur les propritétés des classes permettant de gérer les tables temporaires.
    /// </summary>
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
    public class DbColumnAttribute : Attribute
    {
        /// <summary>
        /// Type SQL Server de la donnée.
        /// </summary>
        public string Type { get; set; }
    }
}

using System.Reflection;

namespace Kr.Samples.Helpers
{
    /// <summary>
    /// Classe permettant de manipuler les informations sur les colonnes des tables temporaires.
    /// </summary>
    public class DbColumn
    {
        /// <summary>
        /// Type SQL Server de la donnée.
        /// </summary>
        public string Type { get; set; }

        /// <summary>
        /// Type de données.
        /// </summary>
        public PropertyInfo Property { get; set; }

        /// <summary>
        /// Initialise une nouvelle instance de la classe <see cref="DbColumn" />.
        /// </summary>
        /// <param name="attribute">Objet <see cref="DbColumnAttribute"/> de base.</param>
        public DbColumn(DbColumnAttribute attribute)
        {
            Type = attribute.Type;
        }
    }
}

La seconde classe (DbColumn) est présent parce que je n’aime pas travailler sur des attributs. C’est tout.

Enfin, voici toute la magie du truc :

using Kr.Samples.Extends;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Core.EntityClient;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;

namespace Kr.Samples.Helpers
{
    /// <summary>
    /// Classe permettant de gérer les tables temporaires.
    /// </summary>
    /// <typeparam name="T">Type de la table temporaire.</typeparam>
    public class TempTableHelper<T>
        where T : new()
    {
        /// <summary>
        /// Dictionnaire contenant les informations liées aux colonnes.
        /// </summary>
        private Dictionary<string, DbColumn> _dbColumn;

        /// <summary>
        /// Instruction SQL permettant de créer la table temporaire.
        /// </summary>
        private string _scriptCreateTable;

        /// <summary>
        /// Instruction SQL permettant de réaliser un select sur la table temporaire.
        /// </summary>
        private string _scriptSelect;

        /// <summary>
        /// Nom de la table temporaire.
        /// </summary>
        protected string TableName;

        /// <summary>
        /// Méthode qui va transformer une ligne du IDataReader en un objet.
        /// </summary>
        private Action<KeyValuePair<string, DbColumn>, IDataReader, T> _mapMethod;

        /// <summary>
        /// Méthode qui va s'exécuter sur chaque ligne retournée par le IDataReader.
        /// </summary>
        private Func<IDataReader, T> _createMethod;

        /// <summary>
        /// Initialise une nouvelle instance de la classe <see cref="TempTableBase{T}" />.
        /// </summary>
        /// <param name="tableName">Nom de la table temporaire.</param>
        public TempTableHelper(string tableName = null)
        {
            TableName = string.IsNullOrEmpty(tableName)
                ? string.Format("#{0}", typeof(T).Name)
                : tableName;

            Initialize();
        }

        /// <summary>
        /// Permet de créer le dictionnaire qui va contenir les informations de colonnes.
        /// </summary>
        private void GetProperties()
        {
            if (_dbColumn == null)
            {
                //// Récupération des propriétés de T.
                _dbColumn = (
                        from properties in typeof(T).GetProperties()
                        let custom = properties.GetCustomAttributes(typeof(DbColumnAttribute), true)
                        where custom.Count() == 1
                        let p = properties
                        select new {
                            Property = p,
                            Attribute = (DbColumnAttribute)custom[0]
                        }).ToDictionary(p => p.Property.Name, arg => new DbColumn(arg.Attribute) { Property = arg.Property });
            }
        }

        /// <summary>
        /// Méthode de création du script de création de la table temporaire.
        /// </summary>
        private void CreateScript()
        {
            if (!string.IsNullOrWhiteSpace(_scriptCreateTable)) return;

            var str = new StringBuilder();
            str.AppendFormat("CREATE TABLE {0} ( ", TableName);
            str.Append(
                string.Join(", ",
                    _dbColumn.Select(
                        kvp => string.Format("{0} {1}", kvp.Key, kvp.Value.Type))));
            str.Append(")");

            _scriptCreateTable = str.ToString();
        }

        /// <summary>
        /// Méthode de création du script de selection de la table temporaire.
        /// </summary>
        private void SelectScript()
        {
            if (!string.IsNullOrWhiteSpace(_scriptSelect)) return;

            var str = new StringBuilder();
            // Génération de l'instruction SELECT :
            // http://stackoverflow.com/questions/17637697/select-pros-cons.
            str.Append("SELECT ");
            str.Append(
                string.Join(", ", 
                    _dbColumn.Select(kvp => kvp.Key)));
            str.AppendFormat(" FROM {0}", TableName);

            _scriptSelect = str.ToString();
        }

        /// <summary>
        /// Initialisation pour le bon fonctionnement del 'objet.
        /// </summary>
        protected void Initialize()
        {
            GetProperties();
            CreateScript();
            SelectScript();
        }

        /// <summary>
        /// Permet de créer la table dans la base de données.
        /// </summary>
        /// <param name="context">Objet Entity Framework qui contient les informations de connexion.</param>
        public void CreateTable(DbContext context)
        {
            EfHelper.OpenConnection(context);
            context.Database.ExecuteSqlCommand(_scriptCreateTable);
        }

        /// <summary>
        /// Permet de récupérer les données depuis la base de données.
        /// </summary>
        /// <param name="context">Objet Entity Framework qui contient les informations de connexion.</param>
        /// <param name="transaction">Transaction dans laquelle s'inscrire.</param>
        /// <returns><see cref="DataTable"/> contenant les données de la base de données.</returns>
        public DataTable GetData(DbContext context, DbTransaction transaction = null)
        {
            return EfHelper.ExecuteCommand(context, _scriptSelect, transaction);
        }

        /// <summary>
        /// Préparation des méthodes pour mapper les données.
        /// </summary>
        private void PrepareGet()
        {
            if (_mapMethod != null && _createMethod != null) return;

            // Récupération du constructeur par défaut.
            var ctor = typeof(T).GetConstructor(
                BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public,
                null,
                new Type[] { },
                null);

            // Transformation en expression et compilation du constructeur.
            var newCreator = Expression.New(ctor);
            var lambda = Expression.Lambda<Func<T>>(newCreator);
            var compiled = lambda.Compile();

            // Création de la méthode qui va transformer une ligne du IDataReader en un objet.
            _mapMethod = new Action<KeyValuePair<string, DbColumn>, IDataReader, T>((kvp, dr, obj) =>
            {
                var value = dr[kvp.Key];
                if (value != DBNull.Value)
                {
                    kvp.Value.Property.SetValue(obj, value, null);
                }
            });

            // Création de la méthode qui va s'exécuter sur chaque ligne retournée par le IDataReader.
            _createMethod = new Func<IDataReader, T>(dr =>
            {
                var obj = compiled();
                _dbColumn.ForEach(kvp => _mapMethod(kvp, dr, obj));
                return obj;
            });
        }

        /// <summary>
        /// Récupère les données de la table temporaire sous forme de liste.
        /// </summary>
        /// <param name="context">Objet Entity Framework qui contient les informations de connexion.</param>
        /// <param name="transaction">Transaction dans laquelle s'inscrire.</param>
        /// <returns>Liste d'objets.</returns>
        public List<T> GetListData(DbContext context, DbTransaction transaction = null)
        {
            PrepareGet();
            return EfHelper.ExecuteCommand(context, _scriptSelect, _createMethod, transaction);
        }
        
        /// <summary>
        /// Permet d'envoyer les données vers la base de données.
        /// </summary>
        /// <param name="context">Objet Entity Framework qui contient les informations de connexion.</param>
        /// <param name="data">Collection contenant les données à envoyer.</param>
        /// <param name="transaction">Transaction dans laquelle s'inscrire.</param>
        public void LoadData(DbContext context, List<T> data, DbTransaction transaction = null)
        {
            if(data.IsNullOrEmpty()) return;

            EfHelper.OpenConnection(context);

            var bulkInsert = transaction == null 
                ? new BulkInsertHelper(context.Database.Connection)
                : new BulkInsertHelper(context.Database.Connection, transaction);

            bulkInsert.WriteToServer(TableName, data);
        }

        /// <summary>
        /// Permet d'envoyer les données vers la base de données.
        /// </summary>
        /// <param name="bulkInsert">Object <see cref="BulkInsertHelper"/> à utiliser.</param>
        /// <param name="data">Collection contenant les données à envoyer.</param>
        public void LoadData(BulkInsertHelper bulkInsert, List<T> data)
        {
            if (data.IsNullOrEmpty()) return;

            bulkInsert.WriteToServer(TableName, data);
        }
    }
}

Le code est expliqué au fil de l’eau et, au final, je ne pense pas qu’il soit très complexe.

En usage, on a donc ceci :

        private static void TempTableExample(int iterations)
        {
            var tempTable = new TempTableHelper<TempExample>();

            var data = GenerateData(iterations);

            using (var context = new SAMPLESEntities())
            {
                EfHelper.OpenConnection(context);

                tempTable.CreateTable(context);
                tempTable.LoadData(context, data);
                var retourDataTable = tempTable.GetData(context);
                var retourList = tempTable.GetListData(context);
            }
        }

 

Conclusion

 

Et en termes de performances (en millisecondes par nombre de lignes) ?

Table Temporaire et EF - Performances

La récupération en DataTable utilise uniquement ADO.Net, tandis que List #1 puis #2 (pour voir l’impact de la génération des méthodes _mapMethod & _createMethod) utilise du code « custom ».
Comme il est possible de voir, ADO .Net sans fioriture est toujours plus performant.
Il vaut mieux rester sous les 100 000 lignes pour avoir des performances pas trop mauvaises.
C’est relativement régulier à 0,005 millisecondes par ligne.

Après, il y a très certainement d’autres méthodes, surement moins fourbe.
Mais pour le coup, je manque d’imagination (ou expériences, c’est selon).
Du coup, si vous avez de meilleures solutions, je suis réellement preneur.

En termes d’améliorations, on va pouvoir travailler sur l’attribut DbColumnAttribute pour gérer les types via une enum, mais dans ce cas il faut aussi gérer les précision et scale, l’identity, éventuellement ajouter le fait que ce soit nullable ou non, etc. On peut aller loin dans le contrôle, mais alors on tend vers la complexité.

Sources

Les sources sont disponibles ici (n’inclue pas les packages, donc EF6).

Catégories :.Net, C#, Développement
  1. Aucun commentaire pour l’instant.
  1. No trackbacks yet.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

%d blogueurs aiment cette page :