c# - groupby 的表达式树，带有 where 子句，而不是 select

Question

来自 UI 的动态列作为 API 中的参数出现，并且基于该参数我必须从数据库中获取数据。示例：在下面的代码中，基于列 if 条件 linq 查询正在执行。现在我想让它成为通用的，以便在将来出现新的列条件时提供服务。

public List<string> GetFilteredTypeAhead(string searchText,string searchForRole,int fiscalyear,int fiscalPeriod)
        {
 if (searchForRole == "column1")
            {
                var accounts = (from a in _context.Account
                                where a.column1.StartsWith(searchText) && a.FiscalPeriod == fiscalPeriod && a.FiscalYear ==fiscalyear
                                group a.column1 by a.column2 into g
                                select g.Key).ToList();
                return accounts;
            }
            else if(searchForRole == "column2")
            {
                var accounts = (from a in _context.Account
                                where a.column2.StartsWith(searchText) && a.FiscalPeriod == fiscalPeriod && a.FiscalYear == fiscalyear
                                group a.column2 by a.column2 into g
                                select g.Key).ToList();
                return accounts;
            }
            else if (searchForRole == "column3")
            {
                var accounts = (from a in _context.Account
                                where a.column3.StartsWith(searchText) && a.FiscalPeriod == fiscalPeriod && a.FiscalYear == fiscalyear
                                group a.column3 by a.column3 into g
                                select g.Key).ToList();
                return accounts;
            }
            else if (searchForRole == "column4")
            {
                var accounts = (from a in _context.Account
                                where a.column4.StartsWith(searchText) && a.FiscalPeriod.Equals(fiscalPeriod) && a.FiscalYear.Equals(fiscalyear)
                                group a.column4 by a.column4 into g
                                select g.Key).ToList();
                return accounts;
            }
            else
            {
                return new List<string>();
            }
        }

将其转换为泛型。我创建了一个表达式树。

static IQueryable<T> ConvertToExpression<T>(IQueryable<T> query, string propertyValue, PropertyInfo propertyInfo, int fiscalyear, int fiscalPeriod)
        {
            ParameterExpression e = Expression.Parameter(typeof(T), "e");
            MemberExpression m = Expression.MakeMemberAccess(e, propertyInfo);
            ConstantExpression c = Expression.Constant(propertyValue, typeof(string));
            MethodInfo mi = typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) });
            Expression call = Expression.Call(m, mi, c);

            PropertyInfo propertyInfoFiscalPeriod = typeof(T).GetProperty("FiscalPeriod");
            MemberExpression memberPropertyFiscalPeriod = Expression.Property(e, propertyInfoFiscalPeriod);
            ConstantExpression right = Expression.Constant(fiscalPeriod);
            Expression equalsFiscalPeriod = Expression.Equal(memberPropertyFiscalPeriod, Expression.Convert(right, typeof(Int16)));

            PropertyInfo propertyInfoFiscalYear = typeof(T).GetProperty("FiscalYear");
            MemberExpression memberPropertyFiscalYear = Expression.Property(e, propertyInfoFiscalYear);
            right = Expression.Constant(fiscalyear);
            Expression equalsFiscalYear = Expression.Equal(memberPropertyFiscalYear, Expression.Convert(right, typeof(Int16)));

            Expression combineExpression = Expression.And(equalsFiscalPeriod, equalsFiscalYear);

            Expression predicateBody = Expression.And(call, combineExpression);

            Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(predicateBody, e);
            return query.Where(lambda);
        }

为了调用它，我使用了如下代码“searchForRole”作为“column1”、“column2”等的参数

 PropertyInfo propertyInfo = typeof(Account).GetProperty(searchForRole);

            IQueryable<Account> query = _context.Account;

            query = ConvertToExpression(query, searchText, propertyInfo,fiscalyear,fiscalPeriod);



            var list = query.ToList();

现在这工作正常，但结果有重复记录。我想在传递的参数列上有一些不同的或分组依据。用简单的话来说，我想删除 if 条件并使我的搜索方法通用。请帮忙。

标签： c#entity-frameworklinqlambda

Answer 1

这是可能的，但恕我直言，最好将动态部分保持在最低限度并尽可能使用 C# 编译时安全性。

有问题的示例查询

var accounts = (from a in _context.Account
                where a.column1.StartsWith(searchText) && a.FiscalPeriod == fiscalPeriod && a.FiscalYear ==fiscalyear
                group a.column1 by a.column1 into g
                select g.Key).ToList();

可以改写如下

var accounts = _context.Account
    .Where(a => a.FiscalPeriod == fiscalPeriod && a.FiscalYear == fiscalyear)
    .Select(a => a.column1)
    .Where(c => c.StartsWith(searchText))
    .Distinct()
    .ToList();

如您所见，唯一的动态部分是a => a.column1type Expression<Func<Account, string>>。所以你需要的只是一个这样的方法：

static Expression<Func<T, M>> MemberSelector<T>(string name)
{
    var parameter = Expression.Parameter(typeof(T), "e");
    var body = Expression.PropertyOrField(name);
    return Expression.Lambda<Func<T, M>>(body, parameter);
}

并替换

.Select(a => a.column1)

和

.Select(MemberSelector<Account, string>(searchForRole))