Automapper use ProjectTo after GroupBy

Question

Here are the Entity and the ViewModel:

public class Journal
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public int ProductId { get; set; }
    public string SKU { get; set; }
    public int Count { get; set; }

    public virtual Product Product { get; set; }
}

public class AnalysisVM
{
    public int ProductId { get; set; }
    public JObject SKU { get; set; }
    public int Count { get; set; }
}

I want use AutoMapper ProjectTo after GroupBy like this.

var results = await _context.Journals.Where(x => x.Date <= date)
                                     .GroupBy(x => new { x.ProductId, x.SKU })
                                     .ProjectTo<AnalysisVM>(_mapper.ConfigurationProvider)
                                     .ToListAsync();

According this LINQ GroupBy Aggregation with AutoMapper,

I Create Mapper Profile

CreateMap<string, JObject>().ConvertUsing(s => JObject.Parse(s));

CreateMap<IEnumerable<Journal>, AnalysisVM>()
    .ForMember(dest => dest.ProductId, opt => opt.MapFrom(src => src.FirstOrDefault().ProductId))
    .ForMember(dest => dest.SKU, opt => opt.MapFrom(src => src.FirstOrDefault().SKU))
    .ForMember(dest => dest.Count, opt => opt.MapFrom(src => src.Sum(x => x.Count)));

But thing goes wrong. How to use ProjectTo to GroupBy?

[2024-03-04 16:59:36 DBG 47] <Microsoft.EntityFrameworkCore.Query> Compiling query expression: 
'DbSet<Journal>()
    .Where(x => x.Date <= __date_0)
    .GroupBy(x => new { 
        ProductId = x.ProductId, 
        SKU = x.SKU
     })
    .Select(dtoIGrouping`2 => new Object_7062817___SKU{ __SKU = dtoIGrouping`2.FirstOrDefault().SKU }
    )
    .Select(dtoLet => new AnalysisVM{ 
        ProductId = dtoIGrouping`2.FirstOrDefault().ProductId, 
        SKU = JObject.Parse(dtoLet.__SKU), 
        Count = dtoIGrouping`2.Sum(x => x.Count)
    }
    )' 

[2024-03-04 16:59:36 ERR 47] <Web.Infrastructure.Filters.HttpGlobalExceptionFilter> The LINQ expression 'dtoIGrouping`2' 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. 

Package Version

"AutoMapper" Version="12.0.1"

"Microsoft.EntityFrameworkCore" Version="6.0.13"

"Pomelo.EntityFrameworkCore.MySql" Version="6.0.2"

Answer 1

The "automatic" mapping of JObject is the issue:

CreateMap<string, JObject>().ConvertUsing(s => JObject.Parse(s));

Try inlining it (worked for me with latest EF Core and AutoMapper):

CreateMap<IEnumerable<Journal>, AnalysisVM>()
    .ForMember(dest => dest.SKU, opt => opt.MapFrom(src => JObject.Parse(src.FirstOrDefault().SKU)))
    // ... 
    ;

Personally I would just use "manual" projection here which also will allow using grouping key to potentially allowing EF to generate better SQL (though you can set up it with AutoMapper also, but then you will need to create a type for the grouping key):

var results = await _context.Journals.Where(x => x.Date <= date)
    .GroupBy(x => new { x.ProductId, x.SKU })
    .Select(g => new AnalysisVM
    {
        ProductId = g.Key.ProductId,
        // .. 
    })
    .ToListAsync();