# Entity Framework Core and SQLite: Upsert/Replace

> 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…

- Author: Jérôme Giacomini
- Language: en
- Canonical URL: [Entity Framework Core and SQLite: Upsert/Replace](https://jeromegiacomini.net/articles/2019/02/22/entity-framework-core-and-sqlite-upsert-replace)
- Published: 2019-02-22
- Last modified: 2026-09-05
- Topics: .NET, C#, DevOps, Xamarin

Today I had to insert or update massive thousands of lines into a database [It's just](https://www.sqlite.org/index.html) using [Entity Framework Core](https://docs.microsoft.com/fr-fr/ef/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


```csharp

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:**

```csharp

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](https://docs.microsoft.com/fr-fr/dotnet/csharp/programming-guide/classes-and-structs/extension-methods) 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:**

```csharp

    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:**

```csharp

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:**

```csharp

        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](https://docs.microsoft.com/fr-fr/ef/core/)

– [Official documentation of SQLite](https://www.sqlite.org/lang_conflict.html)

– [Download the source code](https://gist.github.com/jgiacomini/1b9ae9edabfdb4b7f76fb8605991ae28)