Search code examples
c#sqlormsimple.data

Delete From Table Where Column "In" range - Simple.Data

I'm using Simple.Data as my Micro-ORM and I want to achieve something which may or may not be possible.. I want to do a delete on a table where one of the columns matches a list of variables, so effectively the SQL would be something like:

DELETE FROM [Albums] WHERE Genre IN ("Rock", "Pop")

Is this possible? I have tried a few things such as:

db.Albums.DeleteByGenre(new[] { "Rock", "Pop" });
db.Albums.DeleteAll(db.Albums.Genre == new[] { "Rock", "Pop" });

var genres = new List<string> { "Rock", "Pop" }
db.Albums.DeleteAll(db.Albums.Genre == genres);

Which do not run, as I can see no generated queries when a run a SQL profile. Beginning to think the only option is to loop through the collection of genres calling DeleteBy one-by-one?

EDIT:

The issue I was having was not a Simple.Data issue. The above will work, I used:

var genreList = new List<string> { "Rock", "Pop" };
db.Albums.DeleteByGenre(genreList);

Altenatively you could use:

db.Albums.DeleteAll(db.Albums.Genre == genreList);

I prefer the first option personally :)

Solution

  • I haven't tested it, but you could use the Array.contains method like this:

    string[] genresToDelete = new[] { "Rock", "Pop" };
db.Albums.DeleteAll(genresToDelete.Contains(db.Albums.Genre));

    I'm not 100% sure how intelligent the statement would be created, but I strongly recommend you to define the array before the statement and just use it (instead of maybe defining it for EVERY record of the table). Plus, I think it's more readable that way :)