public void Merge <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType,
Action <decimal> progress) where T : class
{
var connection = OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
bool doExplicitCommit = false;
try
{
if (context.Database.CurrentTransaction == null)
{
//context.Database.UseTransaction(connection.BeginTransaction());
doExplicitCommit = true;
}
var transaction = (SqliteTransaction)(context.Database.CurrentTransaction == null ?
connection.BeginTransaction() :
context.Database.CurrentTransaction.GetUnderlyingTransaction(tableInfo.BulkConfig));
var command = GetSqliteCommand(context, type, entities, tableInfo, connection, transaction);
type = tableInfo.HasAbstractList ? entities[0].GetType() : type;
int rowsCopied = 0;
foreach (var item in entities)
{
LoadSqliteValues(tableInfo, item, command);
command.ExecuteNonQuery();
ProgressHelper.SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
}
if (operationType != OperationType.Delete && tableInfo.BulkConfig.SetOutputIdentity && tableInfo.IdentityColumnName != null)
{
command.CommandText = SqlQueryBuilderSqlite.SelectLastInsertRowId();
long lastRowIdScalar = (long)command.ExecuteScalar();
string identityPropertyName = tableInfo.IdentityColumnName;
var identityPropertyInteger = false;
var identityPropertyUnsigned = false;
var identityPropertyByte = false;
var identityPropertyShort = false;
if (tableInfo.FastPropertyDict[identityPropertyName].Property.PropertyType == typeof(ulong))
{
identityPropertyUnsigned = true;
}
else if (tableInfo.FastPropertyDict[identityPropertyName].Property.PropertyType == typeof(uint))
{
identityPropertyInteger = true;
identityPropertyUnsigned = true;
}
else if (tableInfo.FastPropertyDict[identityPropertyName].Property.PropertyType == typeof(int))
{
identityPropertyInteger = true;
}
else if (tableInfo.FastPropertyDict[identityPropertyName].Property.PropertyType == typeof(ushort))
{
identityPropertyShort = true;
identityPropertyUnsigned = true;
}
else if (tableInfo.FastPropertyDict[identityPropertyName].Property.PropertyType == typeof(short))
{
identityPropertyShort = true;
}
else if (tableInfo.FastPropertyDict[identityPropertyName].Property.PropertyType == typeof(byte))
{
identityPropertyByte = true;
identityPropertyUnsigned = true;
}
else if (tableInfo.FastPropertyDict[identityPropertyName].Property.PropertyType == typeof(sbyte))
{
identityPropertyByte = true;
}
for (int i = entities.Count - 1; i >= 0; i--)
{
if (identityPropertyByte)
{
if (identityPropertyUnsigned)
{
tableInfo.FastPropertyDict[identityPropertyName].Set(entities[i], (byte)lastRowIdScalar);
}
else
{
tableInfo.FastPropertyDict[identityPropertyName].Set(entities[i], (sbyte)lastRowIdScalar);
}
}
else if (identityPropertyShort)
{
if (identityPropertyUnsigned)
{
tableInfo.FastPropertyDict[identityPropertyName].Set(entities[i], (ushort)lastRowIdScalar);
}
else
{
tableInfo.FastPropertyDict[identityPropertyName].Set(entities[i], (short)lastRowIdScalar);
}
}
else if (identityPropertyInteger)
{
if (identityPropertyUnsigned)
{
tableInfo.FastPropertyDict[identityPropertyName].Set(entities[i], (uint)lastRowIdScalar);
}
else
{
tableInfo.FastPropertyDict[identityPropertyName].Set(entities[i], (int)lastRowIdScalar);
}
}
else
{
if (identityPropertyUnsigned)
{
tableInfo.FastPropertyDict[identityPropertyName].Set(entities[i], (ulong)lastRowIdScalar);
}
else
{
tableInfo.FastPropertyDict[identityPropertyName].Set(entities[i], lastRowIdScalar);
}
}
lastRowIdScalar--;
}
}
if (doExplicitCommit)
{
transaction.Commit();
}
}
finally
{
context.Database.CloseConnection();
}
}
internal static SqliteCommand GetSqliteCommand <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, SqliteConnection connection, SqliteTransaction transaction)
{
SqliteCommand command = connection.CreateCommand();
command.Transaction = transaction;
var operationType = tableInfo.BulkConfig.OperationType;
switch (operationType)
{
case OperationType.Insert:
command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.Insert);
break;
case OperationType.InsertOrUpdate:
command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.InsertOrUpdate);
break;
case OperationType.InsertOrUpdateDelete:
throw new NotSupportedException("Sqlite supports only UPSERT(analog for MERGE WHEN MATCHED) but does not have functionality to do: 'WHEN NOT MATCHED BY SOURCE THEN DELETE'" +
"What can be done is to read all Data, find rows that are not in input List, then with those do the BulkDelete.");
case OperationType.Update:
command.CommandText = SqlQueryBuilderSqlite.UpdateSetTable(tableInfo);
break;
case OperationType.Delete:
command.CommandText = SqlQueryBuilderSqlite.DeleteFromTable(tableInfo);
break;
}
type = tableInfo.HasAbstractList ? entities[0].GetType() : type;
var entityType = context.Model.FindEntityType(type);
var entityPropertiesDict = entityType.GetProperties().Where(a => tableInfo.PropertyColumnNamesDict.ContainsKey(a.Name)).ToDictionary(a => a.Name, a => a);
var properties = type.GetProperties();
foreach (var property in properties)
{
if (entityPropertiesDict.ContainsKey(property.Name))
{
var propertyEntityType = entityPropertiesDict[property.Name];
string columnName = propertyEntityType.GetColumnName();
var propertyType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
/*var sqliteType = SqliteType.Text; // "String" || "Decimal" || "DateTime"
* if (propertyType.Name == "Int16" || propertyType.Name == "Int32" || propertyType.Name == "Int64")
* sqliteType = SqliteType.Integer;
* if (propertyType.Name == "Float" || propertyType.Name == "Double")
* sqliteType = SqliteType.Real;
* if (propertyType.Name == "Guid" )
* sqliteType = SqliteType.Blob; */
var parameter = new SqliteParameter($"@{columnName}", propertyType); // ,sqliteType // ,null
command.Parameters.Add(parameter);
}
}
var shadowProperties = tableInfo.ShadowProperties;
foreach (var shadowProperty in shadowProperties)
{
var parameter = new SqliteParameter($"@{shadowProperty}", typeof(string));
command.Parameters.Add(parameter);
}
command.Prepare(); // Not Required (check if same efficiency when removed)
return(command);
}
protected async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync) where T : class
{
SqliteConnection connection = isAsync ? await OpenAndGetSqliteConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false)
: OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
bool doExplicitCommit = false;
SqliteTransaction transaction = null;
try
{
if (context.Database.CurrentTransaction == null)
{
//context.Database.UseTransaction(connection.BeginTransaction());
doExplicitCommit = true;
}
transaction = doExplicitCommit ? connection.BeginTransaction()
: (SqliteTransaction)context.Database.CurrentTransaction.GetUnderlyingTransaction(tableInfo.BulkConfig);
SqliteCommand command = connection.CreateCommand();
command.Transaction = transaction;
// CREATE
command.CommandText = SqlQueryBuilderSqlite.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName);
if (isAsync)
{
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
}
else
{
command.ExecuteNonQuery();
}
tableInfo.BulkConfig.OperationType = OperationType.Insert;
tableInfo.InsertToTempTable = true;
tableInfo.SqliteConnection = connection;
tableInfo.SqliteTransaction = transaction;
// INSERT
if (isAsync)
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
}
else
{
InsertAsync(context, type, entities, tableInfo, progress, cancellationToken, isAsync: false).GetAwaiter().GetResult();
}
// JOIN
List <T> existingEntities;
var sqlSelectJoinTable = SqlQueryBuilder.SelectJoinTable(tableInfo);
Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlSelectJoinTable, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).ToList();
tableInfo.UpdateReadEntities(type, entities, existingEntities);
// DROP
command.CommandText = SqlQueryBuilderSqlite.DropTable(tableInfo.FullTempTableName);
if (isAsync)
{
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
}
else
{
command.ExecuteNonQuery();
}
if (doExplicitCommit)
{
transaction.Commit();
}
}
finally
{
if (doExplicitCommit)
{
if (isAsync)
{
await transaction.DisposeAsync();
await context.Database.CloseConnectionAsync().ConfigureAwait(false);
}
else
{
transaction.Dispose();
context.Database.CloseConnection();
}
}
}
}
internal static SqliteCommand GetSqliteCommand <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, SqliteConnection connection, SqliteTransaction transaction)
{
SqliteCommand command = connection.CreateCommand();
command.Transaction = transaction;
var operationType = tableInfo.BulkConfig.OperationType;
switch (operationType)
{
case OperationType.Insert:
command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.Insert);
break;
case OperationType.InsertOrUpdate:
command.CommandText = SqlQueryBuilderSqlite.InsertIntoTable(tableInfo, OperationType.InsertOrUpdate);
break;
case OperationType.InsertOrUpdateDelete:
throw new NotSupportedException("'BulkInsertOrUpdateDelete' not supported for Sqlite. Sqlite has only UPSERT statement (analog for MERGE WHEN MATCHED) but no functionality for: 'WHEN NOT MATCHED BY SOURCE THEN DELETE'." +
" Another way to achieve this is to BulkRead existing data from DB, split list into sublists and call separately Bulk methods for Insert, Update, Delete.");
case OperationType.Update:
command.CommandText = SqlQueryBuilderSqlite.UpdateSetTable(tableInfo);
break;
case OperationType.Delete:
command.CommandText = SqlQueryBuilderSqlite.DeleteFromTable(tableInfo);
break;
}
type = tableInfo.HasAbstractList ? entities[0].GetType() : type;
var entityType = context.Model.FindEntityType(type);
var entityPropertiesDict = entityType.GetProperties().Where(a => tableInfo.PropertyColumnNamesDict.ContainsKey(a.Name)).ToDictionary(a => a.Name, a => a);
var properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
foreach (var property in properties)
{
if (entityPropertiesDict.ContainsKey(property.Name))
{
var propertyEntityType = entityPropertiesDict[property.Name];
string columnName = propertyEntityType.GetColumnName(tableInfo.ObjectIdentifier);
var propertyType = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
//SqliteType(CpropertyType.Name): Text(String, Decimal, DateTime); Integer(Int16, Int32, Int64) Real(Float, Double) Blob(Guid)
var parameter = new SqliteParameter($"@{property.Name}", propertyType); // ,sqliteType // ,null //()
command.Parameters.Add(parameter);
}
}
var shadowProperties = tableInfo.ShadowProperties;
foreach (var shadowProperty in shadowProperties)
{
var parameter = new SqliteParameter($"@{shadowProperty}", typeof(string));
command.Parameters.Add(parameter);
}
command.Prepare(); // Not Required but called for efficiency (prepared should be little faster)
return(command);
}
protected async Task MergeAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, OperationType operationType, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync) where T : class
{
SqliteConnection connection = isAsync ? await OpenAndGetSqliteConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false)
: OpenAndGetSqliteConnection(context, tableInfo.BulkConfig);
bool doExplicitCommit = false;
try
{
if (context.Database.CurrentTransaction == null)
{
//context.Database.UseTransaction(connection.BeginTransaction());
doExplicitCommit = true;
}
var dbTransaction = doExplicitCommit ? connection.BeginTransaction()
: context.Database.CurrentTransaction.GetUnderlyingTransaction(tableInfo.BulkConfig);
var transaction = (SqliteTransaction)dbTransaction;
var command = GetSqliteCommand(context, type, entities, tableInfo, connection, transaction);
type = tableInfo.HasAbstractList ? entities[0].GetType() : type;
int rowsCopied = 0;
foreach (var item in entities)
{
LoadSqliteValues(tableInfo, item, command, context);
if (isAsync)
{
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
}
else
{
command.ExecuteNonQuery();
}
ProgressHelper.SetProgress(ref rowsCopied, entities.Count, tableInfo.BulkConfig, progress);
}
if (operationType == OperationType.Insert && tableInfo.BulkConfig.SetOutputIdentity && tableInfo.IdentityColumnName != null) // For Sqlite Identity can be set by Db only with pure Insert method
{
command.CommandText = SqlQueryBuilderSqlite.SelectLastInsertRowId();
object lastRowIdScalar = isAsync ? await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false)
: command.ExecuteScalar();
SetIdentityForOutput(entities, tableInfo, lastRowIdScalar);
}
if (doExplicitCommit)
{
transaction.Commit();
}
}
finally
{
if (isAsync)
{
await context.Database.CloseConnectionAsync().ConfigureAwait(false);
}
else
{
context.Database.CloseConnection();
}
}
}
