System.LINQ.Dynamic: Select(" new (...)") into a List<t> (or any other enumerable collection of <t>)</t></t>

Asked
Viewd41095

20

Say I have a DataTable with four columns, Company (string), Fund (string), State (string), Value(double):

    table1.Rows.Add("Company 1","Fund 1","NY",100));
    table1.Rows.Add("Company 2","Fund 1","CA",200));
    table1.Rows.Add("Company 3","Fund 1","FL",300));
    table1.Rows.Add("Company 4","Fund 2","CA",400));
    table1.Rows.Add("Company 5","Fund 1","NY",500));
    table1.Rows.Add("Company 6","Fund 2","CA",600));
    table1.Rows.Add("Company 7","Fund 3","FL",700));

I want to use System.LINQ.Dynamic to build a dynamic query which groups on either Company, Fund, or State, and then selects my group by criteria as the first column, and sum(value):

string groupbyvalue="Fund";
var q1= table1.AsEnumerable().AsQueryable()
              .GroupBy(groupbyvalue,"it")
              .Select("new ("+groupbyvalue+" as Group, Sum(Value) as TotalValue)");

In the above query, the selected groupbyvalue (Group) will always be a string, and the sum will always be a double, so I want to be able to cast into something like a List, where Result is an object with properties Group (string) and TotalValue (double).

I'm having a lot of trouble with this, can anyone shed some light?

4 个答案

0

另一种更简单的方法是使用 Newtonsoft.Json 来使用Json序列化/反序列化,如下所示:

具有一个具有所需属性的类:

 public class MyClass
{
    public string Group;

    public double Total;
}
 

查询后,您对Json进行序列化,然后反序列化为所需的数据类型:

 string jsonData = JsonConvert.SerializeObject(q1);
List<MyClass> typedResult = JsonConvert.DeserializeObject<List<MyClass>>(jsonData);
 
3

我通过以下方式将返回的数据转换为List

1扩展动态linq的Select方法以支持通用类型。请参见此处 >

2使用Select方法(非常类似于dahlbyk的答案的第一行)

 Select<dynamic>("new (Key as Group, Sum(Value) as TotalValue)")
 

如果需要多列,则可以使用以下内容:

 GroupBy(@"new (Fund, Column1, Column2)", "it").
Select<dynamic>("new (Key.Fund, Key.Column1, Key.Column2, Sum(Value) as TotalValue)")
 

3将数据转换为列表。

 var data = ...
  GroupBy("...").Select<dynamic>("...").
  Cast<dynamic>().AsEnumerable().ToList();
 

4使用即兴将列表转换为列表

 var result = new List<IExampleInterface>();

foreach (var d in data)
{
  dynamic r = Impromptu.ActLike<IExampleInterface>(d);
  result.Add(r);
}
 
1

另一种可能性是扩展DLinq的查询语言,并可以在查询字符串的new子句中指定类型名称。

我在另一个 stackoverflow答案中描述了 Dynamic.cs 中的更改需求。 / p>

它将允许您进行如下查询:

 IQueryable<Result> res 
    = table1.AsQueryable()
    .GroupBy(groupbyvalue, "it")
    .Select("new Result(Key as Group, Sum(Value) as TotalValue)")
    as IQueryable<Result>;
 
42

首先,您将在Select子句中访问当前的分组值Key

 .Select("new (Key as Group, Sum(Value) as TotalValue)");
 

这应该使您的查询有效。更加棘手的问题是如何将返回的对象(具有从DynamicClass继承的动态生成的类型)转换为静态类型。

选项1:使用反射访问动态对象的GroupTotalValue属性。

选项2:使用编译的表达式树生成轻量级代码以访问GroupTotalValue属性。

选项3:修改动态库以支持强类型的结果。事实证明这很简单:

  1. ExpressionParser.Parse()中,在一个私有字段中捕获type参数:

     private Type newResultType;
    public Expression Parse(Type resultType)
    {
        newResultType = resultType;
        int exprPos = token.pos;
        // ...
     
  2. ExpressionParser.ParseNew()即将结束时,我们将在默认为动态类型之前尝试使用newResultType

     Expression ParseNew()
    {
        // ...
        NextToken();
        Type type = newResultType ?? DynamicExpression.CreateClass(properties);
        MemberBinding[] bindings = new MemberBinding[properties.Count];
        for (int i = 0; i < bindings.Length; i++)
            bindings[i] = Expression.Bind(type.GetProperty(properties[i].Name), expressions[i]);
        return Expression.MemberInit(Expression.New(type), bindings);
    }
     
  3. 最后,我们需要一个强类型的版本Select()

     public static IQueryable<TResult> Select<TResult>(this IQueryable source, string selector, params object[] values)
    {
        if (source == null) throw new ArgumentNullException("source");
        if (selector == null) throw new ArgumentNullException("selector");
        LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType, typeof(TResult), selector, values);
        return source.Provider.CreateQuery<TResult>(
            Expression.Call(
                typeof(Queryable), "Select",
                new Type[] { source.ElementType, typeof(TResult) },
                source.Expression, Expression.Quote(lambda)));
    }
     

    与原始Select()相比,唯一的变化是我们引用的TResult

现在我们只需要一个命名类型即可返回:

     public class Result
    {
        public string Group { get; set; }
        public double TotalValue { get; set; }
    }
 

更新后的查询将如下所示:

     IQueryable<Result> res = table1.AsQueryable()
        .GroupBy(groupbyvalue, "it")
        .Select<Result>("new (Key as Group, Sum(Value) as TotalValue)");