c# - 带分组依据和字符串变量的 LINQ 动态查询

Question

我想使用 LINQ-to-SQL 创建一个动态查询，除一件事外一切正常:分组依据。我查看了其他问题，但到目前为止还没有找到它不起作用的原因。

我将 using System.Linq.Dynamic; 与以下查询一起使用(有效):

int numberOfRankedItems = 3;

string minMaxChoice = "max";
string orderBy = "";
if (minMaxChoice == "max")
{
    orderBy = "queryGroup.Sum(row => row.POSITIONVALUE) descending";
} else if (minMaxChoice == "min")
{
    orderBy = "queryGroup.Sum(row => row.POSITIONVALUE) ascending";
}

string minMaxFeatureColumnName = "BRANDNAME";
string selectMinMaxFeature = "new { minMaxFeature = queryGroup.Key." + minMaxFeatureColumnName + ", sumPosition = queryGroup.Sum(row => row.POSITIONVALUE)}";

var query = (from tableRow in Context.xxx
                where /* some filters */
                group tableRow by tableRow.BRANDNAME into queryGroup
                orderby orderBy
                select selectMinMaxFeature).Take(numberOfRankedItems).ToList();

orderby 和 select 变量的使用工作正常，但对于 group by 则不管我尝试用变量替换什么.如果我例如查询实际工作使用变量而不是 tableRow.BRANDNAME 但查询返回错误结果。目标是能够动态设置分组列。

想法？

谢谢

编辑:其他变量似乎也存在多个问题。所以我稍微概括了这个问题:如何根据

概括以下查询

1. 分组依据的列
2. 要按 + ASC 或 DESC 排序的列
3. 在 select 语句中第一条语句的列名 (BRANDNAME)

这里是查询:

var query = (from tableRow in ctx.xxx
                where /* filter */
                group tableRow by new { tableRow.BRANDNAME } into queryGroup
                orderby queryGroup.Sum(row => row.POSITIONVALUE) descending
                select new { minMaxFeature = queryGroup.Key.BRANDNAME, sumPosition = queryGroup.Sum(row => row.POSITIONVALUE) }).Take(numberOfRankedItems).ToList();

没有表达式树会很棒;)

Answer 1

我现在已经详细阐述了这个问题的解决方案:

解决方案像以前一样使用 using System.Linq.Dynamic; 现在允许设置某些变量。最后它返回一个字典。

// set variables
int numberOfRankedItems;
if (queryData.NumberOfRankedItems != null)
{
    numberOfRankedItems = (int) queryData.NumberOfRankedItems;
} else
{
    numberOfRankedItems = 0;
}
string minMaxChoice = queryData.MinMaxChoice;
string minMaxFeatureColumnName = queryData.MinMaxFeatureColumnName;

string orderByPrompt = "";
if (minMaxChoice == "max")
{
    orderByPrompt = "it.Sum(POSITIONVALUE) descending";
} else if (minMaxChoice == "min")
{
    orderByPrompt = "it.Sum(POSITIONVALUE) ascending";
}

string groupByPrompt = queryData.MinMaxFeatureColumnName;

// execute query
IQueryable query = (from tableRow in Context.xxx
                        where /* some filters */
                        select tableRow).
                        GroupBy(groupByPrompt, "it").
                        OrderBy(orderByPrompt).
                        Select("new (it.Key as minMaxFeature, it.Sum(POSITIONVALUE) as sumPosition)").
                        Take(numberOfRankedItems);

// create response dictionary
Dictionary<int?, Dictionary<string, int?>> response = new Dictionary<int?, Dictionary<string, int?>>();
int count = 1;
foreach (dynamic item in query)
{
    string minMaxFeatureReturn = item.GetType().GetProperty("minMaxFeature").GetValue(item);
    int? sumPositionReturn = item.GetType().GetProperty("sumPosition").GetValue(item);
    response[count] = new Dictionary<string, int?>() { { minMaxFeatureReturn, sumPositionReturn } };
    count++;
}            
return response;