[SOLVED] LLBL: Delete Where NOT IN

LLBL: Delete Where NOT IN

I'm trying to perform the following query in LLBL and I'm not having much luck.

DELETE FROM dbo.MyTable WHERE MyTableId NOT IN ('39', '43', '44')

Essentially, I'm up to this point:

private static void Delete(MyTableCollection newRecs)
{
    PredicateExpression filter = new PredicateExpression();
    MyTableCollection allRecords = new MyTableCollection();

    filter.Add(new FieldCompareSetPredicate(
                   MyTableFields.MyTableId,
                   MyTableFields.MyTableId,
                   SetOperator.In, null, null, string.Empty, 0, null, true));

    allRecords.DeleteMulti(filter);
}

I'm not sure if the above code is correct, but I'm not understanding how I can supply newRecs as the Collection of records to use in my IN clause. Am I even close?

Any help would be greatly appreciated.

NOTE: I realize that I used static ID's in my SQL example, but I'm really attempting to use the ID's that are stored in the newRecs parameter.

Solution

In order to get the "not in" to work, you've got to pass some kind of array. Try something like this:

List<int> idsNotToDelete = new List<int>();
foreach (MyTableEntity ent in newRecs)
{
     idsNotToDelete.Add(ent.MyTableId);
}
PredicateExpression filter = new PredicateExpression(MyTableFields.MyTableId != idsNotToDelete)
MyTableCollection allRecords = new MyTableCollection();
allRecords.DeleteMulti(filter);