0

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 :)

MartinM
  • 1,736
  • 5
  • 20
  • 33

1 Answers1

1

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 :)

Stefan Woehrer
  • 680
  • 4
  • 12
  • Thanks for the response. This doesn't appear to be right, though. "genresToDelete(db.Albums.Genre)" will just return a boolean, surely? Meaning I'd be calling db.Albums.DeleteAll(true); - doesn't seem correct. Also .Contains(..) can only take in one string, not a collection - so if there is more than one genre we'll have invalid argument errors. Appreciate the help though! – MartinM Dec 03 '14 at 14:06
  • Updated my answer. Seems I was having another problem – MartinM Dec 03 '14 at 15:18
  • 1
    Hi! Well, I have no experience with Simple.Data. According to the documentation it could work that way though :) Note that I've written genresToDelete.CONTAINS! My thoughts were: For every db.Albums.Genre: Check if the genresToDelete-Array contains that genre. The contains method returns true, if there is an array element equal to the parameter. I did not test it though, so it could be wrong ;) Anyways, I'm glad it works now and you solved the problem. Thanks for the edit and the information about it!!! :-) – Stefan Woehrer Dec 08 '14 at 23:35