CODEWITHSUNDEEP
linqsubsonicsubsonic3
Jon
Jon

Reputation: 40062

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

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

Upvotes: 0

Views: 1335

Answers (3)

Jon
Jon

Reputation: 40062

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

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1502156

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."

Upvotes: 2

jao
jao

Reputation: 18620

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)

Upvotes: 0

Related Questions