Reproduce a "DELETE NOT IN" SQL Statement via LINQ/Subsonic

Question

I want to do something like DELETE FROM TABLE WHERE ID NOT IN (1,2,3) AND PAGEID = 9

I have a List of IDS but that could be changed if needs be. I can't work out how to get a boolean result for the LINQ parser.

Here is what Subsonic expects I think.

db.Delete(content => content.PageID == ID).Execute();

I can't work out how to do the NOT IN statement. I've tried the List.Contains method but something not quite right.

UPDATE: One alternative is to do:

var items = TABLE.Find(x => x.PageID == ID)'
foreach(var item in items)
{
   item.Delete();
}

This hits the database a lot more though

Answer 1

Try .Contains:

db.Delete(content => content.PageID.Contains(<Array containing ID's>).Execute();

(the above is just an example, might need some polishing for your specific situation)

Answer 2

When you say "something not quite right" what exactly do you mean?

I'd expect to write:

List<int> excluded = new List<int> { 1, 2, 3 };
db.Delete(content => !excluded.Contains(content.PageID)).Execute();

Note that you need to call Contains on the array of excluded values, not on your candidate. In other words, instead of saying "item not in collection" you're saying "collection doesn't contain item."

Answer 3

I have found that this works but its not via LINQ

var table = new WebPageContentTable(_db.DataProvider);
var g = new SubSonic.Query.Delete<WebPageContent(_db.DataProvider)
            .From(table)
            .Where(table.ID)
            .NotIn(usedID)
            .Execute();

I have found that this does work and via LINQ - however it hits the database multiple times.

        var f = WebPageContent.Find(x => !usedID.Any(e => e == x.ID));
        if (f.Count > 0)
        {
            var repo = WebPageContent.GetRepo();
            repo.Delete(f);
        }

This I imagine would work in one hit to the database but I get an exception thrown in QueryVisitor::VisitUnary

WebPageContent.Delete(x => !usedID.Any(e => e == x.ID));