使用指定列筛选重复数据时EF Core查询转换失败求助

原代码 #

public async Task<dynamic> GetDuplicants(params string[] selectedColumns)
{
    var parameter = Expression.Parameter(typeof(Applicant), "x");
    var properties = selectedColumns.Select(columnName => Expression.Property(parameter, columnName));
    var selector = Expression.Lambda<Func<Applicant, object>>(Expression.NewArrayInit(typeof(object), properties), parameter);
    var duplicates = await _dbContext.Applicants
                                .Where(x => x.Deleted == false)
                                .GroupBy(selector)
                                .Select(group => new
                                {
                                    NumberOfApp = group.Count(),
                                    Columns = group.Key                                     
                                })
                                .Where(result => result.NumberOfApp > 1)
                                .ToListAsync();

    return duplicates;
}

报错信息 #

could not be translated. Additional information: The query contained a new array expression containing non-constant elements, which could not be translated: 'new object[]

问题原因 #

EF Core无法翻译Expression.NewArrayInit生成的数组表达式作为分组键——数组的创建逻辑无法转换为SQL语句，导致查询解析失败。

解决方案 #

改用动态创建的匿名类型作为分组键（EF Core支持将匿名类型转换为SQL分组逻辑），之后在内存中将匿名类型的分组键转换为键值对字典返回。具体实现如下：

第一步：添加动态匿名类型生成工具类 #

using System.Reflection;
using System.Reflection.Emit;

public static class AnonymousTypeBuilder
{
    public static Type CreateType(params (string Name, Type Type)[] properties)
    {
        var assemblyName = new AssemblyName("DynamicAnonymousTypes");
        var assemblyBuilder = AssemblyBuilder.DefineDynamicAssembly(assemblyName, AssemblyBuilderAccess.Run);
        var moduleBuilder = assemblyBuilder.DefineDynamicModule("DynamicAnonymousTypesModule");
        // 生成唯一的匿名类型名称
        var typeBuilder = moduleBuilder.DefineType(
            $"AnonymousType_{Guid.NewGuid():N}",
            TypeAttributes.Public | TypeAttributes.Class | TypeAttributes.Serializable);

        var constructorParams = new List<Type>();
        var propertyNames = new List<string>();

        foreach (var (name, type) in properties)
        {
            // 创建私有字段存储属性值
            var fieldBuilder = typeBuilder.DefineField($"_{name}", type, FieldAttributes.Private);
            // 创建公共只读属性
            var propertyBuilder = typeBuilder.DefineProperty(name, PropertyAttributes.None, type, null);
            
            // 实现属性getter
            var getterBuilder = typeBuilder.DefineMethod(
                $"get_{name}", 
                MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig, 
                type, 
                Type.EmptyTypes);
            var getterIl = getterBuilder.GetILGenerator();
            getterIl.Emit(OpCodes.Ldarg_0);
            getterIl.Emit(OpCodes.Ldfld, fieldBuilder);
            getterIl.Emit(OpCodes.Ret);

            propertyBuilder.SetGetMethod(getterBuilder);
            constructorParams.Add(type);
            propertyNames.Add(name);
        }

        // 生成构造函数，为每个字段赋值
        var constructorBuilder = typeBuilder.DefineConstructor(
            MethodAttributes.Public, 
            CallingConventions.Standard, 
            constructorParams.ToArray());
        var constructorIl = constructorBuilder.GetILGenerator();

        for (int i = 0; i < constructorParams.Count; i++)
        {
            constructorIl.Emit(OpCodes.Ldarg_0);
            constructorIl.Emit(OpCodes.Ldarg, i + 1);
            var field = typeBuilder.GetField($"_{propertyNames[i]}");
            constructorIl.Emit(OpCodes.Stfld, field);
        }
        constructorIl.Emit(OpCodes.Ret);

        return typeBuilder.CreateType();
    }
}

第二步：修改查询方法 #

public async Task<dynamic> GetDuplicants(params string[] selectedColumns)
{
    if (selectedColumns == null || selectedColumns.Length == 0)
        throw new ArgumentException("必须指定至少一列用于筛选重复项");

    var parameter = Expression.Parameter(typeof(Applicant), "x");
    // 获取选中列对应的PropertyInfo
    var propertyInfos = selectedColumns
        .Select(col => typeof(Applicant).GetProperty(col))
        .Where(p => p != null)
        .ToList();

    if (propertyInfos.Count == 0)
        throw new ArgumentException("指定的列名在Applicant类型中不存在");

    // 动态生成对应列的匿名类型
    var anonymousType = AnonymousTypeBuilder.CreateType(
        propertyInfos.Select(p => (p.Name, p.PropertyType)).ToArray());

    // 构建匿名类型的构造表达式
    var constructor = anonymousType.GetConstructor(propertyInfos.Select(p => p.PropertyType).ToArray());
    var constructorArgs = propertyInfos.Select(p => Expression.Property(parameter, p)).ToArray();
    var newExpression = Expression.New(constructor, constructorArgs);

    // 构建分组键的Lambda表达式
    var selector = Expression.Lambda<Func<Applicant, object>>(newExpression, parameter);

    // 执行数据库查询：过滤未删除数据 -> 分组 -> 统计数量 -> 筛选重复项
    var rawDuplicates = await _dbContext.Applicants
        .Where(x => x.Deleted == false)
        .GroupBy(selector)
        .Select(group => new
        {
            NumberOfApp = group.Count(),
            GroupKey = group.Key
        })
        .Where(result => result.NumberOfApp > 1)
        .ToListAsync();

    // 将匿名类型的分组键转换为字典，方便返回
    var result = rawDuplicates.Select(item => new
    {
        item.NumberOfApp,
        Columns = item.GroupKey.GetType().GetProperties()
            .ToDictionary(p => p.Name, p => p.GetValue(item.GroupKey))
    }).ToList();

    return result;
}

说明 #

• 动态生成的匿名类型会被EF Core正确转换为SQL中的GROUP BY语句，解决了原数组表达式无法翻译的问题。
• 最终返回的Columns是一个字典，键为列名，值为对应字段的值，便于调用方读取。
• 添加了参数校验，避免传入空列或不存在的列导致错误。

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