locked
[VB.NET] Dynamicly group by in LINQ RRS feed

  • Question

  • I`m new with LINQ. I wan`t to create a dynamic group by. Now i have the code below and it results an array of weight with number of records grouped by weight. But i can create a dynamic object, with this model on the first row but now i want to be able to select from the model a dynamic column. This time it`s weight but it can be another property. How can i dynamicy group by the input i have the propertyname inside a string but don`t know how to group by a column name whitch is a string.

    ' Data opsetllen

     

    Dim tModel As IModel = Activator.CreateInstance(GetType(T))

     

    Dim tData As IDictionary(Of Object, Integer) = _

    ( _

     

    From tdier In tModel.mGetAll _

     

    Group tdier By tdier.pGewicht _

     

    Into tcount = Count() _

     

    Select pGewicht, tcount _

    ).todictionary(

    Function(tresult) tresult.pGewicht, Function(tresult) tresult.tcount)

    • Moved by Liliane Teng Thursday, January 27, 2011 2:07 AM (From:Visual Basic Language)
    Wednesday, January 26, 2011 10:33 AM

Answers

  • Hi,

    Thank you very much for sending me thd demo project!

    I think your codes look good.  Dynanic LINQ is really hard to use, especially the type conversion part.  However, I think you make it easier.  :)  

    JUST NOTE: Dynamic LINQ Library also has a dynamic GroupBy method to use:
    =====================================================================
    Dim query = tList.AsQueryable().GroupBy(Me._groupBy.pInputProperty, "New( " & Me._groupBy.pInputProperty & " As GROUP, " & Me._majorAxis.pInputProperty & " AS MAJOR, " & Me._minorAxis.pInputProperty & " AS MINOR)")
    =====================================================================

    It can also group by multiple properties using anonymous type expression like .GroupBy("new(AA, BB)", "it").   I used it in this thread about dynamic LINQ to XML queries.
    http://social.msdn.microsoft.com/Forums/en-US/xmlandnetfx/thread/7f60e779-165b-4447-9223-14cd911d7ac7

     

    For another question about group like <75, (75, 100) and >100, I believe we need three dynamic WHERE queries instead of Group By query. 

     

    The last question about extending DynamicLinq file:  Yes, of course we can, but we'd better improve/extend it instead of removing its codes.  As you said, the DynamicLinq file is really complicated.  I would recommend you keep all the codes to avoid any unexpected issues.   If you want to only use GroupBy and Select dynamic method.  You may consider removing other extension methods under the DynamicQueryable module, like Any, Count, OrderBy, Take, Skip and Where.   However, I don' think these methods hold many lines of codes.  More important, they can be very helpful when you extend your own dynamic LINQ methods.   For the rest codes in the DynamicLinq file, I think they cannot be removed easily. 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by BS Software Thursday, February 10, 2011 1:11 PM
    Tuesday, February 8, 2011 7:38 AM
  • I did lose the previous version where i had this problem. After reading the "http://blogs.msdn.com/b/mitsu/archive/2008/02/07/linq-groupbymany-dynamically.aspx" again i figured it out (missed the DynamicLinq, looked it up on google) I did get out of this problem by using DynamicLinq.vb from the internet.

    Below a part of the code where the data reading is in. I also did send you a e-mail.

    =============== CONTENT.VB ================

    ' GetData

     

    Public Function mGetData() As IList(Of DataGroup)

     

    ' Define result

     

    Dim tResult As IList(Of DataGroup) = Nothing

     

    ' Initialize controller

     

    Dim tController As Controller = New Controller()

     

    Dim tList As IList(Of TObject) = tController.mGetList(Of TObject)()

     

    ' Filter

     

    Dim tQueryA As IList(Of Object) = DynamicQueryable.Select(tList.AsQueryable, "New( " & Me._groupBy.pInputProperty & " As GROUP, " & Me._majorAxis.pInputProperty & " AS MAJOR, " & Me._minorAxis.pInputProperty & " AS MINOR)").Cast(Of Object)().ToList()

     

    Dim tQueryB As IList(Of DataRecord) = (From t In tQueryA _

     

    Select New DataRecord With {.pGROUP = t.GROUP, .pMAJOR = t.MAJOR, .pMINOR = t.MINOR}).ToList

    tResult = (

    From q In tQueryB _

     

    Group By q.pGROUP Into t = Group _

     

    Select New DataGroup With {.pNAME = pGROUP, .pRECORDS = t}).ToList

     

    ' Return result

     

    Return tResult

     

    End Function

    ' ##### PRIVATE FUNCTIONS ##### '

     

    ' GetFullPropertyPath

     

    Public Function mGetFullPropertyPath(Of TProperty)(ByVal iExpression As Expression(Of Func(Of TObject, TProperty))) As String

     

    ' Define result

     

    Dim tResult As String = Nothing

     

    ' Basis member

     

    Dim tMember As MemberExpression = iExpression.Body

    tResult =

    CType(tMember.Member, PropertyInfo).Name

     

    ' Members doorlopen

     

    Do While tMember.Expression.GetType Is GetType(MemberExpression)

    tMember = tMember.Expression

    tResult =

    CType(tMember.Member, PropertyInfo).Name & "." & tResult

     

    Loop

     

    ' Return result

     

    Return tResult

     

    End Function

     

    ' GetSelector

     

    Public Function mGetSelector(ByVal tProperty As String) As Func(Of TObject, Object)

     

    ' Define result

     

    Dim tResult As Func(Of TObject, Object) = Nothing

     

    ' Setup Selector

     

    Dim tLamba As LambdaExpression = DynamicExpression.ParseLambda(GetType(TObject), GetType(Object), tProperty)

    tResult = tLamba.Compile

     

    ' Return result

     

    Return tResult

     

    End Function

     

    End

     

    Class

    ' DataGroup

    Public

     

    Class DataGroup

     

    Public pNAME As Object

     

    Public pRECORDS() As DataRecord

    End

     

    Class

    ' DataRecord

    Public

     

    Class DataRecord

     

    Public pGROUP As Object

     

    Public pMAJOR As Object

     

    Public pMINOR As Object

    End

     

    Class

    ======================= DEFAULT.ASPX.VB =======================

    ' Fill

     

    Private Sub mFill(Of TObject)(ByVal iContent As Content(Of TObject))

     

    ' Load data

     

    For Each tGroup In iContent.mGetData()

     

    ' Convert records to dictionary

     

    Dim tDict As New Dictionary(Of Object, Object)

     

    For Each tRecord In tGroup.pRECORDS

    tDict.Add(tRecord.pMAJOR, tRecord.pMINOR)

     

    Next

     

    ' Create serie

     

    Dim tSerie As Series = New Series(tGroup.pNAME)

    tSerie.Points.DataBind(tDict,

    "Key", "Value", "")

     

    ' Add serie

    Chart1.Series.Add(tSerie)

     

    Next

     

    End Sub

    • Marked as answer by BS Software Thursday, February 10, 2011 1:11 PM
    Friday, February 4, 2011 8:11 AM

