Entity Framework通用分页：Linq无法翻译泛型类型的解决办法

问题背景 #

正在为MySQL数据库的Linq查询开发通用分页功能，因不同数据表筛选字段存在差异，需要实现通用的Where条件逻辑，但当前实现中Linq无法翻译泛型类型相关的反射调用，导致查询失败。

原实现代码 #

public static class PaginationExtentions {
    public static async Task<PaginationResult<T>> Pagination<T, J>(this DbSet<J> dbSet, Pagination pagination, Func<IQueryable<J>, IQueryable<T>>? select) where J : class {
        bool previousPage = false;
        bool nextPage = false;
        string startCursor = null;
        string endCursor = null;
        
        var property = typeof(J).GetProperty(pagination.SortBy);
        
        string after = pagination.After != null ? Base64Decode(pagination.After) : null;
        
        bool backwardMode = after != null && after.ToLower().StartsWith("prev__");
        string cursor = after != null ? after.Split("__", 2).Last() : null;

        List<J> items;
        if (cursor == null) {
            items = await (from s in dbSet
                           orderby GetPropertyValue<J, string>(s, pagination.SortBy) ascending
                           select s).Take(pagination.First)
                                    .ToListAsync();
        }
        else if (backwardMode) {
            items = await (from subject in (
                                                     from s in dbSet
                                                     where string.Compare( (string?)property.GetValue(s), cursor) < 0
                                                     orderby (string?)property.GetValue(s) descending
                                                     select s).Take(pagination.First)
                                 orderby (string?)property.GetValue(subject) ascending
                                 select subject).ToListAsync();
        }
        else {
            items = await (from s in dbSet
                           where string.Compare((string?)property.GetValue(s), cursor) > 0
                           orderby GetPropertyValue<J, string>(s, pagination.SortBy) ascending
                           select s).Take(pagination.First)
                                    .ToListAsync();
        }

        previousPage = await dbSet.Where(s => string.Compare((string?)property.GetValue(s), cursor) < 0).AnyAsync();
        nextPage = items.Count == 0 ? false : await dbSet.Where(s => string.Compare((string?)property.GetValue(s), (string?)property.GetValue(items.Last())) > 0).AnyAsync();

        var backwardsCursor = !previousPage ? null : "prev__" + cursor;
        var forwardsCursor = !nextPage ? null : items.Count > 0 ? "next__" + (string?)property.GetValue(items.Last()) : null;

        return new PaginationResult<T>() {
                                             Items = select(items.AsQueryable()).ToList(),
                                             TotalCount = await dbSet.CountAsync(),
                                             HasPreviousPage = previousPage,
                                             HasNextPage = nextPage,
                                             StartCusor = Base64Encode(backwardsCursor),
                                             EndCursor = Base64Encode(forwardsCursor)
                                         };
    }
    
    private static U GetPropertyValue<T, U>(T obj, string propertyName) {
        return (U)obj.GetType().GetProperty(propertyName).GetValue(obj, null);
    }
    
    public static string Base64Encode(string plainText) {
        var plainTextBytes = System.Text.Encoding.UTF8.GetBytes(plainText);
        return System.Convert.ToBase64String(plainTextBytes);
    }
    
    public static string Base64Decode(string base64EncodedData) {
        var base64EncodedBytes = System.Convert.FromBase64String(base64EncodedData);
        return System.Text.Encoding.UTF8.GetString(base64EncodedBytes);
    }
}

问题原因 #

原代码使用了反射API（property.GetValue、GetPropertyValue）来访问实体属性，EF Core无法将这些反射调用翻译为对应的SQL字段访问逻辑，因此会抛出"无法翻译方法/表达式"的异常。

可行解决方案 #

核心思路是用表达式树动态构建属性访问的Lambda表达式，让EF Core能够解析并转换为SQL语句。以下是完整的改进实现：

1. 新增表达式树构建辅助方法 #

添加用于生成属性访问、排序、筛选表达式的工具方法：

private static Expression<Func<J, string>> GetPropertyAccessExpr<J>(string propertyName) where J : class
{
    var parameter = Expression.Parameter(typeof(J), "s");
    var property = Expression.Property(parameter, propertyName);
    var converted = Expression.Convert(property, typeof(string));
    return Expression.Lambda<Func<J, string>>(converted, parameter);
}

