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