Does the linq provider support max used with group by?

Hi lads,

I may be having a blond moment but this query

var query = (
from bi in uow.BillingItems
join b in uow.Bills on bi.Id.BillId equals b.Id
join j in uow.Jobs on b.JobId equals j.Id
where j.JobStatusId != (int)JobStatusEnum.Cancelled
    && billingTypeIdsToCheck.Contains(b.BillingTypeId)
    && assetIdsToCheck.Contains(bi.AssetId)
    && b.InvoicingDueDate < DateTime.Now 
group b by bi.Id.AssetId into g
select new
{
    AssetId = g.Key,
    LastBillDate = g.Max(x => x.InvoicingDueDate)
});

Produces this sql which looks nice apart from the missing max/InvoicingDueDate

SELECT g1.AssetId
FROM
(
    SELECT [t0].[AssetId] AS [AssetId]
    FROM [BillingItem] [t0]
    INNER JOIN [Bill] [t1]
    ON [t0].[BillId] = [t1].[BillId]
    INNER JOIN [Job] [t2]
    ON [t1].[JobId] = [t2].[JobId]
    WHERE ((([t2].[JobStatusId] <> 5 AND [t1].[BillingTypeId] IN (3, 2, 4)) AND [t0].[AssetId] IN (22291)) AND [t1].[InvoicingDueDate] < '11/03/2014 6:45:09 p.m.')
    GROUP BY [t0].[AssetId]
) [g1]

Lightspeed version/nightly is LightSpeed50Professional-20140224

Note: BillingItem table's key is composite key made of AssetId and BillId

Any ideas? Will try make a standalone repro tomorrow.

Any aggregate function will be handled client side, our LINQ provider translates a grouping query into several queries, one for the underlying group by query to fetch the grouping keys and then one per key to fetch the underlying entities in that set. Subsequent operations are handled client side as they need to be performed over sets of entities.

Unfortunately combining grouping and joins is not supported (see: http://www.mindscapehq.com/documentation/lightspeed/Appendices/LINQ-Support-Limitations under the Joining, Grouping and Combining section) so you may need to look at some alternative to achieve the above query (e.g. use of a stored proc or view if thats possible).

Jeremy

Fair enough.