Geek Answers Handbook

How to filter objects that are NOT many-to-many using LLBLGen Pro?

I have code that can retrieve all entities that contain a specific relationship, for example:

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

It works great. Now I want to get all entities in the message table that DO NOT have the corresponding row in the xref MessageTemplate table. That is where MessageEntity.Relations.MessageTemplateReferenceEntityUsingMessageId is null / false / nonexistent.

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

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

This really worked until recently, when the number of templatedMessages in the table grew beyond 2100, and the method began to throw these exceptions:

(TDS) (RPC). RPC. 2100.

, SQL , , . LLBLGen? SQL - :

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

LLBLGen?

+5
1

FieldCompareSetPredicate:

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

ssmith: - , , , xref MessageTemplateReference:

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

boolean Exists.

+3

More articles:


All Articles