private static Expression<Func<J, bool>> GetFilterExpr<J>(string propertyName, string cursor, bool isLessThan) where J : class
{
    var propertyExpr = GetPropertyAccessExpr<J>(propertyName);
    var cursorConst = Expression.Constant(cursor, typeof(string));
    
    var compareMethod = typeof(string).GetMethod(nameof(string.Compare), new[] { typeof(string), typeof(string) });
    var compareExpr = Expression.Call(compareMethod, propertyExpr.Body, cursorConst);
    
    var zeroConst = Expression.Constant(0, typeof(int));
    var conditionExpr = isLessThan 
        ? Expression.LessThan(compareExpr, zeroConst) 
        : Expression.GreaterThan(compareExpr, zeroConst);
    
    return Expression.Lambda<Func<J, bool>>(conditionExpr, propertyExpr.Parameters[0]);
}

2. 重构分页方法逻辑 #

替换原代码中的反射调用，改用表达式树生成的Lambda表达式：

public static async Task<PaginationResult<T>> Pagination<T, J>(this DbSet<J> dbSet, Pagination pagination, Func<IQueryable<J>, IQueryable<T>>? select) where J : class
{
    bool previousPage = false;
    bool nextPage = false;
    string startCursor = null;
    string endCursor = null;
    
    string after = pagination.After != null ? Base64Decode(pagination.After) : null;
    bool backwardMode = after != null && after.ToLower().StartsWith("prev__");
    string cursor = after != null ? after.Split("__", 2).Last() : null;
    
    var propertyAccessExpr = GetPropertyAccessExpr<J>(pagination.SortBy);
    IQueryable<J> query = dbSet;
    List<J> items;

    if (cursor == null)
    {
        items = await query.OrderBy(propertyAccessExpr)
                           .Take(pagination.First)
                           .ToListAsync();
    }
    else if (backwardMode)
    {
        var filterExpr = GetFilterExpr<J>(pagination.SortBy, cursor, isLessThan: true);
        items = await query.Where(filterExpr)
                           .OrderByDescending(propertyAccessExpr)
                           .Take(pagination.First)
                           .OrderBy(propertyAccessExpr)
                           .ToListAsync();
    }
    else
    {
        var filterExpr = GetFilterExpr<J>(pagination.SortBy, cursor, isLessThan: false);
        items = await query.Where(filterExpr)
                           .OrderBy(propertyAccessExpr)
                           .Take(pagination.First)
                           .ToListAsync();
    }

    if (cursor != null)
    {
        var prevFilterExpr = GetFilterExpr<J>(pagination.SortBy, cursor, isLessThan: true);
        previousPage = await query.AnyAsync(prevFilterExpr);
    }

    if (items.Count > 0)
    {
        var lastItemValue = propertyAccessExpr.Compile()(items.Last());
        var nextFilterExpr = GetFilterExpr<J>(pagination.SortBy, lastItemValue, isLessThan: false);
        nextPage = await query.AnyAsync(nextFilterExpr);
    }
    else
    {
        nextPage = false;
    }

    var backwardsCursor = !previousPage ? null : "prev__" + cursor;
    var forwardsCursor = !nextPage ? null : items.Count > 0 ? "next__" + propertyAccessExpr.Compile()(items.Last()) : null;

    return new PaginationResult<T>
    {
        Items = select != null ? select(items.AsQueryable()).ToList() : items.Cast<T>().ToList(),
        TotalCount = await query.CountAsync(),
        HasPreviousPage = previousPage,
        HasNextPage = nextPage,
        StartCusor = Base64Encode(backwardsCursor),
        EndCursor = Base64Encode(forwardsCursor)
    };
}

3. 关键改进点说明 #

• 表达式树替代反射：通过Expression类动态构建属性访问和筛选逻辑，EF Core可以将这些表达式翻译为对应的SQL字段操作。
• 类型安全的查询构建：避免了反射带来的运行时类型风险，同时保证查询能被EF Core正确解析。
• 兼容MySQL排序/筛选：生成的表达式会被转换为MySQL支持的ORDER BY、WHERE语句，适配数据库特性。

注意事项 #

• 确保pagination.SortBy传入的是实体类中存在的字符串类型属性名，否则会抛出属性不存在的异常。
• 如果需要支持非字符串类型的排序字段，可扩展辅助方法，增加对不同类型的处理逻辑（如int、DateTime等）。

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