EntityFramework Core et Sqlite : mettre à jour ou insèrer une donnée (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.
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
Carousel rowToUpsert = ...;// var itemInDb = await dbContext.FirstOrDefaultAsync(c => 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’expé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 :
?
|
1
2
3
4
|
REPLACE INTO BlogPost (id, Title) VALUES(2, 'blog sur SQLite');// La ligne va être inséréeREPLACE INTO BlogPost (id, Title) VALUES(2, 'blog sur SQLite !');// La ligne existant déjà, elle va être mise à jour |
Si vous êtes vous voulez découvrir d’autres finesse de SQLite, n’hésitez pas à lire cet article.
Générer dynamiquement la commande UPSERT
Pour ce 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 :
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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; }} |
Récupération du nom de la table :
Pour récupérer dynamiquement le nom de la table en fonction du type passé en paramètre.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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 :
?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
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
– Bonnes pratiques pour gérer une base de données SQLite
Commentaires