Group Records by Property to be put in a table

Question

If I have a list of objects like so:

ID    |    First Name    |    Location    |    Division    |    Age    |
------------------------------------------------------------------
 1          John              Building1         ISS              22
 2          Alex              Building1         ISS              30
 3          Chris             Building1         HR               41
 4          Mary              Building1         HR               25
 5          Monica            Building1         HR               46
 6          Ian               Building2         Accounting       37
 7          John              Building2         Accounting       32
 8          Justin            Building2         Sales            22
 9          Trevor            Building2         Sales            26
10          Shelley           Building2         Sales            34
11          Troy              Building1         Network          25

THIS IS JUST A SAMPLE OF WHAT I HAVE... I HAVE A LOT MORE

My goal is to create a table each time if the location differs OR the division differs.

So I want my outcome to be:

Table 1

Building 1 - ISS // table header
------------------------------------------------------------------
// table body
 John              Building1         ISS              22
 Alex              Building1         ISS              30

Table 2

Building 1 - HR// table header
------------------------------------------------------------------
// table body
 Chris              Building1         HR              41
 Mary               Building1         HR              25
 Monica             Building1         HR              46

Table 3

Building 2 - Accounting // table header
------------------------------------------------------------------
// table body
 Ian              Building2         Accounting              37
 John             Building2         Accounting              32

Table 4

Building 2 - Sales // table header
------------------------------------------------------------------
// table body
 Justin              Building2         Sales              22
 Trevor              Building2         Sales              26
 Shelley             Building2         Sales              34

At the top of my view I have:

@model List<ProjectName.Models.ViewModels.DetailsViewModel>

I would like to use only 1 foreach loop, because like i stated above there are a lot more locations and division and I would prefer not to have to create a foreach loop for every different location and division.

How can this be done?

Any help is appreciated.

UPDATE

I have added an extra in the table above. That person is a part of Building1 location, but since there is only one of him, instead of having 1 whole table generated for just one person I would like to add him to the group Building1 ISS.

Is there some kind of conditional statement that I can do to do this?

UPDATE 2

ID    |    First Name    |    Location    |    Division    |    Age    |
------------------------------------------------------------------
 1           John             Building1     Administration      40
 2           Melissa          Building1     Division1           32
 3           Sean             Building1     Division1           26
 4           Timothy          Building1     Division1           33
 5           Joe              Headquarters  Administration      33
 5           Jason            Headquarters  Staff               33
 5           Greg             Headquarters  Staff               33
 5           Brett            Headquarters  Staff               33
 1           Rob              Building2     Administration      40
 2           Nate             Building2     Division2           32
 3           Leslie           Building2     Division2           26
 4           Phil             Building2     Division2           33

Goal

I do not care about the Headquarters people.. if there is only 1 person in a division then so be it.. I only care about building1 and building2 people.

So I would like Rob added to Building2 Division2 group and John added to Building1 Division1 group.

Can a conditional statement be used here to check for count and a where statement to check for location name?

Answer 1

You need a query the records using a .GroupBy on both the Location and Division as keys.

Start with view models representing your view

public class MainVM
{
    public string Title { get; set; }
    public IEnumerable<ChildVM> Children { get; set; }
}
public class ChildVM
{
    public string FirstName { get; set; }
    public string Location { get; set; }
    public string Division { get; set; }
    public int Age { get; set; }
}

And to generate your model

var data = ... // your query to get the database records

IEnumerable<MainVM> model = data.GroupBy(x => new { x.Location, x.Division }).Select(y => new MainVM()
{
    Title = y.Key.Location + " - " + y.Key.Division,
    Children = y.Select(z => new ChildVM()
    {
        FirstName = z.FirstName,
        Location = z.Location,
        Division = z.Division,
        Age = z.Age
    })
});
return View(model);

and in the view

@model IEnumerable<MainVM>
....
@foreach(var table in Model)
{
    <table>
        <thead><tr><th colspan="4">@table.Title</th></tr></thead>
        <tbody>
            @foreach(var row in table.Children)
            {
                <tr>
                    <td>@row.FirstName</td>
                    .... // do you really need to repeat Location and Division which are already in the header?
                    <td>@row.Age</td>
                </tr>
            }
        </tbody>
    </table>
}

---

Based on your updated question, you would need to select all ChildVM records where the .Count() of MainVM.Children == 1, and concatenate then to the first MainVM

IEnumerable<MainVM> model = data.GroupBy(... // as above
model.First().Children = model.First().Children
    .Concat(q.Where(x => x.Children.Count() == 1).SelectMany(x => x.Children));

and then return only those MainVM who have .Children.Count > 1

model = model.Where(x => x.Children.Count() > 1);
return View(model);