如何在GroupBy后正确使用AutoMapper的ProjectTo方法？

实体类与视图模型 #

Journal实体类 #

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; }
}

AnalysisVM视图模型 #

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

原实现代码 #

查询代码 #

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

AutoMapper映射配置 #

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)));

错误日志 #

[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. 

问题原因 #

1. 映射类型不匹配：EF Core的GroupBy返回IGrouping<TKey, Journal>集合，而非IEnumerable<Journal>，原映射配置的源类型错误，导致AutoMapper生成的查询逻辑混乱。
2. 无法翻译的客户端操作：JObject.Parse是.NET客户端方法，EF Core无法将其转换为SQL语句执行，必须在数据拉取到客户端后处理。
3. 查询嵌套引用异常：AutoMapper生成的查询中，内层Select无法引用外层的IGrouping对象，导致EF Core无法解析表达式。

正确解决方案 #

方案一：先数据库聚合，再客户端映射（推荐） #

先在数据库端完成分组聚合，拉取必要字段后，在客户端用AutoMapper映射为AnalysisVM，避免EF Core无法翻译的操作：

调整查询代码

// 数据库端完成分组聚合，仅拉取需要的字段
var dbResults = await _context.Journals
    .Where(x => x.Date <= date)
    .GroupBy(x => new { x.ProductId, x.SKU })
    .Select(g => new 
    {
        g.Key.ProductId,
        g.Key.SKU,
        Count = g.Sum(x => x.Count)
    })
    .ToListAsync();

// 客户端映射为AnalysisVM
var results = _mapper.Map<List<AnalysisVM>>(dbResults);

调整AutoMapper配置

新增匿名类型到AnalysisVM的映射，保留string到JObject的转换：

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

// 针对匿名类型配置映射，或定义中间DTO提升严谨性
CreateMap<object, AnalysisVM>()
    .ForMember(dest => dest.ProductId, opt => opt.MapFrom(src => src.GetType().GetProperty("ProductId").GetValue(src)))
    .ForMember(dest => dest.SKU, opt => opt.MapFrom(src => src.GetType().GetProperty("SKU").GetValue(src)))
    .ForMember(dest => dest.Count, opt => opt.MapFrom(src => src.GetType().GetProperty("Count").GetValue(src)));

方案二：适配IGrouping的AutoMapper配置（客户端处理转换） #

若坚持使用ProjectTo，需调整映射配置适配IGrouping，并将JObject转换标记为客户端执行：

调整AutoMapper配置

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

// 针对IGrouping<匿名键类型, Journal>配置映射
CreateMap<IGrouping<object, Journal>, AnalysisVM>()
    .ForMember(dest => dest.ProductId, opt => opt.MapFrom(src => src.Key.GetType().GetProperty("ProductId").GetValue(src.Key)))
    .ForMember(dest => dest.SKU, opt => opt.MapFrom(src => src.Key.GetType().GetProperty("SKU").GetValue(src.Key)))
    .ForMember(dest => dest.Count, opt => opt.MapFrom(src => src.Sum(x => x.Count)))
    // 客户端完成JObject转换
    .AfterMap((src, dest) => dest.SKU = JObject.Parse(dest.SKU.ToString()));

调整查询代码

需先切换到客户端执行上下文，再执行映射：

var results = await _context.Journals
    .Where(x => x.Date <= date)
    .GroupBy(x => new { x.ProductId, x.SKU })
    .AsEnumerable() // 切换到客户端执行
    .ProjectTo<AnalysisVM>(_mapper.ConfigurationProvider)
    .ToListAsync();

注意：AsEnumerable()会将分组后的数据全部拉取到客户端，数据量大时需谨慎使用，优先选择方案一。

内容的提问来源于stack exchange，提问作者qslcna