internal async Task <bool> CheckHasExplicitUniqueConstrainAsync(DbContext context, TableInfo tableInfo, CancellationToken cancellationToken, bool isAsync)
{
string countUniqueConstrain = SqlQueryBuilderPostgreSql.CountUniqueConstrain(tableInfo);
bool hasUniqueConstrain = false;
using (var command = context.Database.GetDbConnection().CreateCommand())
{
//command.CommandText = @"SELECT COUNT(*) FROM ""Item""";
//var count = command.ExecuteScalar();
command.CommandText = countUniqueConstrain;
context.Database.OpenConnection();
if (isAsync)
{
using var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
if (reader.HasRows)
{
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
hasUniqueConstrain = (long)reader[0] == 1;
}
}
}
else
{
using var reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
hasUniqueConstrain = (long)reader[0] == 1;
}
}
}
}
return(hasUniqueConstrain);
}
public async Task TruncateAsync(DbContext context, TableInfo tableInfo, CancellationToken cancellationToken)
{
var sqlTruncateTable = SqlQueryBuilderPostgreSql.TruncateTable(tableInfo.FullTableName);
await context.Database.ExecuteSqlRawAsync(sqlTruncateTable, cancellationToken).ConfigureAwait(false);
}
protected async Task InsertAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync)
{
NpgsqlConnection connection = tableInfo.NpgsqlConnection;
bool closeConnectionInternally = false;
if (connection == null)
{
(connection, closeConnectionInternally) =
isAsync ? await OpenAndGetNpgsqlConnectionAsync(context, tableInfo.BulkConfig, cancellationToken).ConfigureAwait(false)
: OpenAndGetNpgsqlConnection(context, tableInfo.BulkConfig);
}
try
{
string sqlCopy = SqlQueryBuilderPostgreSql.InsertIntoTable(tableInfo, tableInfo.InsertToTempTable ? OperationType.InsertOrUpdate : OperationType.Insert);
using var writer = isAsync ? await connection.BeginBinaryImportAsync(sqlCopy, cancellationToken).ConfigureAwait(false)
: connection.BeginBinaryImport(sqlCopy);
var uniqueColumnName = tableInfo.PrimaryKeysPropertyColumnNameDict.Values.ToList().FirstOrDefault();
var doKeepIdentity = tableInfo.BulkConfig.SqlBulkCopyOptions == SqlBulkCopyOptions.KeepIdentity;
var propertiesColumnDict = ((tableInfo.InsertToTempTable || doKeepIdentity) && tableInfo.IdentityColumnName == uniqueColumnName)
? tableInfo.PropertyColumnNamesDict
: tableInfo.PropertyColumnNamesDict.Where(a => a.Value != tableInfo.IdentityColumnName);
var propertiesNames = propertiesColumnDict.Select(a => a.Key).ToList();
var entitiesCopiedCount = 0;
foreach (var entity in entities)
{
if (isAsync)
{
await writer.StartRowAsync(cancellationToken).ConfigureAwait(false);
}
else
{
writer.StartRow();
}
foreach (var propertyName in propertiesNames)
{
if (tableInfo.DefaultValueProperties.Contains(propertyName) && !tableInfo.PrimaryKeysPropertyColumnNameDict.ContainsKey(propertyName))
{
continue;
}
var propertyValue = tableInfo.FastPropertyDict.ContainsKey(propertyName) ? tableInfo.FastPropertyDict[propertyName].Get(entity) : null;
var propertyColumnName = tableInfo.PropertyColumnNamesDict.ContainsKey(propertyName) ? tableInfo.PropertyColumnNamesDict[propertyName] : string.Empty;
var columnType = tableInfo.ColumnNamesTypesDict[propertyColumnName];
// string is 'text' which works fine
if (columnType.StartsWith("character")) // when MaxLength is defined: 'character(1)' or 'character varying'
{
columnType = "character"; // 'character' is like 'string'
}
else if (columnType.StartsWith("varchar"))
{
columnType = "varchar";
}
else if (columnType.StartsWith("numeric"))
{
columnType = "numeric";
}
var convertibleDict = tableInfo.ConvertibleColumnConverterDict;
if (convertibleDict.ContainsKey(propertyColumnName) && convertibleDict[propertyColumnName].ModelClrType.IsEnum)
{
if (propertyValue != null)
{
var clrType = tableInfo.ConvertibleColumnConverterDict[propertyColumnName].ProviderClrType;
if (clrType == typeof(byte)) // columnType == "smallint"
{
propertyValue = (byte)propertyValue;
}
if (clrType == typeof(short))
{
propertyValue = (short)propertyValue;
}
if (clrType == typeof(Int32))
{
propertyValue = (int)propertyValue;
}
if (clrType == typeof(Int64))
{
propertyValue = (long)propertyValue;
}
if (clrType == typeof(string))
{
propertyValue = propertyValue.ToString();
}
}
}
if (isAsync)
{
await writer.WriteAsync(propertyValue, columnType, cancellationToken).ConfigureAwait(false);
}
else
{
writer.Write(propertyValue, columnType);
}
}
entitiesCopiedCount++;
if (progress != null && entitiesCopiedCount % tableInfo.BulkConfig.NotifyAfter == 0)
{
progress?.Invoke(ProgressHelper.GetProgress(entities.Count, entitiesCopiedCount));
}
}
if (isAsync)
{
await writer.CompleteAsync(cancellationToken).ConfigureAwait(false);
}
else
{
writer.Complete();
}
}
finally
{
if (closeConnectionInternally)
{
//connection.Close();
if (isAsync)
{
await connection.CloseAsync();
//await context.Database.CloseConnectionAsync().ConfigureAwait(false);
}
else
{
connection.Close();
//context.Database.CloseConnection();
}
}
}
}
// Truncate
public void Truncate(DbContext context, TableInfo tableInfo)
{
var sqlTruncateTable = SqlQueryBuilderPostgreSql.TruncateTable(tableInfo.FullTableName);
context.Database.ExecuteSqlRaw(sqlTruncateTable);
}
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
{
var entityPropertyWithDefaultValue = entities.GetPropertiesWithDefaultValue(type);
if (tableInfo.BulkConfig.CustomSourceTableName == null)
{
tableInfo.InsertToTempTable = true;
var sqlCreateTableCopy = SqlQueryBuilderPostgreSql.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateTableCopy, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateTableCopy);
}
}
bool hasUniqueConstrain = await CheckHasExplicitUniqueConstrainAsync(context, tableInfo, cancellationToken, isAsync);
if (hasUniqueConstrain == false)
{
if (tableInfo.EntityPKPropertyColumnNameDict == tableInfo.PrimaryKeysPropertyColumnNameDict)
{
hasUniqueConstrain = true; // ExplicitUniqueConstrain not required for PK
}
}
bool doDropUniqueConstrain = false;
try
{
if (tableInfo.BulkConfig.CustomSourceTableName == null)
{
if (isAsync)
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
}
else
{
Insert(context, type, entities, tableInfo, progress);
}
}
if (!hasUniqueConstrain)
{
string createUniqueIndex = SqlQueryBuilderPostgreSql.CreateUniqueIndex(tableInfo);
string createUniqueConstrain = SqlQueryBuilderPostgreSql.CreateUniqueConstrain(tableInfo);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(createUniqueIndex, cancellationToken).ConfigureAwait(false);
await context.Database.ExecuteSqlRawAsync(createUniqueConstrain, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(createUniqueIndex);
context.Database.ExecuteSqlRaw(createUniqueConstrain);
}
doDropUniqueConstrain = true;
}
var sqlMergeTable = SqlQueryBuilderPostgreSql.MergeTable <T>(context, tableInfo, operationType, entityPropertyWithDefaultValue);
if (operationType != OperationType.Read && (!tableInfo.BulkConfig.SetOutputIdentity || operationType == OperationType.Delete))
{
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlMergeTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlMergeTable);
}
}
else
{
List <T> outputEntities = tableInfo.LoadOutputEntities <T>(context, type, sqlMergeTable);
tableInfo.UpdateReadEntities(type, entities, outputEntities, context);
}
}
finally
{
if (doDropUniqueConstrain)
{
string dropUniqueConstrain = SqlQueryBuilderPostgreSql.DropUniqueConstrain(tableInfo);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(dropUniqueConstrain, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(dropUniqueConstrain);
}
}
if (!tableInfo.BulkConfig.UseTempDB)
{
if (tableInfo.BulkConfig.CustomSourceTableName == null)
{
var sqlDropTable = SqlQueryBuilderPostgreSql.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
}
}
}
