Group By

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;

Answer 1

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
   }
}

Answer 2

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

Answer 3

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;

Answer 4

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.

Answer 5

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)?

Answer 6

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