FieldCompareSetPredicate is more for situations like:
WHERE OrderId IN (SELECT ...)
For what you want is more appropriate a ScalarQueryExpression and use it to filter the related collection (prefetchPath).
Also, to fetch the related collection you should use PrefetchPaths. You can filter the prefetchPath to include only the MAX related entity.
Example: I want to fetch all customers, and for each one I want to fetch the latest order, where "latest order" is max(orderId). The code would look like this:
// define the scalar field and filter
var orderIdMax = new EntityField2("MaxOrderFromCust",
new ScalarQueryExpression(OrderFields.OrderId.SetObjectAlias("O2")
.SetAggregateFunction(AggregateFunction.Max),
(OrderFields.CustomerId.SetObjectAlias("O2") == OrderFields.CustomerId)));
// path
var path = new PrefetchPath2((int)EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathOrders, 0, new PredicateExpression(OrderFields.OrderId == orderIdMax));
// fetch
var customers = new EntityCollection<CustomerEntity>();
using (var adapter = new DataAccessAdapter())
{
adapter.FetchEntityCollection(customers, null, path);
}
The generated SQL, due to the PrefetchPath, is done in two queries:
SELECT [Northwind].[dbo].[Customers].[Address],
[Northwind].[dbo].[Customers].[City],
...
FROM [Northwind].[dbo].[Customers]
SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId],
[Northwind].[dbo].[Orders].[EmployeeID] AS [EmployeeId],
[Northwind].[dbo].[Orders].[Freight],
...
FROM [Northwind].[dbo].[Orders]
WHERE ([Northwind].[dbo].[Orders].[CustomerID] IN
(SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId]
FROM [Northwind].[dbo].[Customers])
AND (([Northwind].[dbo].[Orders].[OrderID] =
(SELECT MAX([O2].[OrderID]) AS [OrderId]
FROM [Northwind].[dbo].[Orders] [O2]
WHERE ([O2].[CustomerID] = [Northwind].[dbo].[Orders].[CustomerID])))))
