SQL Server : delete multiple rows with multiple conditions

Question

I'm trying to delete multiple rows from a SQL Server table, matching on multiple conditions.

Assume the following table:

In my C# code, I have a list with objects containing the following values. Assume this list to be of a decent size.

{Name = 'Bob', Address = '1600 Pennsylvania Avenue, Washington DC'},
{Name = 'Maria', Address = '21B Baker St, London, England.'}

What would be the cleanest way to delete these values? I would rather not run one query per record in my list.

Normally I would do something like this, but in this case, it will empty my entire table (which is not what I want).

DELETE FROM MyTable
WHERE Name IN ('Bob', 'Maria')
  AND Address IN ('1600 Pennsylvania Avenue, Washington DC', '21B Baker St, London, England.')

This works, but it is not something I would consider 'clean' with a decently sized list

DELETE FROM MyTable
WHERE 
   (Name = 'Bob' AND Address = '1600 Pennsylvania Avenue, Washington DC') OR
   (Name = 'Maria' AND Address = '21B Baker St, London, England.')

I'm using Dapper in my C# application, so both Dapper snippets and raw SQL could help me out. As a sucker for clean SQL, I'm really curious how clean this gets.

Thanks a bunch!

Answer 1

There are several things to consider here. When you deploy another variant of a SQL string you're not only generating a new string (allocations in your application), but also query plans on the SQL server. Each permutation has its own plan and cache. This won't likely matter at small scale, but worth keeping in mind.

Often the cleanest way most things is the simplest. To do a small number of items, keeping it simple generally means a loop. Dapper accepts an IEnumerable in the parameters and when it finds an enumerable and it'll execute the same command with the different parameters n times. Though this is round-trips to the server and isn't great for huge numbers, it's very clean. Here's what it'd look like, assuming:

public class Foo
{
    public string Name { get; set; }
    public string Address { get; set; }
}

Then you'd delete multiple like this:

var toDelete = new List<Foo>() {
    new Foo { Name = "Bob", Address = "1600 Pennsylvania Avenue, Washington DC" },
    new Foo { Name = "Maria", Address = "21B Baker St, London, England." }
};

conn.Execute(@"Delete From MyTable Where Name = @Name And Address = @Address", toDelete);