locked
Group By RRS feed

  • Question

  • User271039688 posted

    Hello,

    Could anyone be kind enough to let me know how I could group by a field named ApplicantCode using the following code please? I'm really struggling to get my head around this!

        var applicants = (from c in context.tblapplicants
                                  where c.Forename != null
                                  join e in context.tblapplicant_notes on c.ApplicantCode equals e.ApplicantCode
                                  orderby e.NoteDate descending
                                  select c).ToList();
    
                model.Applicants = applicants;

    Wednesday, July 31, 2019 3:03 PM

Answers

  • User1520731567 posted

    Hi webdeveloper2016,

    Thank you but still not fully understanding this I'm afraid. Is it not possible to assign a grouped list to an IEnumerable model object (to be then used for a webgrid)?

    I don't recommend that you use the group by clause to filter data in the background.

    Unless the data you send to the webgrid meets the requirements, you need to spend a lot of time on the custom data.

    I google similar informations,here and here,someone said webgrid doesn't allow you to do grouping.

    So,I suggest you could group data in client end with canSort attribute (webgrid's own properties).

    For example:

    @{
            var grid = new WebGrid(source: ViewBag.list, canPage: true,rowsPerPage: 10);
            grid.Pager(WebGridPagerModes.NextPrevious);
            @MvcHtmlString.Create(
        @grid.GetHtml(htmlAttributes: new { id = "gridT", @class = "Grid" },
    columns: grid.Columns(
      grid.Column(columnName :"Id", header: "Id",canSort: true),//add canSort: true on the field which you want to group by
      grid.Column("field1", "field1", canSort: true)
    ....
    }

    How it works:

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 6, 2019 9:54 AM

All replies

  • User-474980206 posted

    first of all group by will return a special group by collection. if you iterate the collection, there is an item per key, the item has a .Key value, and can be enumerated to  get the item values.

    so in your case its:

    // to pass group by to view create a new class or use tupple
    public ApplicationViewModel
    {
       public string ApplicationCode {get; set;}
       public List<Applicant> Applicants {get; set;} // guessing class name
    }
    
    var applicants = (
       from c in context.tblapplicants
       where c.Forename != null
       join e in context.tblapplicant_notes 
          on c.ApplicantCode equals e.ApplicantCode
       orderby e.NoteDate descending
       select c).ToList();
    
    // create List<ApplicationViewModel>
    var applicantGroups = (
       from c in applicants
       group c by c.ApplicationCode into g
       order g.key
       select new ApplicationViewModel
       {
           ApplicationCode = g.Key,
           Applicants = g.ToList()
       }).ToList();
    
    
    foreach  (var group in applicantGroups)
    {
       var applicationCode = group.ApplicationCode;
       
       foreach (var applicant in group.Applicants)
       { 
          // process applicant
       }
    }
    
    



    Wednesday, July 31, 2019 6:30 PM
  • User1520731567 posted

    Hi webdeveloper2016,

    You want to group by the query,you could like:

                var applicants  = (from c in context.tblapplicants
    where c.Forename != null join d in context.tblapplicant_notes on c.ApplicantCode equals d.ApplicantCode
    orderby e.NoteDate descending group c by c.ApplicantCode into grp select new { xxx1= grp.Key,xxx2=grp.ToList()}).ToList();

    or you could also use .Join() and .GroupBy() to filter,more details,you could refer to:

    https://stackoverflow.com/a/6606440/11741806

    Best Regards.

    Yuki Tao

    Thursday, August 1, 2019 5:16 AM
  • User271039688 posted

    Hi Yuki,

    Thank you for your response.

    How would I actually assigned the applicants list to my IEnumerable applicants model as I need to display all fields in a webgrid?

    model.Applicants = applicants;

    Thursday, August 1, 2019 10:00 AM
  • User-474980206 posted
    The group by builds a collection of group keys. Each item if the group collection, has a collection of values. If displaying in a grid, normally you would use nested grids. The main grid would have a row for each group by key, and in this row would be another grid of the group key values.

    You you want a only one row per group key, then instead of a list of key values, you need to aggregate each column with a sum, min or max as you would in a sql group by.
    Thursday, August 1, 2019 2:17 PM
  • User271039688 posted

    Thank you but still not fully understanding this I'm afraid. Is it not possible to assign a grouped list to an IEnumerable model object (to be then used for a webgrid)?

    Friday, August 2, 2019 9:21 AM
  • User1520731567 posted

    Hi webdeveloper2016,

    Thank you but still not fully understanding this I'm afraid. Is it not possible to assign a grouped list to an IEnumerable model object (to be then used for a webgrid)?

    I don't recommend that you use the group by clause to filter data in the background.

    Unless the data you send to the webgrid meets the requirements, you need to spend a lot of time on the custom data.

    I google similar informations,here and here,someone said webgrid doesn't allow you to do grouping.

    So,I suggest you could group data in client end with canSort attribute (webgrid's own properties).

    For example:

    @{
            var grid = new WebGrid(source: ViewBag.list, canPage: true,rowsPerPage: 10);
            grid.Pager(WebGridPagerModes.NextPrevious);
            @MvcHtmlString.Create(
        @grid.GetHtml(htmlAttributes: new { id = "gridT", @class = "Grid" },
    columns: grid.Columns(
      grid.Column(columnName :"Id", header: "Id",canSort: true),//add canSort: true on the field which you want to group by
      grid.Column("field1", "field1", canSort: true)
    ....
    }

    How it works:

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 6, 2019 9:54 AM