SQLite DatabaseExtension

DatabaseExtension.cs

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

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

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

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