How do I filter out entities that are NOT in a many-to-many relationship using LLBLGen Pro?

Question

I have some code that is able to retrieve all entities that contain a particular relationship, like so:

        var bucket = new RelationPredicateBucket();
        bucket.Relations.Add(MessageEntity.Relations.MessageTemplateReferenceEntityUsingMessageId);
        var messageEntities = new EntityCollection<MessageEntity>();
        using (var myAdapter = PersistenceLayer.GetDataAccessAdapter())
        {
            myAdapter.FetchEntityCollection(messageEntities, bucket);
        }

Works great. Now, I want to get all of the entities in the Message table that do NOT have a corresponding row in the MessageTemplate xref table. That is, where the MessageEntity.Relations.MessageTemplateReferenceEntityUsingMessageId is null/false/nonexistent.

Here is a phenomenal hack that one of my teammates did to make this work some years ago (the above code's entity collection is in templatedMessages below):

bucket.PredicateExpression.Add(MessageFields.Id
 != templatedMessages.Select(m =>
 m.Id).ToArray());

This actually worked, until recently when the number of templatedMessages in the table grew beyond 2100, and the method started throwing these exceptions:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

Obviously, passing SQL a complete list of IDs that it should avoid is not terribly efficient. What's the best way to do this in LLBLGen? In SQL I would do something like:

SELECT m.* FROM Message m 
WHERE NOT EXISTS (SELECT 1 FROM MessageTemplate mt WHERE mt.MessageID = m.ID)

Can I do this in LLBLGen?

Answer 1

Use a FieldCompareSetPredicate:

http://www.llblgen.com/documentation/2.6/hh_goto.htm#Using%20the%20generated%20code/Adapter/Filtering%20and%20Sorting/gencode_filteringpredicateclasses_adapter.htm#FieldCompareSetPredicate

Updated by ssmith: This set me on the right path - here is the actual code I ended up using to get the desired behavior of all Message rows that are not in the MessageTemplateReference xref table:

bucket.PredicateExpression.Add(
  new FieldCompareSetPredicate(
    MessageFields.Id, 
    null, 
    MessageTemplateReferenceFields.MessageId, 
    null,
    SetOperator.Exist,  
    (MessageFields.Id == MessageTemplateReferenceFields.MessageId), 
    true));

The last boolean negates the Exists.