I think Jason suggestions indeed works.
Anyway, you can write subquery relations using Derived Tables and Dynamic Relations:
// -- SUBQUERY AND DYNAMIC RELATION
// first specify the elements in the derived table select (which is a dyn. list)
ResultsetFields dtFields = new ResultsetFields(2);
dtFields.DefineField(OrdersFields.OrderId, 0, "OrderId", AggregateFunction.Max);
dtFields.DefineField(OrdersFields.CustomerId, 1);
// the grouper
GroupByCollection dtGroupBy = new GroupByCollection(dtFields[1]);
// define the deriveTable
DerivedTableDefinition dtDefinition = new DerivedTableDefinition(dtFields, "MaxOrderByCustomer", null, dtGroupBy);
// then specify the relation.
// derivedtable spec, join type, end entity type, alias first element, alias end element, on clause filter
DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner,
EntityType.OrdersEntity, "o",
(new EntityField2(OrdersFieldIndex.OrderId.ToString(), "MaxOrderByCustomer", typeof(int))
== OrdersFields.OrderId.SetObjectAlias("o")));
// -- THE RESULTS AND RELATIONS
// then define the results you want at top
ResultsetFields finalResulsetFields = new ResultsetFields(3);
finalResulsetFields.DefineField(CustomersFields.CustomerId, 0, "CustomerId", "c");
finalResulsetFields.DefineField(OrdersFields.OrderId, 1, "OrderId", "o");
finalResulsetFields.DefineField(OrdersFields.OrderDate, 2, "OrderDate", "o");
// relations
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(CustomersEntity.Relations.OrdersEntityUsingCustomerId, "c", "o", JoinHint.Inner);
filter.Relations.Add(relation);
// -- FETCH RESULTS
DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
adapter.FetchTypedList(finalResulsetFields, results, filter);
}
Above would generate something like:
SELECT
[LPA_c1].[CustomerID] AS [CustomerId],
[LPA_o2].[OrderID] AS [OrderId],
[LPA_o2].[OrderDate]
FROM (
( [Northwind].[dbo].[Customers] [LPA_c1]
INNER JOIN [Northwind].[dbo].[Orders] [LPA_o2]
ON [LPA_c1].[CustomerID] = [LPA_o2].[CustomerID])
INNER JOIN (
SELECT
MAX([Northwind].[dbo].[Orders].[OrderID]) AS [OrderId],
[Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId]
FROM
[Northwind].[dbo].[Orders]
GROUP BY [Northwind].[dbo].[Orders].[CustomerID] ) [LPA_M3]
ON [LPA_M3].[OrderId] = [LPA_o2].[OrderID])
You just need fix some typos on my code and it would work for sure.
