I'm trying to delete a set of orphaned records, using llblgenpro using Adapter. Here is the simple SQL for SQL Server 2000

delete dbo.child c where c.childid not in ( select childid from dbo.parents )

This will delete all children that don't have parent.

ChildEntity ParentEntity are the object names.

Thank you for the help.

Here is what I came up with. It's working, but I don't like it.

public static int RemoveOrphanedChildren(IDataAccessAdapter adapter)
{
    IRelationPredicateBucket DeleteFilter = new RelationPredicateBucket();

    FieldCompareSetPredicate CompareSet = new FieldCompareSetPredicate(
    EntityFieldFactory.Create(ChildFieldIndex.ParentId), null,
    EntityFieldFactory.Create(ParentChildFieldIndex.ParentId), null, 
    SetOperator.In, null);
    CompareSet.Negate = true;

    DeleteFilter.PredicateExpression.Add(CompareSet);

    EntityCollection children = new EntityCollection(new ChildEntityFactory());
    adapter.FetchEntityCollection(children, DeleteFilter );
    return adapter.DeleteEntityCollection(children);
}

I don't like this solution because the ChildEntity has a photo in it, that can be rather large. I'm having to send quite a bit across the wire, just to get the children collection, then to turn around a delete it.

How can I turn this into a straight delete, rather then select first, then delete?

Thanks for the feedback.

Did you look at this section, Deleting one or more entities from the persistent storage, in the Using The Entity Collection Topic?

Exactly, check that section for 'DeleteEntitiesDirectly', a method which lets you delete entities directly from the database without first loading them.

Just to maybe help soemone else out in the future, This worked perfect, I simply replaced.

    adapter.FetchEntityCollection(children, DeleteFilter );
    return adapter.DeleteEntityCollection(children);

with

    return adapter.DeleteEntitiesDirectly("ChildEntity", DeleteFilter);

Thanks for the tip.