none
creating html table with many levels of data RRS feed

  • Question

  • Hi,  I expect that the title of the question is not misleading. Anyway here's my scenario:

    I'm new to Linq, and I was requested to create a table that comes from the same query but has many levels:

    First I group the information by company, type, subtype, class and finally take the sum of time.

    The image below shows what I am trying to acomplish:

    What I did to get the html table was to create a method that returns a DataSet that has many datatables.  Each table contains a linq subset of every subgroup (first group: company, type, subtype, class and finally take the sum of time,  second group: first group- grouped by type, subtype, class and sum of time and so on until I had all groups.

    I'm having performance issues and the question is related about the way I'm taking the information from my datalayer where resides the method that returns the dataset.  Should I only get the main query from the datalayer method and perform the consecutive subqueries on page codebehind?   What should I do?  

    Thanks in advance

     edotom


    cognosoft

    Friday, January 17, 2014 8:17 PM

Answers

  • I suggest to make a stored procedure with grouping sets and pivot . You can then call your stored proc from linq.
    • Proposed as answer by JS PADOAN Saturday, January 18, 2014 8:01 PM
    • Marked as answer by Fred BaoModerator Monday, January 27, 2014 9:12 AM
    Saturday, January 18, 2014 8:01 PM
  • Hello edotom,

    >> Should I only get the main query from the datalayer method and perform the consecutive subqueries on page codebehind?   What should I do? 

    How have you defined your LINQ queries? For improving the performance, you can do what PADOAN has said: To make a stored procedure with grouping sets and pivot. You can then call your stored proc from linq.

    And if your LINQ queries returns the List<T> or IEunmerable<T> collections, please change it to IQueryable<T>, then it will execute the generated T-SQL in database directly rather than load all data into in-memory and then do a filter operation in it.

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 20, 2014 2:21 AM
    Moderator

All replies

  • I suggest to make a stored procedure with grouping sets and pivot . You can then call your stored proc from linq.
    • Proposed as answer by JS PADOAN Saturday, January 18, 2014 8:01 PM
    • Marked as answer by Fred BaoModerator Monday, January 27, 2014 9:12 AM
    Saturday, January 18, 2014 8:01 PM
  • Hello edotom,

    >> Should I only get the main query from the datalayer method and perform the consecutive subqueries on page codebehind?   What should I do? 

    How have you defined your LINQ queries? For improving the performance, you can do what PADOAN has said: To make a stored procedure with grouping sets and pivot. You can then call your stored proc from linq.

    And if your LINQ queries returns the List<T> or IEunmerable<T> collections, please change it to IQueryable<T>, then it will execute the generated T-SQL in database directly rather than load all data into in-memory and then do a filter operation in it.

    If I misunderstand, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, January 20, 2014 2:21 AM
    Moderator
  • Hi, thanks for answering.  What I do in my datalayer is make the first "big" query, then convert it to a list and then I perform the next linq queries based on that first group.  Every consecutive query is converted to a list and then to a datatable. Finally I assign all of those to a dataset that is in turn returned from datalayer method.  If I understood correctly, IQueryable performs its queries directly to the database.  I think that in order to do that I should change the stored procedure and keep the method with the same logic which is return all subqueries in datatables inside a dataset.  Is that correct?  

    cognosoft

    Tuesday, January 21, 2014 1:21 PM
  • Hi, thanks for answering.  I'll give it a try.  Regards

    cognosoft

    Tuesday, January 21, 2014 1:22 PM
  • Hi,

    >> If I understood correctly, IQueryable performs its queries directly to the database.  I think that in order to do that I should change the stored procedure and keep the method with the same logic which is return all subqueries in datatables inside a dataset.  Is that correct? 

    Yes, you are right. You can have a try.

    Friday, January 24, 2014 6:03 AM