CODEWITHSUNDEEP
c#.netdapper
Casey Crookston
Casey Crookston

Reputation: 13965

Delete a List<T> using Dapper

Using Dapper or Dapper.SimpleCRUD, How might I delete a List from a table. Something like:

    public static void DeleteList<T>(List<T> listToDelete)
    {
        using (var connection = OpenConnection())
        {
            connection.Delete<T>(listToDelete);
        }
    }

But when I try that, I get...

The member of type DataModel.MyTable cannot be used as a parameter value

Is the only option to pass in a WHERE clause?

Upvotes: 7

Views: 17606

Answers (5)

Wouter Vanherck
Wouter Vanherck

Reputation: 2337

When using the Dapper.Contrib.Extensions package, one could use db.Delete(user); as long as you're trying to delete by the Primary Key (indicated by the [Key] annotation on the User object). As soon as you want to delete by anything else, you're better off using db.Execute(query, params);.

Dapper.Contrib.Extension Delete documentation

Upvotes: 1

Wajdi Gharsalli
Wajdi Gharsalli

Reputation: 368

Easy way: 

public void BulkDelete(IEnumerable<int> idList)
    {
        using (var connection = GetNewConnection())
        {
            connection.Execute("Delete FROM MY_TABLE WHERE Id in @idList", new { idList });
        }
    }

Upvotes: 8

mrogunlana
mrogunlana

Reputation: 847

Not sure why the above answer was selected. But Dapper out of the box does not support list functions. Instead of recreating the wheel in my answer see: Delete rows from mysql server from a list of ID's C#... just my 2c.

Upvotes: 1

Win
Win

Reputation: 62301

Dapper doesn't know your entity; it is not like Entity Framework. You need to execute a SQL Command or a Store Procedure by yourself.

public static void DeleteList(List<int> idToDelete)
{
    using (IDbConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        foreach (int id in idToDelete)
        {
            conn.Execute(@"DELETE FROM [User] WHERE Id = @Id", new {Id = id});
        }
    }
}

Or Execute a Command multiple times

public static void DeleteList(List<int> idToDelete)
{
    using (IDbConnection conn = new SqlConnection(connString))
    {
        conn.Open();
        conn.Execute(@"DELETE FROM [User] WHERE Id = @Id",
            idToDelete.Select(x => new { Id = x }).ToArray());
    }
}

Upvotes: 9

Maciej Jureczko
Maciej Jureczko

Reputation: 1598

I don't see any Dapper method for what you want to achieve. The only options are two single deletes and two multiple deletes.

public static int Delete<T>(this IDbConnection connection, int Id)

public static int Delete<T>(this IDbConnection connection, T entityToDelete)

public static int DeleteList<T>(this IDbConnection connection, object whereConditions, IDbTransaction transaction = null, int? commandTimeout = null)

public static int DeleteList<T>(this IDbConnection connection, string conditions, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null)

Both multiple deletes require you to pass conditions or a where clause. Examples:

connection.DeleteList<User>(new { Age = 10 });
connection.DeleteList<User>("Where age > 20");
connection.DeleteList<User>("Where age > @Age", new {Age = 20});

Upvotes: 3

Related Questions