Aujourd’hui je devais insérer ou mettre à jour massivement des milliers de lignes dans une base de données SQLite en utilisant Entity Framework Core.

Le contexte

Une approche simple pour effectuer cette opération pourrait être :

1. de vérifier si la ligne existe en base de données
2. de l’insérer si elle n’existe pas
3. de la mettre à jour si elle existe

Carousel rowToUpsert = ...;
// 

var itemInDb = await dbContext.FirstOrDefaultAsync(c => c.Id  == rowToUpsert.Id, cancellationToken);

if (itemIndb == null)
{
    await dbContext.AddAsync(rowToUpsert, cancellationToken);
}
else
{
    dbContext.Update(model);
}

Cependant, cette approche est anti-performante car, pour chaque ligne à mettre à jour, il faudrait effectuer deux requêtes. Dans mon cas, mettre à jour quelques milliers de lignes pourrait prendre plusieurs minutes ! De plus, pour ne pas nuire à l’éxpérience utilisateur, il faut que les traitements de ce genre soient le plus rapide possible dans mon app Xamarin.

La commande UPSERT de SQLite à la rescousse

Heureusement, SQLite dispose d’une commande REPLACE qui permet d’insérer ou mettre à jour la ligne si elle existe déjà.
Pour les familiers de MySQL, il s’agit d’un équivalent de la requête UPSERT.

Exemple d’utilisation de la méthode REPLACE :

REPLACE INTO BlogPost (id, Title) VALUES(2, 'blog sur SQLite');
// La ligne va être insérée
REPLACE INTO BlogPost (id, Title) VALUES(2, 'blog sur SQLite !');
// La ligne existe déjà elle va être mise à jour

Générer dynamiquement la commande UPSERT

Pour se faire nous allons écrire une méthode d’extension qui étendra le DbContext.

Cette méthode sera écrite en 3 étapes clefs :

1. Listages des propriétés liées à la base de données (GetProperties)
2. Récupération du nom de la table (GetTableName)
3. Génération de la requête SQL

Récupération des propriétés :

    public static class DatabaseExtension
    {
        private static readonly Dictionary<Type, List<PropertyInfo>> _properties = new Dictionary<Type, List<PropertyInfo>>();
        private static readonly Dictionary<Type, string> _tableNames = new Dictionary<Type, string>();
        private static readonly object _toLockTableName = new object();
        private static readonly object _toLockTypeProperties = new object();

        private static List<PropertyInfo> GetProperties<T>()
        {
            var type = typeof(T);
            List<PropertyInfo> props = null;
            lock (_toLockTypeProperties)
            {
                if (_properties.ContainsKey(type))
                {
                    props = _properties[type];
                }
                else
                {
                    props = type.GetProperties().Where(p => p.CanRead && p.CanWrite && !p.GetCustomAttributes<NotMappedAttribute>().Any()).ToList();
                    _properties[type] = props;
                }
            }
    
            return props;
        }
    }

Pour récupérer dynamiquement le nom de la table en fonction du type passé en paramètre.

Récupération du nom de la table :

public static string GetTableName<T>(this DbContext dbContext)
            where T : class
        {
            lock (_toLockTableName)
            {
                var type = typeof(T);
                if (_tableNames.ContainsKey(type))
                {
                    return _tableNames[type];
                }

                var model = dbContext.Model;
                var entityTypes = model.GetEntityTypes();
                var entityType = entityTypes.First(t => t.ClrType == type);
                var tableNameAnnotation = entityType.GetAnnotation("Relational:TableName");
                var tableName = tableNameAnnotation.Value.ToString();

                _tableNames[type] = tableName;
                return tableName;
            }
        }

Une fois les deux étapes de récupération dynamique du nom de la table et propriétés complétées, il suffit juste de générer la commande SQL.

Génération de la commande :

        public static async Task UpsertAsync<T>(this DbContext dbContext, T item, CancellationToken cancellationToken)
            where T : class
        {
            var props = GetProperties<T>();
            var sbQuery = new StringBuilder(3000);
            var sqliteParameters = new List<SqliteParameter>();
            var tableName = GetTableName<T>(dbContext);

            sbQuery.AppendLine($"REPLACE INTO {tableName}( ");

            for (int i = 0; i < props.Count; i++)
            {
                sbQuery.Append($"[{props[i].Name}] ");

                if (i == props.Count - 1)
                {
                    sbQuery.AppendLine($")");
                }
                else
                {
                    sbQuery.Append($", ");
                }
            }

            sbQuery.AppendLine($"VALUES ( ");
            for (int i = 0; i < props.Count; i++)
            {
                var parameterName = $"@param{i}";
                object parameterValue = props[i].GetValue(item);
                if (parameterValue == null)
                {
                    parameterValue = DBNull.Value;
                }

                sqliteParameters.Add(new SqliteParameter(parameterName, parameterValue));

                sbQuery.Append(parameterName);
                if (i == props.Count - 1)
                {
                    sbQuery.AppendLine($")");
                }
                else
                {
                    sbQuery.Append($", ");
                }
            }

            using (var command = dbContext.Database.GetDbConnection().CreateCommand())
            {
                foreach (var parameter in sqliteParameters)
                {
                    command.Parameters.Add(parameter);
                }

                await dbContext.Database.GetDbConnection().OpenAsync(cancellationToken);
                command.CommandText = sbQuery.ToString();
                var query = sbQuery.ToString();
                Debug.WriteLine(query);
                await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
            }
        }

Happy coding 🙂

Pour aller plus loin :
– Entity Framework Core
– Documentation officielle SQLite
– Télécharger le code source