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

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?

5
задан ssmith 24 January 2011 в 21:40
поделиться