In 2004 we introduced aggregate functions. During development we quickly ran into a problem. Say you do this:
ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(OrderFields.OrderId.SetAggregateFunction(AggregateFunction.Max), 0);
PredicateExpression filter = new PredicateExpression(new FieldCompareValueExpression(fields[0], ComparisonOperator.Equal, 3));
This is a problem, because the aggregate is for the projection (resultsetfields) but will also be applied in the predicate. To avoid that, we only allowed aggregates in predicates in having clauses. This is the reason why you don't see it appear in the scalar. In practice this worked because there's almost no use case for aggregates in predicates, except perhaps this one.
I can reproduce this behavior you're seeing with:
[Test]
public void MaxInScalarAndHavingTest()
{
ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(OrderFields.CustomerId.SetObjectAlias("t1"), 0);
PredicateExpression expFieldQta = new PredicateExpression();
expFieldQta.Add(OrderFields.OrderId.SetObjectAlias("t1").SetAggregateFunction(AggregateFunction.Max) == OrderFields.OrderId.SetObjectAlias("t2"));
IExpression scalarQuery = new ScalarQueryExpression(OrderFields.Freight.SetObjectAlias("t2"), expFieldQta);
fields.DefineField(new EntityField2("QtaDisp", scalarQuery), 1);
RelationPredicateBucket filter = new RelationPredicateBucket(OrderFields.ShippedDate.SetObjectAlias("t1") > DateTime.Parse("1/1/1990"));
GroupByCollection grpBy = new GroupByCollection(fields[0]);
grpBy.HavingClause = new PredicateExpression(new EntityField2("HC", scalarQuery) > 0);
DataTable dt = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(fields, dt, filter, 0, null, false, grpBy);
}
}
which crashes as the MAX aggregate isn't shown in the predicate. It's not shown there because it's part of a scalar query, and it doesn't know it's part of the having clause (the first one will never have the MAX).
So that's the background on this, but how to fix it for your situation? We can't change the library behavior as that would break applications, what we can do is offer you a workaround for this situation so you can construct the query you want. If you look at my query above, you can make the following change: use a DbFunctionCall, to introduce the 'Max' instead of the aggregatefunction:
So instead of:
expFieldQta.Add(OrderFields.OrderId.SetObjectAlias("t1").SetAggregateFunction(AggregateFunction.Max) == OrderFields.OrderId.SetObjectAlias("t2"));
I do:
expFieldQta.Add(new EntityField2("OID", new DbFunctionCall("MAX({0})", new object[] { OrderFields.OrderId.SetObjectAlias("t1") }))
== OrderFields.OrderId.SetObjectAlias("t2"));
which simply wraps the field in MAX(). It's a workaround which is necessary only in situations where you use an aggregate in a predicate which is usually not that common.
We do realize this isn't great, and apologize for this workaround, however we can't change it in the framework as that would break the code which utilizes this feature (as I explained in the beginning of my post).