none
Dynamic LINQ RRS feed

  • Question

  • I have to write Dynamic Linq to group results in a datatable and spit it back to a datatable. Here is a simple example of what I need to do. The columns in the datatable are variable so I don't know which ones I will be using until runtime, I have a list of which ones will be grouped on and which ones will be aggregated. Here is the code:

    var

     

    q = dt.AsEnumerable().AsQueryable();

     

    var qtest = q.GroupBy("New(get_Item(@0) as Segment, get_Item(@1) as Region)", "it", "Segment", "Region");

     

    var qtest2 = q.Select("New(get_Item(@1) as Segment, get_Item(@2) as Region, Sum(get_Item(@0)) as DirectMailVolume)", "DirectMailVolume", "Segment", "Region");

     

    DataTable test = qtest2.CopyToDataTable();

    The part that doesn't work is the aggregate on the select when it is running. The groupby doesn't blowup, but because the select blows up I don't know if the grouping is even working. The error I am getting is "Sum not valid method for type DataRow" or something to that affect. I am using the Dynamic Linq Library as I am sure you can tell by the code snippet. The strings in the GroupBy and the Select I am building dynamically, this is just the test one that I was trying to work out the syntax with. Thank you in advance for your help.

    Tuesday, April 6, 2010 1:56 PM

Answers

  • Hello Steve,

     

    Welcome to MSDN Forums!

     

    First, based on my understanding, the qtest2 query should be based on qtest instead of q.  

     

    Then, the Sum method should executed on the GroupBy result to calculate each group’s DirectMailVolume sum.  However, if the DirectMailVolume is in type of integer, we need to pass the lambda expression Expression<Func<IEnumerable<DataRow>, int>> into the .Select() extension method:

    ======================================================================================

    var qtest = q.GroupBy("New(get_Item(@0) as Segment, get_Item(@1) as Region)", "it", "Segment", "Region");

    Expression<Func<IEnumerable<DataRow>, int>> ex = list => list.Sum(d => d.Field<int>("DirectMailVolume"));

    var qtest2 = qtest.Select("New(Key.Segment as Segment, Key.Region as Region, @0(it) as DirectMailVolume)", ex);

    ======================================================================================

    Here I used the Dynamic Lambda Invocation feature of the Dynamic LINQ Library.  For detail, please see the Dynamic Lambda Invocation section in the Dynamic LINQ Library documentation. 

     

    At last, the CopyToDataTable() should be executed on IEnumerable<DataRow>, however, qtest2 gets a list of anonymous type.  If you want to convert the anonymous type list into DataTable, please refer to http://unboxedsolutions.com/sean/archive/2009/06/06/15961.aspx.  

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     

    P.S.   I will move this thread to ADO.NET DataSet forum. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, April 7, 2010 5:08 AM
    Moderator

All replies

  • You might want to try posting your question here.  Data Platform Development   On one of those forums.

    Mark the best replies as answers. "Fooling computers since 1971."
    Tuesday, April 6, 2010 4:48 PM
  • Hello Steve,

     

    Welcome to MSDN Forums!

     

    First, based on my understanding, the qtest2 query should be based on qtest instead of q.  

     

    Then, the Sum method should executed on the GroupBy result to calculate each group’s DirectMailVolume sum.  However, if the DirectMailVolume is in type of integer, we need to pass the lambda expression Expression<Func<IEnumerable<DataRow>, int>> into the .Select() extension method:

    ======================================================================================

    var qtest = q.GroupBy("New(get_Item(@0) as Segment, get_Item(@1) as Region)", "it", "Segment", "Region");

    Expression<Func<IEnumerable<DataRow>, int>> ex = list => list.Sum(d => d.Field<int>("DirectMailVolume"));

    var qtest2 = qtest.Select("New(Key.Segment as Segment, Key.Region as Region, @0(it) as DirectMailVolume)", ex);

    ======================================================================================

    Here I used the Dynamic Lambda Invocation feature of the Dynamic LINQ Library.  For detail, please see the Dynamic Lambda Invocation section in the Dynamic LINQ Library documentation. 

     

    At last, the CopyToDataTable() should be executed on IEnumerable<DataRow>, however, qtest2 gets a list of anonymous type.  If you want to convert the anonymous type list into DataTable, please refer to http://unboxedsolutions.com/sean/archive/2009/06/06/15961.aspx.  

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com

     

    P.S.   I will move this thread to ADO.NET DataSet forum. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, April 7, 2010 5:08 AM
    Moderator
  • Hi Steve,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, April 12, 2010 6:16 AM
    Moderator
  • Hi Lingzhi Sun,

    I am a LINQ newbie and am having a similar situation as above. Now I believe if I use typed datasets it should solve my problem but was wondering if there is another solution.

    At the moment I receive a couple of untyped DataTables from a web service into my function and add them to a DataSet, I use LINQ to query those tables and would like to take the results, and return them to a Form and display it on a DataGridView. However I don't understand how to return a List(Of <anonymous type>) List using the .ToList() method of the LINQ query. Or converting the result of .ToList() to a DataView or DataTable etc.

    I had a read of the link you redirected Steve (http://unboxedsolutions.com/sean/archive/2009/06/06/15961.aspx.) However this is in C# and was wondering if there is a VB version or another solution?

    I get the error:  Value of type 'System.Collections.Generic.List(Of <anonymous type>)' cannot be converted to 'System.Collections.Generic.List(Of Object)'.

    Any help would be much appreciated

    cheers

    Monday, June 21, 2010 12:21 AM
  • Could you please open a new thread to discuss it?  

     

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, June 21, 2010 8:25 AM
    Moderator