All replies

  • Hello BS Software,

    Thanks for your post.

    I moved this thread to LINQ to SQL forum where you may get more better and quicker support because more this aspect experts live there. Thanks for your understanding.

    Have a nice day.

    Best regards


    Liliane Teng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 27, 2011 2:10 AM
  • Hi BS Software,

    Welcome to LINQ to SQL forums.

    According to your description, You want to dynamicly Group your records, You can refer this link: http://blogs.msdn.com/b/mitsu/archive/2008/02/07/linq-groupbymany-dynamically.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, January 27, 2011 6:15 AM
  • Thanks for moving the thread and thanks for the response.
    I did came futher, my object is now dynamic but the column on whitch i`m sorting out isn`t dynamic.
    Don`t check the names of the parameters because i`m only testing this functionality and building it in a application.

    I have read something about a Func(of dynamic, dynamic) in .NET 4 but i don`t have .NET 4 for this application. We are still working on .NET 3.5.

    The data i have:
    ObjectInput = Type of object whitch is requested
    PropertyInput = PropertyInfo of the property witch is requested

    In this example i use the keyword T witch is the input of the function with the same object as objectinput but i want to replace this by the objectinput i have above.

    Can you tell me how to set dynamic keywords? How can i set (Of ...) dynamicly?

     

    ' Data opstellen

     

    Dim tModel As IModel(Of T) = Activator.CreateInstance(GetType(T))

     

    ' Kolommen

     

    Dim tParameterExpression As ParameterExpression = Expression.Parameter(GetType(T), iXAs.pInputProperty.Name)

     

    Dim tMemberExpression As MemberExpression = Expression.PropertyOrField(tParameterExpression, iXAs.pInputProperty.Name)

     

    Dim tLamba As LambdaExpression = Expression.Lambda(tMemberExpression, tParameterExpression)

     

    Dim tType As Type = iXAs.pInputProperty.PropertyType

     

    Dim tTest As Func(Of T, Decimal) = CType(tLamba.Compile, Func(Of T, Decimal))

     

    Dim tList As IList(Of T) = tModel.mGetAll()

     

    Dim tquer As IDictionary(Of Decimal, T()) = tModel.mGetAll.GroupBy(tTest).ToDictionary(Function(Result) Result.Key, Function(Data) Data.ToArray)

     

    Thursday, January 27, 2011 7:47 AM
  • I am sorry, I am not a VB expert at all. But I know C#. How about using a generic method? You have to be careful with this though. I would expect errors using the wrong Generic Type.

    Public Sub GetArray(Of T)()

    //Do your work using T

    End Sub
    Monday, January 31, 2011 3:04 PM
  • Thanks for your time.

    I wan`t to have a user control where i can put in the object and the three arguments like Object Animal, MajorAxis = Weight, MinorAxis = Meat % and the group by action on every month like GroupBy = Date On Month.

    But when using set functions i can`t define the variable like the types they supose to be. But when calling it from a constructor i need to define every column type myself like new Chart(Of Animal, Decimal, Decimal, Date) but i just want to put in the properties because those can be different when they want to see another chart they have combined.

    Maby i`m looking at the wrong directorion. Is there inside Microsoft Chart something to put data in and let users deside witch combination they would like to see in the chart?

    Thanks again.

    Monday, January 31, 2011 3:30 PM
  • Hi,

    It's a very interesting question.  First, let me clarify something.  It seems that you want dynamic LINQ to Objects query instead of LINQ to SQL query since no database calls are involved here, all the query collections or entities are in-memory objects, right? 

    Besides, I believe Alan's and LitEnders' posts are both very helpful.   Although the dynamic LINQ library is generally for LINQ to SQL or LINQ to Entities queries, it can be used in LINQ to Objects query as well. 
    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
    http://blogs.msdn.com/b/mitsu/archive/2008/02/07/linq-groupbymany-dynamically.aspx

    For the query return type, I agree that using generic type is a good way to go because we cannot determine the type during design time.  So let the method caller to decide what type to return.  

    It seems you are using Microsoft Chart controls, could you please make your scenario be more detailed and provide us some sample codes?  If you have some demo to share, please directly ping me at misun@microsoft.com.   I cannot say I can 100% figure out the issue, but I will do my best to help you.  :)   

    Have a nice day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 2, 2011 1:55 AM
  • Hello,

    Thanks for the response. I want to use all data from a object based system. Near this i`m busy working on a ORM for our applications. So you no something about why i want to use this input.

    A simple example of how i would like to use the chart. If i have the base i can expand in later with minimum and maximum values, preferd values, etc.

    == TABLE "SalesVolume" ==
    [Id -- Volume -- Date]
    #1 -- 50.000 -- 01-01-2009
    #2 -- 60.000 -- 02-01-2009
    #3 -- 45.000 -- 03-01-2009
    .... -- .......... -- ............
    #366 -- 40.000 -- 01-01-2010
    #367 -- 70.000 -- 02-01-2010
    #368 -- 65.000 -- 03-01-2010

    == CODE ==
    I`m using a UserControl with a chart inside so i can set the content in there. In this content i would like to set the X, Y and Z axis. So in code this would meen.

    Dim tContent As new Content(of SalesVolume)
    tContent.mSetXAxis(function(tSalesVolume) tSalesVolume.Date.Month)
    tContent.mSetYAxis(function(tSalesVolume) tSalesVolume.Volume)
    tContent.mSetZAxis(function(tSalesVolume) tSalesVolume.Date.Year)

    == RESULT ==
    The output i wan`t to see with this content is on the bottom the months, in every month the total volume grouped by year.

    The chart wil take the data out of the Content object by a function called mGetData() an on the moment it calles the mGetData() the query must be executed, this is a link query on a IList(of ContentObject). But now you can`t execute things like From tContent In tList Group By tProperty.

    I did use the LinqGroupByMany dynamic from this thread and this made it possible to get the data out. But when i only want to set a X and Y axis i would like to use a Dictionary(Of XAsType, YAsType) for example but i can`t execute this because i can`t set the dictonary dynamicly. The chart want`s to know witch data comes in.

     

    I replaced all of this by combining a SQL query from the input. Like i would do later with a ORM but when i want to use a filter on a IList(of SalesVolume) i can`t get the dynamic output like Dictonary(Of Integer, Decimal) because the generic type is dynamicly. This is the whole isue. So if you now a sollution i would appriciate it.

    Hopefully it i made clear what the isue is?

    Wednesday, February 2, 2011 11:07 AM
  • Hi,

    Thanks a lot for following up!   I can understand most of your scenario, but I think it's really hard to realize such a dynamic request.  If it is convenient for you to build up a small demo of the issue, could you please send us one?  It will be very helpful for us to do some further investigation.   We may involve some more senior engineer to help you.   My mail: misun@microsoft.com.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 3, 2011 3:23 AM
  • I did lose the previous version where i had this problem. After reading the "http://blogs.msdn.com/b/mitsu/archive/2008/02/07/linq-groupbymany-dynamically.aspx" again i figured it out (missed the DynamicLinq, looked it up on google) I did get out of this problem by using DynamicLinq.vb from the internet.

    Below a part of the code where the data reading is in. I also did send you a e-mail.

    =============== CONTENT.VB ================

    ' GetData

     

    Public Function mGetData() As IList(Of DataGroup)

     

    ' Define result

     

    Dim tResult As IList(Of DataGroup) = Nothing

     

    ' Initialize controller

     

    Dim tController As Controller = New Controller()

     

    Dim tList As IList(Of TObject) = tController.mGetList(Of TObject)()

     

    ' Filter

     

    Dim tQueryA As IList(Of Object) = DynamicQueryable.Select(tList.AsQueryable, "New( " & Me._groupBy.pInputProperty & " As GROUP, " & Me._majorAxis.pInputProperty & " AS MAJOR, " & Me._minorAxis.pInputProperty & " AS MINOR)").Cast(Of Object)().ToList()

     

    Dim tQueryB As IList(Of DataRecord) = (From t In tQueryA _

     

    Select New DataRecord With {.pGROUP = t.GROUP, .pMAJOR = t.MAJOR, .pMINOR = t.MINOR}).ToList

    tResult = (

    From q In tQueryB _

     

    Group By q.pGROUP Into t = Group _

     

    Select New DataGroup With {.pNAME = pGROUP, .pRECORDS = t}).ToList

     

    ' Return result

     

    Return tResult

     

    End Function

    ' ##### PRIVATE FUNCTIONS ##### '

     

    ' GetFullPropertyPath

     

    Public Function mGetFullPropertyPath(Of TProperty)(ByVal iExpression As Expression(Of Func(Of TObject, TProperty))) As String

     

    ' Define result

     

    Dim tResult As String = Nothing

     

    ' Basis member

     

    Dim tMember As MemberExpression = iExpression.Body

    tResult =

    CType(tMember.Member, PropertyInfo).Name

     

    ' Members doorlopen

     

    Do While tMember.Expression.GetType Is GetType(MemberExpression)

    tMember = tMember.Expression

    tResult =

    CType(tMember.Member, PropertyInfo).Name & "." & tResult

     

    Loop

     

    ' Return result

     

    Return tResult

     

    End Function

     

    ' GetSelector

     

    Public Function mGetSelector(ByVal tProperty As String) As Func(Of TObject, Object)

     

    ' Define result

     

    Dim tResult As Func(Of TObject, Object) = Nothing

     

    ' Setup Selector

     

    Dim tLamba As LambdaExpression = DynamicExpression.ParseLambda(GetType(TObject), GetType(Object), tProperty)

    tResult = tLamba.Compile

     

    ' Return result

     

    Return tResult

     

    End Function

     

    End

     

    Class

    ' DataGroup

    Public

     

    Class DataGroup

     

    Public pNAME As Object

     

    Public pRECORDS() As DataRecord

    End

     

    Class

    ' DataRecord

    Public

     

    Class DataRecord

     

    Public pGROUP As Object

     

    Public pMAJOR As Object

     

    Public pMINOR As Object

    End

     

    Class

    ======================= DEFAULT.ASPX.VB =======================

    ' Fill

     

    Private Sub mFill(Of TObject)(ByVal iContent As Content(Of TObject))

     

    ' Load data

     

    For Each tGroup In iContent.mGetData()

     

    ' Convert records to dictionary

     

    Dim tDict As New Dictionary(Of Object, Object)

     

    For Each tRecord In tGroup.pRECORDS

    tDict.Add(tRecord.pMAJOR, tRecord.pMINOR)

     

    Next

     

    ' Create serie

     

    Dim tSerie As Series = New Series(tGroup.pNAME)

    tSerie.Points.DataBind(tDict,

    "Key", "Value", "")

     

    ' Add serie

    Chart1.Series.Add(tSerie)

     

    Next

     

    End Sub

    • Marked as answer by BS Software Thursday, February 10, 2011 1:11 PM
    Friday, February 4, 2011 8:11 AM
  • Hi,

    Thank you very much for sending me thd demo project!

    I think your codes look good.  Dynanic LINQ is really hard to use, especially the type conversion part.  However, I think you make it easier.  :)  

    JUST NOTE: Dynamic LINQ Library also has a dynamic GroupBy method to use:
    =====================================================================
    Dim query = tList.AsQueryable().GroupBy(Me._groupBy.pInputProperty, "New( " & Me._groupBy.pInputProperty & " As GROUP, " & Me._majorAxis.pInputProperty & " AS MAJOR, " & Me._minorAxis.pInputProperty & " AS MINOR)")
    =====================================================================

    It can also group by multiple properties using anonymous type expression like .GroupBy("new(AA, BB)", "it").   I used it in this thread about dynamic LINQ to XML queries.
    http://social.msdn.microsoft.com/Forums/en-US/xmlandnetfx/thread/7f60e779-165b-4447-9223-14cd911d7ac7

     

    For another question about group like <75, (75, 100) and >100, I believe we need three dynamic WHERE queries instead of Group By query. 

     

    The last question about extending DynamicLinq file:  Yes, of course we can, but we'd better improve/extend it instead of removing its codes.  As you said, the DynamicLinq file is really complicated.  I would recommend you keep all the codes to avoid any unexpected issues.   If you want to only use GroupBy and Select dynamic method.  You may consider removing other extension methods under the DynamicQueryable module, like Any, Count, OrderBy, Take, Skip and Where.   However, I don' think these methods hold many lines of codes.  More important, they can be very helpful when you extend your own dynamic LINQ methods.   For the rest codes in the DynamicLinq file, I think they cannot be removed easily. 

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by BS Software Thursday, February 10, 2011 1:11 PM
    Tuesday, February 8, 2011 7:38 AM
  • Hi,

    Could you please tell us how is the problem now? 

    If you need any further assistance, please feel free to let me know.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 10, 2011 9:47 AM
  • Hello,

    Thanks for youre response and time. I have made the demo but didn`t use the functionality yet.
    I will use youre advise when building it into the complete application.

    Right now i`m searching out other parts of the project we need.

    Thanks again, for me the problem is solved for now.

    Kinds regards,
    Jos

    Thursday, February 10, 2011 1:11 PM
  • It's my pleasure, Joe!  :)

    Good day!


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 10, 2011 2:01 PM