/// <summary>
/// An IQueryable<T> extension method that deletes all rows from the query without
/// retrieving entities.
/// </summary>
/// <typeparam name="T">The type of elements of the query.</typeparam>
/// <param name="query">The query to delete rows from without retrieving entities.</param>
/// <param name="batchDeleteBuilder">The batch builder action to change default configuration.</param>
/// <returns>The number of rows affected.</returns>
public static int Delete <T>(this IQueryable <T> query, Action <BatchDelete> batchDeleteBuilder) where T : class
{
var batchDelete = new BatchDelete();
if (BatchDeleteManager.BatchDeleteBuilder != null)
{
BatchDeleteManager.BatchDeleteBuilder(batchDelete);
}
if (batchDeleteBuilder != null)
{
batchDeleteBuilder(batchDelete);
}
return(batchDelete.Execute(query));
}
/// <summary>Executes the batch delete operation.</summary>
/// <typeparam name="T">The type of elements of the query.</typeparam>
/// <param name="query">The query used to execute the batch operation.</param>
/// <returns>The number of rows affected.</returns>
public int Execute <T>(IQueryable <T> query) where T : class
{
// FIX query with visitor
{
var visitor = new BatchDeleteVisitor();
visitor.Visit(query.Expression);
if (visitor.HasOrderBy)
{
query = query.Take(int.MaxValue);
}
if (visitor.HasTake || visitor.HasSkip)
{
BatchSize = 0;
}
}
string expression = query.Expression.ToString();
if (Regex.IsMatch(expression, @"\.Where\(\w+ => False\)"))
{
return(0);
}
// GET model and info
#if EF5 || EF6
var model = query.GetDbContext().GetModel();
var entity = model.Entity <T>();
var keys = entity.Info.Key.PropertyRefs;
// SELECT keys names
var queryKeys = query.SelectByName(keys.Select(x => x.Name).ToList());
var innerObjectQuery = queryKeys.GetObjectQuery();
// CREATE command
var command = CreateCommand(innerObjectQuery, entity);
// EXECUTE
var ownConnection = false;
try
{
if (innerObjectQuery.Context.Connection.State != ConnectionState.Open)
{
ownConnection = true;
innerObjectQuery.Context.Connection.Open();
}
if (command.GetType().Name == "NpgsqlCommand")
{
command.CommandText = command.CommandText.Replace("[", "\"").Replace("]", "\"");
int totalRowAffecteds = command.ExecuteNonQuery();
return(totalRowAffecteds);
}
else if (command.Connection.GetType().Name.Contains("MySql"))
{
int totalRowAffecteds = command.ExecuteNonQuery();
return(totalRowAffecteds);
}
else if (command.Connection.GetType().Name.Contains("Oracle"))
{
int totalRowAffecteds = command.ExecuteNonQuery();
return(totalRowAffecteds);
}
else if (command.GetType().Name == "SqlCeCommand")
{
int totalRowAffecteds = command.ExecuteNonQuery();
return(totalRowAffecteds);
}
else
{
if (Executing != null)
{
Executing(command);
}
var rowAffecteds = (int)command.ExecuteScalar();
return(rowAffecteds);
}
}
finally
{
if (ownConnection && innerObjectQuery.Context.Connection.State != ConnectionState.Closed)
{
innerObjectQuery.Context.Connection.Close();
}
}
#elif EFCORE
if (BatchDeleteManager.InMemoryDbContextFactory != null && query.IsInMemoryQueryContext())
{
var context = BatchDeleteManager.InMemoryDbContextFactory();
var list = query.ToList();
context.RemoveRange(list);
context.SaveChanges();
return(list.Count);
}
var dbContext = query.GetDbContext();
var entity = dbContext.Model.FindEntityType(typeof(T));
var keys = entity.GetKeys().ToList()[0].Properties;
var queryKeys = query.SelectByName(keys.Select(x => x.Name).ToList());
// CREATE command
var command = CreateCommand(queryKeys, entity);
// EXECUTE
var ownConnection = false;
try
{
if (dbContext.Database.GetDbConnection().State != ConnectionState.Open)
{
ownConnection = true;
dbContext.Database.OpenConnection();
}
if (command.GetType().Name == "NpgsqlCommand")
{
command.CommandText = command.CommandText.Replace("[", "\"").Replace("]", "\"");
int totalRowAffecteds = command.ExecuteNonQuery();
return(totalRowAffecteds);
}
else if (command.Connection.GetType().Name.Contains("MySql"))
{
int totalRowAffecteds = command.ExecuteNonQuery();
return(totalRowAffecteds);
}
else
{
if (Executing != null)
{
Executing(command);
}
}
var rowAffecteds = (int)command.ExecuteScalar();
return(rowAffecteds);
}
finally
{
if (ownConnection && dbContext.Database.GetDbConnection().State != ConnectionState.Closed)
{
dbContext.Database.CloseConnection();
}
}
#endif
}
/// <summary>Executes the batch delete operation.</summary>
/// <typeparam name="T">The type of elements of the query.</typeparam>
/// <param name="query">The query used to execute the batch operation.</param>
/// <returns>The number of rows affected.</returns>
public int Execute <T>(IQueryable <T> query) where T : class
{
// FIX query with visitor
{
var visitor = new BatchDeleteVisitor();
visitor.Visit(query.Expression);
if (visitor.HasOrderBy)
{
query = query.Take(int.MaxValue);
}
if (visitor.HasTake || visitor.HasSkip)
{
BatchSize = 0;
}
}
string expression = query.Expression.ToString();
if (Regex.IsMatch(expression, @"\.Where\(\w+ => False\)"))
{
return(0);
}
// GET model and info
#if EF5 || EF6
var dbContext = query.GetDbContext();
#if EF6
if (dbContext.IsInMemoryEffortQueryContext())
{
var context = query.GetDbContext();
var list = query.ToList();
context.Set <T>().RemoveRange(list);
context.SaveChanges();
return(list.Count);
}
#endif
var model = dbContext.GetModel();
//get our table definitions for the requested type
IEnumerable <TableDefinition> lsTableDefinitions = model.GetTableDefinitions <T>();
//reverse the order so the most base table is the first
TableDefinition tdBaseTableDefinition = lsTableDefinitions.OrderByDescending(i => i.Order).FirstOrDefault();
//stop if there's nothing
if (tdBaseTableDefinition == null)
{
throw new Exception("Could not find Entity meta data");
}
int rowsAffected = 0;
// SELECT keys names
var queryKeys = query.SelectByName(tdBaseTableDefinition.Keys.Select(x => x.ColumnName).ToList());
var innerObjectQuery = queryKeys.GetObjectQuery();
// CREATE command
var command = CreateCommand(innerObjectQuery, tdBaseTableDefinition);
// WHERE 1 = 0
if (command == null)
{
return(0);
}
// EXECUTE
var ownConnection = false;
try
{
if (innerObjectQuery.Context.Connection.State != ConnectionState.Open)
{
ownConnection = true;
innerObjectQuery.Context.Connection.Open();
}
Executing?.Invoke(command);
#if EF5
if (command.GetType().Name == "NpgsqlCommand")
{
command.CommandText = command.CommandText.Replace("[", "\"").Replace("]", "\"");
rowsAffected += command.ExecuteNonQuery();
}
else if (command.Connection.GetType().Name.Contains("MySql"))
{
rowsAffected += command.ExecuteNonQuery();
}
else if (command.Connection.GetType().Name.Contains("Oracle"))
{
rowsAffected += command.ExecuteNonQuery();
}
else if (command.GetType().Name == "SqlCeCommand")
{
rowsAffected += command.ExecuteNonQuery();
}
else
{
rowsAffected += (int)command.ExecuteScalar();
}
#elif EF6
var interceptionContext = new DbCommandInterceptionContext(dbContext.GetObjectContext().GetInterceptionContext());
if (command.GetType().Name == "NpgsqlCommand")
{
command.CommandText = command.CommandText.Replace("[", "\"").Replace("]", "\"");
rowsAffected += DbInterception.Dispatch.Command.NonQuery(command, interceptionContext);
}
else if (command.Connection.GetType().Name.Contains("MySql"))
{
rowsAffected += DbInterception.Dispatch.Command.NonQuery(command, interceptionContext);
}
else if (command.Connection.GetType().Name.Contains("Oracle") || command.Connection.GetType().Name.Contains("SQLite"))
{
rowsAffected += DbInterception.Dispatch.Command.NonQuery(command, interceptionContext);
}
else if (command.GetType().Name == "SqlCeCommand")
{
rowsAffected += DbInterception.Dispatch.Command.NonQuery(command, interceptionContext);
}
else
{
rowsAffected += (int)DbInterception.Dispatch.Command.Scalar(command, interceptionContext);
}
#endif
}
finally
{
if (ownConnection && innerObjectQuery.Context.Connection.State != ConnectionState.Closed)
{
innerObjectQuery.Context.Connection.Close();
}
}
return(rowsAffected);
#elif EFCORE
if (BatchDeleteManager.InMemoryDbContextFactory != null && query.IsInMemoryQueryContext())
{
var context = BatchDeleteManager.InMemoryDbContextFactory();
var list = query.ToList();
context.RemoveRange(list);
context.SaveChanges();
return(list.Count);
}
var dbContext = query.GetDbContext();
var entity = dbContext.Model.FindEntityType(typeof(T));
//go all the way to our base type
while (entity.BaseType != null)
{
entity = entity.BaseType;
}
//get our keys
var keys = entity.GetKeys().SelectMany(i => i.Properties);
var queryKeys = query.SelectByName(keys.Select(x => x.Name).ToList());
// CREATE command
var command = CreateCommand(queryKeys, entity);
// EXECUTE
var ownConnection = false;
int intRowsAffected = 0;
try
{
if (dbContext.Database.GetDbConnection().State != ConnectionState.Open)
{
ownConnection = true;
dbContext.Database.OpenConnection();
}
Executing?.Invoke(command);
if (command.GetType().Name == "NpgsqlCommand")
{
command.CommandText = command.CommandText.Replace("[", "\"").Replace("]", "\"");
intRowsAffected = command.ExecuteNonQuery();
}
else if (command.Connection.GetType().Name.Contains("MySql"))
{
intRowsAffected = command.ExecuteNonQuery();
}
else if (command.GetType().Name.Contains("Sqlite"))
{
intRowsAffected = command.ExecuteNonQuery();
}
else
{
intRowsAffected = (int)command.ExecuteScalar();
}
}
finally
{
if (ownConnection && dbContext.Database.GetDbConnection().State != ConnectionState.Closed)
{
dbContext.Database.CloseConnection();
}
}
return(intRowsAffected);
#endif
}
/// <summary>Executes the batch delete operation.</summary>
/// <typeparam name="T">The type of elements of the query.</typeparam>
/// <param name="query">The query used to execute the batch operation.</param>
/// <returns>The number of rows affected.</returns>
public int Execute <T>(IQueryable <T> query) where T : class
{
if (query.Expression.ToString().Contains(".Where(x => False)"))
{
return(0);
}
// GET model and info
#if EF5 || EF6
var model = query.GetDbContext().GetModel();
var entity = model.Entity <T>();
var keys = entity.Info.Key.PropertyRefs;
// SELECT keys names
var queryKeys = query.SelectByName(keys.Select(x => x.Name).ToList());
var innerObjectQuery = queryKeys.GetObjectQuery();
// CREATE command
var command = CreateCommand(innerObjectQuery, entity);
// EXECUTE
var ownConnection = false;
try
{
if (innerObjectQuery.Context.Connection.State != ConnectionState.Open)
{
ownConnection = true;
innerObjectQuery.Context.Connection.Open();
}
if (command.GetType().Name == "NpgsqlCommand")
{
command.CommandText = command.CommandText.Replace("[", "\"").Replace("]", "\"");
int totalRowAffecteds = 0;
int rowAffecteds = 0;
do
{
if (rowAffecteds > 0 && BatchDelayInterval != 0)
{
Thread.Sleep(BatchDelayInterval);
}
rowAffecteds = (int)command.ExecuteNonQuery();
totalRowAffecteds += rowAffecteds;
} while (rowAffecteds > 0);
return(totalRowAffecteds);
}
else if (command.Connection.GetType().Name.Contains("MySql"))
{
int totalRowAffecteds = 0;
int rowAffecteds = 0;
do
{
if (rowAffecteds > 0 && BatchDelayInterval != 0)
{
Thread.Sleep(BatchDelayInterval);
}
rowAffecteds = (int)command.ExecuteNonQuery();
totalRowAffecteds += rowAffecteds;
} while (rowAffecteds > 0);
return(totalRowAffecteds);
}
else
{
if (Executing != null)
{
Executing(command);
}
var rowAffecteds = (int)command.ExecuteScalar();
return(rowAffecteds);
}
}
finally
{
if (ownConnection && innerObjectQuery.Context.Connection.State != ConnectionState.Closed)
{
innerObjectQuery.Context.Connection.Close();
}
}
#elif EFCORE
if (BatchDeleteManager.InMemoryDbContextFactory != null && query.IsInMemoryQueryContext())
{
var context = BatchDeleteManager.InMemoryDbContextFactory();
var list = query.ToList();
context.RemoveRange(list);
context.SaveChanges();
return(list.Count);
}
var dbContext = query.GetDbContext();
var entity = dbContext.Model.FindEntityType(typeof(T));
var keys = entity.GetKeys().ToList()[0].Properties;
var queryKeys = query.SelectByName(keys.Select(x => x.Name).ToList());
// CREATE command
var command = CreateCommand(queryKeys, entity);
// EXECUTE
var ownConnection = false;
try
{
if (dbContext.Database.GetDbConnection().State != ConnectionState.Open)
{
ownConnection = true;
dbContext.Database.OpenConnection();
}
if (Executing != null)
{
Executing(command);
}
var rowAffecteds = (int)command.ExecuteScalar();
return(rowAffecteds);
}
finally
{
if (ownConnection && dbContext.Database.GetDbConnection().State != ConnectionState.Closed)
{
dbContext.Database.CloseConnection();
}
}
#endif
}
