EntityFramework Core et Sqlite : Upsert/Replace
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 :
- de vérifier si la ligne existe en base de données
- de l’insérer si elle n’existe pas
- 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 :
- Listages des propriétés liées à la base de données (GetProperties)
- Récupération du nom de la table (GetTableName)
- 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