Select(" new (...)") into a ListT> (or any other enumerable collection of T>) using System.LINQ.Dynamic.

dynamic-linq linq

Question

Consider a data table with the following four columns: Company (string), Fund (string), State (string), and 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 create a dynamic query using System.LINQ.Dynamic that groups on Company, Fund, or State, chooses my group by criterion as the first column, then sums the values:

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

I want to be able to cast into something like a List, where Result is an object with attributes Group (string) and TotalValue, as the chosen groupbyvalue (Group) in the aforementioned query will always be a string and the total will always be a double (double).

Can somebody help me with this? I'm having a lot of difficulties.

1
20
4/30/2014 1:08:04 PM

Popular Answer

The current grouped value will be the first thing you access.Key When using the Select clause:

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

Your query should function as a result. The trickier issue is how to transform the returned objects, whose type will be dynamically produced and inherit fromDynamicClass creating a static type.

Option 1: Access the dynamic object's properties using reflection.Group and TotalValue properties.

Option 2: Generate lightweight code using compiled expression trees to access theGroup and TotalValue properties.

Third alternative: Change the Dynamic library to enable strongly-typed results. It turns out to be rather easy to do this:

  1. In ExpressionParser.Parse() the type parameter should be recorded in a private field:

    private Type newResultType;
    public Expression Parse(Type resultType)
    {
        newResultType = resultType;
        int exprPos = token.pos;
        // ...
    
  2. In the vicinity ofExpressionParser.ParseNew() we'll attempt to utilizenewResultType prior to using a dynamic type by default:

    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. Last but not least, we need a highly typed variant ofSelect() :

    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)));
    }
    

    The only modifications to the originalSelect() are sites that we mentionTResult .

All that is left is for a named type to return:

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

And your revised search will appear as follows:

    IQueryable<Result> res = table1.AsQueryable()
        .GroupBy(groupbyvalue, "it")
        .Select<Result>("new (Key as Group, Sum(Value) as TotalValue)");
42
9/23/2009 8:33:32 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow