public void SelectJoinTableReadTest()
{
TableInfo tableInfo = GetTestTableInfo();
tableInfo.BulkConfig.UpdateByProperties = new List <string> {
nameof(Item.Name)
};
string result = SqlQueryBuilder.SelectJoinTable(tableInfo);
string expected = "SELECT S.[ItemId], S.[Name] FROM [dbo].[Item] AS S " +
"JOIN [dbo].[ItemTemp1234] AS J " +
"ON S.[ItemId] = J.[ItemId]";
Assert.Equal(result, expected);
}
public async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress,
CancellationToken cancellationToken) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo(context);
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo), cancellationToken).ConfigureAwait(false);
try
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
tableInfo.PropertyColumnNamesDict = tableInfo.OutputPropertyColumnNamesDict;
var sqlQuery = SqlQueryBuilder.SelectJoinTable(tableInfo);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict;
List <T> existingEntities;
if (typeof(T) == type)
{
Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlQuery, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).ToList();
}
else
{
Expression <Func <DbContext, IEnumerable> > expression = tableInfo.GetQueryExpression(type, sqlQuery, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).Cast <T>().ToList();
}
tableInfo.UpdateReadEntities(type, entities, existingEntities);
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
await context.Database.ExecuteSqlRawAsync(SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB), cancellationToken).ConfigureAwait(false);
}
}
}
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();
}
}
}
}
protected async Task ReadAsync <T>(DbContext context, Type type, IList <T> entities, TableInfo tableInfo, Action <decimal> progress, CancellationToken cancellationToken, bool isAsync) where T : class
{
Dictionary <string, string> previousPropertyColumnNamesDict = tableInfo.ConfigureBulkReadTableInfo();
var sqlCreateTableCopy = SqlQueryBuilder.CreateTableCopy(tableInfo.FullTableName, tableInfo.FullTempTableName, tableInfo);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlCreateTableCopy, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlCreateTableCopy);
}
try
{
if (isAsync)
{
await InsertAsync(context, type, entities, tableInfo, progress, cancellationToken).ConfigureAwait(false);
}
else
{
InsertAsync(context, type, entities, tableInfo, progress, cancellationToken, isAsync: false).GetAwaiter().GetResult();
}
tableInfo.PropertyColumnNamesDict = tableInfo.OutputPropertyColumnNamesDict;
var sqlSelectJoinTable = SqlQueryBuilder.SelectJoinTable(tableInfo);
tableInfo.PropertyColumnNamesDict = previousPropertyColumnNamesDict; // TODO Consider refactor and integrate with TimeStampPropertyName, also check for Calculated props.
// Output only PropertisToInclude and for getting Id with SetOutputIdentity
if (tableInfo.TimeStampPropertyName != null && !tableInfo.PropertyColumnNamesDict.ContainsKey(tableInfo.TimeStampPropertyName))
{
tableInfo.PropertyColumnNamesDict.Add(tableInfo.TimeStampPropertyName, tableInfo.TimeStampColumnName);
}
List <T> existingEntities;
if (typeof(T) == type)
{
Expression <Func <DbContext, IQueryable <T> > > expression = tableInfo.GetQueryExpression <T>(sqlSelectJoinTable, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).ToList();
}
else // TODO: Consider removing
{
Expression <Func <DbContext, IEnumerable> > expression = tableInfo.GetQueryExpression(type, sqlSelectJoinTable, false);
var compiled = EF.CompileQuery(expression); // instead using Compiled queries
existingEntities = compiled(context).Cast <T>().ToList();
}
tableInfo.UpdateReadEntities(type, entities, existingEntities);
if (tableInfo.TimeStampPropertyName != null && !tableInfo.PropertyColumnNamesDict.ContainsKey(tableInfo.TimeStampPropertyName))
{
tableInfo.PropertyColumnNamesDict.Remove(tableInfo.TimeStampPropertyName);
}
}
finally
{
if (!tableInfo.BulkConfig.UseTempDB)
{
var sqlDropTable = SqlQueryBuilder.DropTable(tableInfo.FullTempTableName, tableInfo.BulkConfig.UseTempDB);
if (isAsync)
{
await context.Database.ExecuteSqlRawAsync(sqlDropTable, cancellationToken).ConfigureAwait(false);
}
else
{
context.Database.ExecuteSqlRaw(sqlDropTable);
}
}
}
}