public async static Task <int> UpdateFromQueryAsync <T>(this IQueryable <T> querable, Expression <Func <T, T> > updateExpression, int?commandTimeout = null,
CancellationToken cancellationToken = default) where T : class
{
int rowAffected = 0;
var dbContext = querable.GetDbContext();
using (var dbTransactionContext = new DbTransactionContext(dbContext))
{
var dbConnection = dbTransactionContext.Connection;
var dbTransaction = dbTransactionContext.CurrentTransaction;
try
{
var sqlQuery = SqlBuilder.Parse(querable.ToQueryString());
string setSqlExpression = updateExpression.ToSqlUpdateSetExpression(sqlQuery.GetTableAlias());
sqlQuery.ChangeToUpdate(sqlQuery.GetTableAlias(), setSqlExpression);
rowAffected = await dbContext.Database.ExecuteSqlRawAsync(sqlQuery.Sql, sqlQuery.Parameters.ToArray(), cancellationToken);
dbTransactionContext.Commit();
}
catch (Exception ex)
{
dbTransactionContext.Rollback();
throw ex;
}
}
return(rowAffected);
}
public static int InsertFromQuery <T>(this IQueryable <T> querable, string tableName, Expression <Func <T, object> > insertObjectExpression, int?commandTimeout = null) where T : class
{
int rowAffected = 0;
using (var dbTransactionContext = new DbTransactionContext(querable.GetDbContext()))
{
var dbContext = dbTransactionContext.DbContext;
try
{
var sqlQuery = SqlBuilder.Parse(querable.ToQueryString());
if (dbContext.Database.TableExists(tableName))
{
sqlQuery.ChangeToInsert(tableName, insertObjectExpression);
dbContext.Database.ToggleIdentityInsert(true, tableName);
rowAffected = dbContext.Database.ExecuteSql(sqlQuery.Sql, sqlQuery.Parameters.ToArray(), commandTimeout);
dbContext.Database.ToggleIdentityInsert(false, tableName);
}
else
{
sqlQuery.Clauses.First().InputText += string.Format(" INTO {0}", tableName);
rowAffected = dbContext.Database.ExecuteSql(sqlQuery.Sql, sqlQuery.Parameters.ToArray(), commandTimeout);
}
dbTransactionContext.Commit();
}
catch (Exception ex)
{
dbTransactionContext.Rollback();
throw ex;
}
}
return(rowAffected);
}
public async static Task <int> BulkUpdateAsync <T>(this DbContext context, IEnumerable <T> entities, BulkUpdateOptions <T> options, CancellationToken cancellationToken = default)
{
int rowsUpdated = 0;
var outputRows = new List <BulkMergeOutputRow <T> >();
var tableMapping = context.GetTableMapping(typeof(T));
using (var dbTransactionContext = new DbTransactionContext(context))
{
var dbContext = dbTransactionContext.DbContext;
var dbConnection = dbTransactionContext.Connection;
var transaction = dbTransactionContext.CurrentTransaction;
try
{
string stagingTableName = CommonUtil.GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
string[] columnNames = tableMapping.GetNonValueGeneratedColumns().ToArray();
string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().ToArray();
if (storeGeneratedColumnNames.Length == 0 && options.UpdateOnCondition == null)
{
throw new InvalidDataException("BulkUpdate requires that the entity have a primary key or the Options.UpdateOnCondition must be set.");
}
await context.Database.CloneTableAsync(destinationTableName, stagingTableName, null, null, cancellationToken);
await BulkInsertAsync(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, false, cancellationToken);
IEnumerable <string> columnstoUpdate = CommonUtil.FormatColumns(columnNames.Where(o => !options.IgnoreColumns.GetObjectProperties().Contains(o)));
string updateSetExpression = string.Join(",", columnstoUpdate.Select(o => string.Format("t.{0}=s.{0}", o)));
string updateSql = string.Format("UPDATE t SET {0} FROM {1} AS s JOIN {2} AS t ON {3}; SELECT @@RowCount;",
updateSetExpression, stagingTableName, destinationTableName, CommonUtil <T> .GetJoinConditionSql(options.UpdateOnCondition, storeGeneratedColumnNames, "s", "t"));
rowsUpdated = await context.Database.ExecuteSqlRawAsync(updateSql, cancellationToken);
dbContext.Database.DropTable(stagingTableName);
//ClearEntityStateToUnchanged(context, entities);
dbTransactionContext.Commit();
}
catch (Exception)
{
dbTransactionContext.Rollback();
throw;
}
return(rowsUpdated);
}
}
public async static Task <int> BulkDeleteAsync <T>(this DbContext context, IEnumerable <T> entities, BulkDeleteOptions <T> options, CancellationToken cancellationToken = default)
{
int rowsAffected = 0;
var tableMapping = context.GetTableMapping(typeof(T));
using (var dbTransactionContext = new DbTransactionContext(context))
{
var dbConnection = dbTransactionContext.Connection;
var transaction = dbTransactionContext.CurrentTransaction;
try
{
string stagingTableName = CommonUtil.GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
string[] keyColumnNames = options.DeleteOnCondition != null ? CommonUtil <T> .GetColumns(options.DeleteOnCondition, new[] { "s" })
: tableMapping.GetPrimaryKeyColumns().ToArray();
if (keyColumnNames.Length == 0 && options.DeleteOnCondition == null)
{
throw new InvalidDataException("BulkDelete requires that the entity have a primary key or the Options.DeleteOnCondition must be set.");
}
await context.Database.CloneTableAsync(destinationTableName, stagingTableName, keyColumnNames, null, cancellationToken);
await BulkInsertAsync(entities, options, tableMapping, dbConnection, transaction, stagingTableName, keyColumnNames, SqlBulkCopyOptions.KeepIdentity,
false, cancellationToken);
string deleteSql = string.Format("DELETE t FROM {0} s JOIN {1} t ON {2}", stagingTableName, destinationTableName,
CommonUtil <T> .GetJoinConditionSql(options.DeleteOnCondition, keyColumnNames));
rowsAffected = await context.Database.ExecuteSqlRawAsync(deleteSql, cancellationToken);
context.Database.DropTable(stagingTableName);
dbTransactionContext.Commit();
}
catch (Exception)
{
dbTransactionContext.Rollback();
throw;
}
return(rowsAffected);
}
}
public static int UpdateFromQuery <T>(this IQueryable <T> querable, Expression <Func <T, T> > updateExpression, int?commandTimeout = null) where T : class
{
int rowAffected = 0;
using (var dbTransactionContext = new DbTransactionContext(querable.GetDbContext()))
{
var dbContext = dbTransactionContext.DbContext;
try
{
var sqlQuery = SqlBuilder.Parse(querable.ToQueryString());
string setSqlExpression = updateExpression.ToSqlUpdateSetExpression(sqlQuery.GetTableAlias());
sqlQuery.ChangeToUpdate(sqlQuery.GetTableAlias(), setSqlExpression);
rowAffected = dbContext.Database.ExecuteSql(sqlQuery.Sql, sqlQuery.Parameters.ToArray(), commandTimeout);
dbTransactionContext.Commit();
}
catch (Exception ex)
{
dbTransactionContext.Rollback();
throw ex;
}
}
return(rowAffected);
}
public static int DeleteFromQuery <T>(this IQueryable <T> querable, int?commandTimeout = null) where T : class
{
int rowAffected = 0;
using (var dbTransactionContext = new DbTransactionContext(querable.GetDbContext()))
{
var dbContext = dbTransactionContext.DbContext;
try
{
var sqlQuery = SqlBuilder.Parse(querable.ToQueryString());
sqlQuery.ChangeToDelete();
rowAffected = dbContext.Database.ExecuteSql(sqlQuery.Sql, sqlQuery.Parameters.ToArray(), commandTimeout);
dbTransactionContext.Commit();
}
catch (Exception ex)
{
dbTransactionContext.Rollback();
throw ex;
}
}
return(rowAffected);
}
private static BulkMergeResult <T> InternalBulkMerge <T>(this DbContext context, IEnumerable <T> entities, BulkMergeOptions <T> options)
{
int rowsAffected = 0;
var outputRows = new List <BulkMergeOutputRow <T> >();
var tableMapping = context.GetTableMapping(typeof(T));
int rowsInserted = 0;
int rowsUpdated = 0;
int rowsDeleted = 0;
using (var dbTransactionContext = new DbTransactionContext(context))
{
var dbConnection = dbTransactionContext.Connection;
var transaction = dbTransactionContext.CurrentTransaction;
try
{
string stagingTableName = CommonUtil.GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
string[] columnNames = tableMapping.GetNonValueGeneratedColumns().ToArray();
string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().ToArray();
if (storeGeneratedColumnNames.Length == 0 && options.MergeOnCondition == null)
{
throw new InvalidDataException("BulkMerge requires that the entity have a primary key or the Options.MergeOnCondition must be set.");
}
context.Database.CloneTable(destinationTableName, stagingTableName, null, Common.Constants.InternalId_ColumnName);
var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, null, SqlBulkCopyOptions.KeepIdentity, true);
IEnumerable <string> columnsToInsert = CommonUtil.FormatColumns(columnNames.Where(o => !options.GetIgnoreColumnsOnInsert().Contains(o)));
IEnumerable <string> columnstoUpdate = CommonUtil.FormatColumns(columnNames.Where(o => !options.GetIgnoreColumnsOnUpdate().Contains(o))).Select(o => string.Format("t.{0}=s.{0}", o));
List <string> columnsToOutput = new List <string> {
"$Action", string.Format("{0}.{1}", "s", Constants.InternalId_ColumnName)
};
List <PropertyInfo> propertySetters = new List <PropertyInfo>();
Type entityType = typeof(T);
foreach (var storeGeneratedColumnName in storeGeneratedColumnNames)
{
columnsToOutput.Add(string.Format("inserted.[{0}]", storeGeneratedColumnName));
columnsToOutput.Add(string.Format("deleted.[{0}]", storeGeneratedColumnName));
propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName));
}
string mergeSqlText = string.Format("MERGE {0} t USING {1} s ON ({2}) WHEN NOT MATCHED BY TARGET THEN INSERT ({3}) VALUES ({3}) WHEN MATCHED THEN UPDATE SET {4}{5}OUTPUT {6};",
destinationTableName, stagingTableName, CommonUtil <T> .GetJoinConditionSql(options.MergeOnCondition, storeGeneratedColumnNames, "s", "t"),
SqlUtil.ConvertToColumnString(columnsToInsert),
SqlUtil.ConvertToColumnString(columnstoUpdate),
options.DeleteIfNotMatched ? " WHEN NOT MATCHED BY SOURCE THEN DELETE " : " ",
SqlUtil.ConvertToColumnString(columnsToOutput)
);
var bulkQueryResult = context.BulkQuery(mergeSqlText, dbConnection, transaction, options);
rowsAffected = bulkQueryResult.RowsAffected;
foreach (var result in bulkQueryResult.Results)
{
string id = string.Empty;
object entity = null;
string action = (string)result[0];
if (action != SqlMergeAction.Delete)
{
int entityId = (int)result[1];
id = (storeGeneratedColumnNames.Length > 0 ? Convert.ToString(result[2]) : "PrimaryKeyMissing");
entity = bulkInsertResult.EntityMap[entityId];
if (options.AutoMapOutputIdentity && entity != null)
{
for (int i = 2; i < 2 + storeGeneratedColumnNames.Length; i++)
{
propertySetters[0].SetValue(entity, result[i]);
}
}
}
else
{
id = Convert.ToString(result[2 + storeGeneratedColumnNames.Length]);
}
outputRows.Add(new BulkMergeOutputRow <T>(action, id));
if (action == SqlMergeAction.Insert)
{
rowsInserted++;
}
else if (action == SqlMergeAction.Update)
{
rowsUpdated++;
}
else if (action == SqlMergeAction.Delete)
{
rowsDeleted++;
}
}
context.Database.DropTable(stagingTableName);
//ClearEntityStateToUnchanged(context, entities);
dbTransactionContext.Commit();
}
catch (Exception)
{
dbTransactionContext.Rollback();
throw;
}
return(new BulkMergeResult <T>
{
Output = outputRows,
RowsAffected = rowsAffected,
RowsDeleted = rowsDeleted,
RowsInserted = rowsInserted,
RowsUpdated = rowsUpdated,
});
}
}
public static int BulkInsert <T>(this DbContext context, IEnumerable <T> entities, BulkInsertOptions <T> options)
{
int rowsAffected = 0;
var tableMapping = context.GetTableMapping(typeof(T));
using (var dbTransactionContext = new DbTransactionContext(context))
{
try
{
var dbConnection = dbTransactionContext.Connection;
var transaction = dbTransactionContext.CurrentTransaction;
string stagingTableName = CommonUtil.GetStagingTableName(tableMapping, options.UsePermanentTable, dbConnection);
string destinationTableName = string.Format("[{0}].[{1}]", tableMapping.Schema, tableMapping.TableName);
IEnumerable <string> columnNames = options.InputColumns != null?options.InputColumns.GetObjectProperties() : tableMapping.GetColumns(options.KeepIdentity);
columnNames = columnNames.Where(o => !options.IgnoreColumns.GetObjectProperties().Contains(o));
string[] storeGeneratedColumnNames = tableMapping.GetPrimaryKeyColumns().ToArray();
IEnumerable <string> columnsToInsert = CommonUtil.FormatColumns(columnNames);
columnNames = columnNames.Union(storeGeneratedColumnNames);
context.Database.CloneTable(destinationTableName, stagingTableName, columnNames, Common.Constants.InternalId_ColumnName);
var bulkInsertResult = BulkInsert(entities, options, tableMapping, dbConnection, transaction, stagingTableName, columnNames, SqlBulkCopyOptions.KeepIdentity, true);
List <string> columnsToOutput = new List <string> {
"$Action", string.Format("[{0}].[{1}]", "s", Constants.InternalId_ColumnName)
};
List <PropertyInfo> propertySetters = new List <PropertyInfo>();
Type entityType = typeof(T);
foreach (var storeGeneratedColumnName in storeGeneratedColumnNames)
{
columnsToOutput.Add(string.Format("[inserted].[{0}]", storeGeneratedColumnName));
propertySetters.Add(entityType.GetProperty(storeGeneratedColumnName));
}
string insertSqlText = string.Format("MERGE {0} t USING {1} s ON {2} WHEN NOT MATCHED THEN INSERT ({3}) VALUES ({3}){4};",
destinationTableName,
stagingTableName,
options.InsertIfNotExists ? CommonUtil <T> .GetJoinConditionSql(options.InsertOnCondition, storeGeneratedColumnNames, "t", "s") : "1=2",
SqlUtil.ConvertToColumnString(columnsToInsert),
columnsToOutput.Count > 0 ? " OUTPUT " + SqlUtil.ConvertToColumnString(columnsToOutput) : "");
if (options.KeepIdentity && storeGeneratedColumnNames.Length > 0)
{
context.Database.ToggleIdentityInsert(true, destinationTableName);
}
var bulkQueryResult = context.BulkQuery(insertSqlText, dbConnection, transaction, options);
if (options.KeepIdentity && storeGeneratedColumnNames.Length > 0)
{
context.Database.ToggleIdentityInsert(false, destinationTableName);
}
rowsAffected = bulkQueryResult.RowsAffected;
if (options.AutoMapOutputIdentity)
{
if (rowsAffected == entities.Count())
{
foreach (var result in bulkQueryResult.Results)
{
int entityId = (int)result[1];
var entity = bulkInsertResult.EntityMap[entityId];
for (int i = 2; i < columnsToOutput.Count; i++)
{
propertySetters[2 - i].SetValue(entity, result[i]);
}
}
}
}
context.Database.DropTable(stagingTableName);
//ClearEntityStateToUnchanged(context, entities);
dbTransactionContext.Commit();
return(rowsAffected);
}
catch (Exception ex)
{
dbTransactionContext.Rollback();
throw;
}
}
}
