Here is my case.

3 Tables Company CompanyEmployee (association table) Employee

So company-employee is a m:n relationship through the association table, with all the foreign keys you would expect in that case.

I need to delete all employees that are related to no company. In other words, delete all rows of table Employee for which there are no rows of CompanyEmployee.

As the Employee table has millions of rows, I need a fast solution, using Adapter.DeleteEntitiesDirectly.

What is the best way to write the predicate to pass to DeleteEntitiesDirectly?

Thanks in advance Silver

WHERE EmployeeId NOT IN (SELECT EmployeeId FROM CompanyEmployee )

Use a FieldComapreSetpredicate for the NOT IN

Would you mind showing me the syntax to use to define a FieldCompareSetPredicate in my case?

I found filter.Add(new FieldCompareSetPredicate( CustomerFields.CustomerID, OrderFields.CustomerID, SetOperator.In, (OrderFields.EmployeeID == 2))); in the documentation, but I'm not sure how it applies in my case.

filter.Add(new FieldCompareSetPredicate(
    EmployeeFields.Id, null, CompanyEmployeeFields.EmployeeId, null 
    SetOperator.In, null, true));

use the above filter with the DeleteEntitiesdirectly() method.

Walaa,

I tried your predicate and here is the query that LLBLGen generated for it

DELETE FROM [EMPLOYEE] WHERE ( ( NOT [EMPLOYEE].[id] IN (SELECT [COMPANYEMPLOYEE].[contract_id] AS [ContractId] FROM [COMPANYEMPLOYEE] )))

That doesn't seem to delete anything from the DB.

Why contract_id was selected in the sub Select? Are you sure you have used CompanyEmployeeFields.EmployeeId?

Sorry, contractid is from my real case, I missed it when doing the replace to match the name I used for the example case. It's the correct field name for the data I'm working on.

I believe I have found the source of my problem. CompanyEmployee contains some rows that have EmployeeId=NULL (for reasons outside the scope of this example, and I have no control over that).

As a result, the NOT IN clause doesn't work because one of the value in the subquery is NULL. That makes sense.

I need a way to make LLBL emit

DELETE FROM [EMPLOYEE] WHERE ( ( NOT [EMPLOYEE].[id] IN (SELECT [COMPANYEMPLOYEE].[EmployeeId] AS [EmployeeId] FROM [COMPANYEMPLOYEE] WHERE EmployeeId IS NOT NULL )))

How can I get this result using the fieldcomparesetpredicate, please?

When you build the inner PredicateExpression for the FieldCompareSetPredicate you can use FieldCompareNullPredicate and then pass it to the FieldCompareSetPredicate's constructor:

// C#
bucket.PredicateExpression.Add(new FieldCompareNullPredicate(OrderFields.OrderDate, null, true));
    
// which is equal to:
bucket.PredicateExpression.Add((OrderFields.OrderDate!=System.DBNull.Value));

If you have problems with that please post the code you have so far.

Seems to be working fine with your suggestion. Thanks a lot Daelmo, I had missed that there was an overload to the constructor of FieldCompareSetPredicate that accepted another Predicate as a subfilter of the set.