Version 2.6 OCT 09 SelfServicing .NET 4.0 SQL Server 2008 Express

I am developing a ticket system to keep track of messages submitted to an organisation.

The class hierarchy includes a ContactMessage entity that has various header fields associated with the message (MESSAGEID, fromname, email, date-received etc). It also includes a collection of ContactMessageThread (THREADID, MESSAGEID, message_text, date-received) entities. These are used to keep track of individual thread messages.

What I want to do is perform a dynamic search to retrieve a ContactMessageEntity Collection.

Requirements are as follows:

I want to retrieve all ContactMessage entities that have a ContactMessageThread count of 1 (ie it is a new message). In SQL I would Group By MessageID in the ContactMessageThread Table Having Count(*) = 1 and Join on the ContactMessage table to pull back results. I do not know how to approach this will LLBL. I have tried PredicateExpressions, RelationPredicateBucket, GroupByCollection and am pretty much at a loss as to which is the right path.

Needless to say I am not an LLBL power user

Help much appreciated,

Al

Hi Al,

I will show you a similar scenario. Say you want to return all Orders which have only one OrderDetail item. Here is the code:

// collection to fetch
OrderCollection orders = new OrderCollection();

// set the subfilter expression
RelationCollection subFilterRels = new RelationCollection(OrderEntity.Relations.OrderDetailEntityUsingOrderId);         
GroupByCollection subFilterGrouper = new GroupByCollection(OrderDetailFields.OrderId);
subFilterGrouper.HavingClause = new PredicateExpression(
    OrderDetailFields.OrderId.SetAggregateFunction(AggregateFunction.Count) == 1);

FieldCompareSetPredicate inSetFilter = new FieldCompareSetPredicate(
    OrderFields.OrderId, OrderDetailFields.OrderId, SetOperator.In,
    null, subFilterRels, string.Empty, 0, null, false, subFilterGrouper);


// global filter
IPredicateExpression filter = new PredicateExpression(inSetFilter);

// fetch
orders.GetMulti(filter);

Such code generates this sql:

...

FROM 
    [Northwind].[dbo].[Orders]  
    
WHERE 
    ( 
        ( [Northwind].[dbo].[Orders].[OrderID] IN 
            ( SELECT [Northwind].[dbo].[Order Details].[OrderID] AS [OrderId] 
              FROM ( [Northwind].[dbo].[Orders]  
                INNER JOIN [Northwind].[dbo].[Order Details]  
                    ON  [Northwind].[dbo].[Orders].[OrderID]=[Northwind].[dbo].[Order Details].[OrderID]) 
              GROUP BY [Northwind].[dbo].[Order Details].[OrderID] 
              HAVING ( COUNT([Northwind].[dbo].[Order Details].[OrderID]) = @OrderId1))
        )
    )
            
Parameter: @OrderId1 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

.. which is as I think, what you actually need. Please let us know if you need further assistence on this.

Hope helpful

David that was spot on thanks!

Helped to clear up some things in my mind. Maybe you could help me with the following:

I am now trying to show all messages which have updates from the submitter of the message and are not new (ie have more than one message)

Here is the SQL that accomplishes showing the max message in a thread that was inserted my the submitter:

SELECT t2.MESSAGEID FROM tbl_CONTACT_message t1 INNER JOIN tbl_CONTACT_message_thread t2 on t1.MESSAGEID = t2.MESSAGEID where t2.THREADID = (SELECT MAX(THREADID) FROM tbl_CONTACT_message_thread WHERE MESSAGEID = t1.MESSAGEID and t2.isadmin = 0)

To achieve this in LLGBL what I tried was the following:

filter.Add(new FieldCompareSetPredicate(ContactMessageThreadFields.Threadid, null, ContactMessageThreadFields.Threadid.SetAggregateFunction(AggregateFunction.Max), null, SetOperator.Equal, (ContactMessageFields.Messageid == ContactMessageThreadFields.Messageid)));

But I am getting an Index was outside the bounds of the array exception.

The last part of the query would be to exclude the results of the first query you helped me with - ie to ignore new messages (those with one thread).

Cheers for your time!

I don't quite follow your SQL, but I see you are using the FieldCompareSetPredicate overload for Adapter not SelfServicing. Adapter receives IFiledPersistenceInfo for each field. You should use this overload:

filter.Add(new FieldCompareSetPredicate(ContactMessageThreadFields.Threadid,  ContactMessageThreadFields.Threadid.SetAggregateFunction(AggregateFunction.Max),  SetOperator.Equal, (ContactMessageFields.Messageid == ContactMessageThreadFields.Messageid)));

Another thing I noted is that you are comparing the same entity (ContactMessageThread) with different aliases. That means you need to use aliases, at lest for the ambiguous object (the ContactMessageThread inside the filter). So try this:

filter.Add(  new FieldCompareSetPredicate(
     ContactMessageThreadFields.Threadid,       
     ContactMessageThreadFields.Threadid.SetObjectAlias("tin")
          .SetAggregateFunction(AggregateFunction.Max),  
     SetOperator.Equal, 
     (ContactMessageFields.Messageid == ContactMessageThreadFields.Messageid.SetObjectAlias("tin"))));

I used "tin" for the ContactMessageThread fields inside the subquery. Anyway I don't recognize the "Index out of..." exception in this context. Try above code and let me know how it works. If you are still stuck, please post the complete stack trace (if any exception) of the Generated SQL (if it doesn't look like it should).