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?