locked
Linq to dataset: dynamic aggregate functions on dynamic group by fields on dynamic columns RRS feed

  • Question

  • Hi,

    My problem is how can i handle dynamic aggregate function and group by fields and column names in linq query as it can vary in numbers(depends on array values that too will depend on user selection).

    I have four parameters to my function a datatable , array consisting of expressions(aggregate functions),array consisting of column names on which to apply expressions and an array consisting of column names on which i have to group by.


    i have written code for static querry, but need a generic way...

    like in my code i can have n number of fields in group by, like for now i have taken only two(DataSourceType and Polarity) and similarly i can have n number of columns and aggregate functions like for sum,count etc which will be as an array as parameter based on user selection.

    Actually all the aggregate functions and group by fields and column names on which to apply group by depends on user selection, on the basis of this i have to draw chart, I looked over the dynamic linq library but could not figure it out how it could be helpful in my case.

    Please reply its urgent, if linq cannot be helpful please suggest some alternative.

    Thanks in advance!

    regards,

    Peeyush Pandey


      public void ExpressionManipulation(DataTable dtExprEvaluate, string[] strExpressions, string[] colName, string[] groupbyFields)
    {
           var groupByQueryEvaluate = from table in dtExprEvaluate.AsEnumerable()
                              group table by new { column1 = table["DataSourceType"], column2 = table["Polarity"] }
                                  into groupedTable
                                  select new
                                  {
                                      x = groupedTable.Key,  // Each Key contains column1 and column2
                                      y = groupedTable.Count(),
                                      //z = groupedTable.Max(column1),
                                      z = groupedTable.Sum(table => Convert.ToInt32(table["Polarity"]))
                                  };
        
    }



    Software Developer Next Gen Invent Corporation


    Sunday, April 14, 2013 5:24 AM

Answers

  • I figured it out and it worked with dynamic linq:

     var objGroupSumCountkey = dt.AsEnumerable().AsQueryable().GroupBy("new ( it[\"DataSourceType\"] as GroupByColumnName1,it[\"Polarity\"] as GroupByColumnName2)", "it")
                                              .Select("new ( Sum(Convert.ToDouble(it[\"Polarity\"].ToString())) as SumValue,Count() as TotalCount,it.key)");

    Thanks!

    Software Developer Next Gen Invent Corporation

    Tuesday, April 23, 2013 12:54 PM

All replies

  • Generally Linq has do be fully defined at Compile Time. You cannot create and execute a Linq query at runtime.

    Unless you use dynamic linq:

    http://stackoverflow.com/questions/6417791/how-do-i-build-dynamic-linq-queries-at-runtime-using-strings

    Be advised that you totally loose linq's typesavety when doing this (one of the main reasons to use it) and is a lot slower as well.

    It might be better to restructure your data or generally give us a bit more information.


    Let's talk about MVVM: http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/b1a8bf14-4acd-4d77-9df8-bdb95b02dbe2

    Sunday, April 14, 2013 5:35 AM
  • Sunday, April 14, 2013 12:46 PM
  • I have gone through this link but could not figure out how it can be helpful, could you please illustrate with an example or so that relates to my problem, that could really be of help.

    Thanks!


    Software Developer Next Gen Invent Corporation

    Thursday, April 18, 2013 3:51 AM
  • Is there any other way to accomplish my need??

    Software Developer Next Gen Invent Corporation

    Thursday, April 18, 2013 3:52 AM
  • I figured it out and it worked with dynamic linq:

     var objGroupSumCountkey = dt.AsEnumerable().AsQueryable().GroupBy("new ( it[\"DataSourceType\"] as GroupByColumnName1,it[\"Polarity\"] as GroupByColumnName2)", "it")
                                              .Select("new ( Sum(Convert.ToDouble(it[\"Polarity\"].ToString())) as SumValue,Count() as TotalCount,it.key)");

    Thanks!

    Software Developer Next Gen Invent Corporation

    Tuesday, April 23, 2013 12:54 PM
  • An other solution is to use the QueryByCube function provided by the AdaptiveLINQ component (www.adaptivelinq.com)

    QueryByCube doesn't use dynamic LINQ but transform the expression at runtime.

    I'm the AdaptiveLINQ developer. Feel free to send me any question.

    Friday, February 7, 2014 8:42 PM