I’ve got the following model:

An Order has zero or more Order Lines. Each Order Line may have one Area

For a given order I want to retrieve the area that appears most on its order lines. I currently have a UDF that does this based on the following SQL:

SELECT TOP (1) @Result = [Area].[Area]
FROM         [Order] INNER JOIN
             [OrderLine] ON [Order].[ OrderId] = [OrderLine].[OrderId] INNER JOIN
                      [Area] ON [OrderLine].[AreaId] = [Area].[AreaId]
WHERE    ([Order].[ OrderId] = @OrderId)
GROUP BY [Area].[Area]
ORDER BY COUNT([Area].[Area]) DESC

How can I convert this to get the AreaEntity in code using my (self servicing) collections / entities?

I suspect I might need to do a GetScalar() on the OrderCollection to get the AreaId, but I’m not sure how to construct the arguments to .GetScalar() to match my sql statement.

Hi Vic,

I would use FieldCompareSetPredicate. This way you can custom your filter, with the order clause and retrieve the top Area in one shot :

// setup the relations
RelationCollection subQueryRelations = new RelationCollection();
subQueryRelations.Add(OrderEntity.Relations.OrderLineEntityUsingOrderId);
subQueryRelations.Add(OrderLineEntity.Relations.AreaEntityUsingAreaId);

// setup the groupBy
GroupByCollection grouper = new GroupByCollection();
grouper.Add(AreaFields.AreaId);

// setup the sorter
SortExpression subQuerySorter = new SortExpression();
SortClause countDescendingSort = new SortClause(AreaFields.AreaId, SortOperator.Descending);
countDescendingSort.FieldToSortCore.AggregateFunctionToApply = AggregateFunction.Count;
countDescendingSort.EmitAliasForExpressionAggregateField = false;
subQuerySorter.Add(countDescendingSort);            

// the query that returns the Id Area
FieldCompareSetPredicate subQuery = new FieldCompareSetPredicate(
    AreaFields.AreaId, AreaFields.AreaId,
    SetOperator.In, null, subQueryRelations, "", 1, 
    subQuerySorter, false, grouper);

// the whole filter
IPredicateExpression filter = new PredicateExpression(subQuery);

// fetch
AreaCollection areas = new AreaCollection();
products.GetMulti(filter);

// get your area that appears the most
AreaEntity areaTheMost = areas[0];

Perfect, cheers!!