Error when trying to use group by with projectTo

[MaticDiba]

Version: x.y.z

AutoMapper 11.0.1
AutoMapper.Extensions.Microsoft.DependencyInjection 11.0.0
Microsoft.EntityFrameworkCore.SqlServer 6.0.4

Expected behavior

I'm trying to use groupby with returning the first result of a group. This got supported in the new version of EFCore: https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/whatsnew#improved-groupby-support

Example:

// query is of Type IQueryable<T>
query = query.GroupBy(specification.GroupBy).Select(x => x.OrderByDescending(d => d.CreatedDate).First());
var test = await query.ToListAsync(); // Test line to see if EF work with group by. List is generated sucessfully.
var projected = query.ProjectTo<A>(_mapper.ConfigurationProvider);

var items = await projected.ToListAsync(); // throws the error below

I tried setting up a mapping from T to A with CreateMap and CreateProjection. The result was the same.

Actual behavior

The error I get after ToListAsync:

The LINQ expression 'ProjectionBindingExpression: 0' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

If I skip the GroupBy, ProjectTo works.

Is there another way I could use this with projectTo?

[lbargaoanu]

Check the execution plan. Run that as a LINQ statement, without AM. You should get the same result.

[MaticDiba]

I tested that. And the only difference is the part added by projectTo.

Without project to (this works):

var docs = await _context.Entity.AsNoTracking()
                .Where(cv => ...)
                .Where(d => ...)
                .OrderByDescending(cv => (object)cv.CreatedDate)
                .Where(d => d.Name != null)
                .GroupBy(d => d.Name)
                .Select(x => x
                    .OrderByDescending(d => d.CreatedDate)
                    .FirstOrDefault())
                .Skip(0)
                .Take(10).ToListAsync();

And Linq as a result of projectTo (this one throws the error above:

var docs = await _context.Entity.AsNoTracking()
                .Where(cv => ...)
                .Where(d => ...)
                .OrderByDescending(cv => (object)cv.CreatedDate)
                .Where(d => d.Name != null)
                .GroupBy(d => d.Name)
                .Select(x => x
                    .OrderByDescending(d => d.CreatedDate)
                    .FirstOrDefault())
                .Skip(0)
                .Take(10)
                .Select(Dto=> new Dto
                {
                    Id = dtoDocument.Id,
                    ... // other queries to related connected properties
                }
                ).ToListAsync();

[lbargaoanu]

Right :) So now you have a Select statement that doesn't work. AM is no longer involved. Once you make that query work, it will be easy to rewrite that with ProjectTo.

[MaticDiba]

The problem is, that Select after GroupBy will never work. The only way this would work is to introduce select before FirstOrDefault.

I'm trying to figure out if it's even possible to use AutoMapper with grouping and taking first element.

[jbogard]

AutoMapper has nothing to do with it at this point, you're now just trying to get Select to work. You can ask about that in Stack Overflow or in the EF Core repo.

With AutoMapper and LINQ, anything going wrong is never (very very very rarely) AutoMapper's fault 🤣 it's always the underlying LINQ provider