Today I had to insert or update massive thousands of lines into a database It's just using Entity Framework Core.

The context

A simple approach to carrying out this operation could be:

  1. to check if the line exists in a database
  2. to insert it if it does not exist
  3. to update it if it exists

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);
}

In my case, updating a few thousand lines could take several minutes. Moreover, in order not to harm the user experience, such processing must be as fast as possible in my Xamarin app.

The SQLite UPSERT command to rescue

Luckily, SQLite has a command Replacement which allows the line to be inserted or updated if it already exists.

For those familiar with MySQL, this is a query equivalent UPSERT.

Example d’use of the REPLACE method:


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

Dynamically generate the UPSERT command

We're going to write a method of extension which will extend the DbContext.

This method will be written in three key steps:

  1. Listings of database-related properties (GetProperties)
  2. Recovery of the table name (GetTableName)
  3. Generating the SQL query

Property recovery:


    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;
        }
    }

To dynamically retrieve the name of the table based on the type passed as a parameter.

Recovery of the table name:


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;
            }
        }

Once the two steps of dynamic recovery of the table name and properties are completed, just generate the SQL command.

The order generation:


        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 🙂

To go further:

– Entity Framework Core

– Official documentation of SQLite

– Download the source code