Adding a dynamic GroupBy expression to an IQueryable

Question

I want to pass a dynamic GroupBy expression into an IQueryable. I'm already doing something similar with a Where clause that is working OK.

The purpose of the group by is then to allow my to extract just the most recent version of each record in each respective group. For example:

object MyMethod(Expression<Func<MyObj,int>> whereExpr = null, 
              Expression<Func<MyObj,int>> groupByExpr = null)
{
    var query = DbSet.Where(x => x.IsArchived == false);


    if (whereExpr != null) {
        query = query.Where(whereExpr); // this is working
    }

    if (groupByExpr != null) {
        query = query.GroupBy(groupByExpr).Select(g => g.OrderByDescending(x => x.CreatedDate).FirstOrDefault()).AsQueryable(); // this is throwing a client side error
    }
    
    return new GridDevExtremeDataSource<TEntity>(query);
}

Then called with something like

var obj = MyMethod(whereExpr: x => x.Id > 100, groupByExpr: x => x.CategoryId)

This is throwing a client side error:

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
---> System.InvalidOperationException: Nullable object must have a value.

Try replace `.FirstOrDefault()` with `First()` and remove `AsQueryable` — Svyatoslav Danyliv, Jul 27 '23 at 18:27
@SvyatoslavDanyliv thanks for taking time to respond. Neither of those changes have made a difference. I tried both together and individually. — Mark Cooper, Jul 27 '23 at 19:08

Svyatoslav Danyliv · Accepted Answer · 2023-07-28T15:13:25.027

1

Actually you are trying to get latest item from the group. Looks like EF Core, at least up to version 7, has limitation in such queries.

I have prepared another answer for similar problem and implemented custom DistinctBy extension implementation which mimic what EF Core should do for retrieving last record of grouped items.

So, you problematic line with this function should looks like:

query = query.DistinctBy(groupByExpr, x => x.CreatedDate);

edited Jul 28 '23 at 15:13

answered Jul 28 '23 at 08:39

Svyatoslav Danyliv

21,911
3
16
32

`query = query.DistinctBy(groupByExpr, x => x.CreatedDate);` doesn't compile. How should the 2nd parameter be passed? Intellisense wants an `IEqualityComparer` - how will that get the records in the correct order to pick the latest one only by `CreatedDate` – Mark Cooper Jul 28 '23 at 12:03
You have used not my extension method but standard .NET core `DistinctBy`. – Svyatoslav Danyliv Jul 28 '23 at 12:12
Rename it to avoid ambiguous usage. – Svyatoslav Danyliv Jul 28 '23 at 12:19
I didn't spot your link. Thank you, that has worked great! – Mark Cooper Jul 28 '23 at 13:41

Adding a dynamic GroupBy expression to an IQueryable

1 Answers1

Linked