LINQ Expression GroupBy Select First

Question

I've got a question on LINQ Expression. I need to replicate the following lambda:

var result = processRevisions
   OrderByDescending(pr => pr.CreatedAt)
   GroupBy(pr => pr.IdProcess)
   Select(pr => pr.First());

which result is the latest revision created for every process, in LINQ expression.

I start with this code:

Expression<Func<ProcessRevision, DateTime>> orderProcessRevision = f => f.CreatedAt;
Expression<Func<ProcessRevision, long>> groupProcessRevision = f => f.IdProcess;
Expression<Func<ProcessRevision, ProcessRevision>> selectProcessRevision = f => f;

processRevisions = await processRevisionRepository.GetDatasByLastRevisionAsync(orderProcessRevision, groupProcessRevision, selectProcessRevision);

The repository is as follows:

public async Task<IEnumerable<T>> GetDatasByLastRevisionAsync(
   Expression<Func<T, DateTime>> order,
   Expression<Func<T, long>> group,
   Expression<Func<T, T>> select)
{
   IQueryable<T> query = dbContext.Set<T>();

   query = query.OrderByDescending(order);
   query = query.GroupBy(group);
   query = query.Select(select);

   return await query.ToListAsync();
}

The only part of the query which works is the orderbydescending. Then, the groupby yields an error (implicit convert IGrouping to IQueryable) and also the select first item of each group goes in error.

How to apply the groupby (igrouping) to IQueryable and for every group take the first object?

Thanks for help.

try this https://stackoverflow.com/questions/33098361/row-number-over-partition-by-yyy-in-entity-framework — Jayrag Pareek, Jun 22 '21 at 09:54
If I'm not mistaken the error is because the result of `.GroupBy` is not `IQueryable` so you can't assign it to `query`. Maybe you could assign it to a new variable? (not sure if that works, have no environment to test it for you) — EpicKip, Jun 22 '21 at 09:57

score 0 · Answer 1 · answered Jun 22 '21 at 10:02

I think the issue is that GroupBy doesn't return an IQueryable<T> but an IEnumerable<TResult> where TResult is of type IGrouping<TKey, TElement>.

Expression<Func<ProcessRevision, DateTime>> orderProcessRevision = f => f.CreatedAt;
Expression<Func<ProcessRevision, long>> groupProcessRevision = f => f.IdProcess;
// Fix the type and update the lambda to select the first item in the group
Expression<Func<IGrouping<long, ProcessRevision>, ProcessRevision>> selectProcessRevision = f => f.First();

processRevisions = await processRevisionRepository.GetDatasByLastRevisionAsync(orderProcessRevision, groupProcessRevision, selectProcessRevision);

public async Task<IEnumerable<T>> GetDatasByLastRevisionAsync(
   Expression<Func<T, DateTime>> order,
   Expression<Func<T, long>> group,
   Expression<Func<IGrouping<long, T>, T>> select)
{
   IQueryable<T> query = dbContext.Set<T>();

   query = query.OrderByDescending(order);
   // Don't reassign, create a new var
   var grouped = query.GroupBy(group);
   var selected = grouped.Select(select);

   return await selected.ToListAsync();
}

I received this error on First(): `System.InvalidOperationException: 'The LINQ expression '(GroupByShaperExpression: KeySelector: (p.IdProcess), ElementSelector:(EntityShaperExpression: EntityType: ProcessRevision ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ) ) .First()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().` — Michele Boscagin, Jun 22 '21 at 10:12
Does it work, if you change `query.GroupBy(group);` to `query.GroupBy(group).ToList();` (or to ToListAsync)? It's not optimal, because you loose the translation to sql, but I'm not aware of any solution which are linq-to-sql compatible... — Emaro, Jun 22 '21 at 13:00

LINQ Expression GroupBy Select First

1 Answers1