Delete multiple objects with a single query (or in transaction)

Question

I'm using Dapper with Dapper-Extensions. I'm currently deleting all the objects one-by-one:

dbConnection.Delete<MyObj>(data);

This is bad not only for performance, but also because if a delete fails I would like to rollback the entire operation. Is there a way to perform a "massive" delete, for example passing a list of objects instead of data?

Answer 1

You may pass IPredicate to delete multiple records based on condition (WHERE clause) in one go.

If you simply pass empty IPredicate, all records from the table will be deleted.

Following function handles both the cases:

protected void DeleteBy(IPredicate where)
{//If 'where' is null, this method will delete all rows from the table.
    if(where == null)
        where = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };//Send empty predicateGroup to delete all records.

    var result = connection.Delete<TPoco>(predicate, ......);
}

In above code, TPoco is your POCO type which is mapped to database table you are talking about.

You can build the predicate something like below:

var predicateGroup = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };
if(!string.IsNullOrEmpty(filterValue))
    predicateGroup.Predicates.Add(Predicates.Field<MyPoco>(x => x.MyProperty, Operator.Eq, PredicateGroup));

Transaction is different thing. You can put all your current code in transaction. You can put my code in transaction as well. With my code, transaction does not make much difference though; although it is recommended to always use transactions.

About passing list of objects, I do not see any way. Following are two extension methods of Dapper Extensions for deleting the record:

public static bool Delete<T>(this IDbConnection connection, object predicate, IDbTransaction transaction = null, int? commandTimeout = default(int?)) where T : class;
public static bool Delete<T>(this IDbConnection connection, T entity, IDbTransaction transaction = null, int? commandTimeout = default(int?)) where T : class;

None of it accepts list of objects. One accept predicate and other accepts single object.