How to dynamically group a list depending on role in asp.net mvc.

Question

Here is my scenario: We would like to have a page listing donors, depending on the user viewing the page we would like to group by the donor's giving level, or just their sort name. The twist that is throwing me is that we would like to group the Anonomous Donors and give a count based on the grouping.

In my controller I have

 [HttpGet]
        public ActionResult Society(string id)
        {
            var society = _db.ONLINEDR_DONOR_LIST
                .Include("ONLINEDR_DONORS")
                .Single(s => s.DONOR_LIST_ID == id);

            var donors = _db.ONLINEDR_DONORS
                .Where(d => d.DONOR_LIST_ID == id)
                .OrderBy(d => d.SUBGROUP_SORT)
                .ThenBy(d => d.SORT_NAME)
                .ToList();
            if (User.Identity.Name == "public")
            {
                //First off, check to make sure the list is viewable.
                if (society.PUBLIC_IND == "N")
                    RedirectToAction("Index", "Home");
                donors = _db.ONLINEDR_DONORS
                    .Where(d => d.DONOR_LIST_ID == id)
                    .OrderBy(d => d.SORT_NAME)
                    .ToList();
            }
            var viewModel = new SocietyDetailViewModel()
            {
                Society = society,
                Donors = donors
            };

            return View(viewModel);
        }

I would like to have something like

donors = _db.ONLINEDR_DONORS
     .Where(d => d.DONOR_LIST_ID == id)
     .GroupBy(d => d.SORT_NAME)
     .ToList();

Which I can pass to my view, and then somehow show in the view

<% if (Model.donor.GroupedByItemCount > 1) { %>
<%: Model.donor.GroupedByItemCount %>
<% } %

(I am still new to asp.net MVC and LINQ so any helpful references to explain what I am doing wrong would be appreciated as well).

Thanks so much.

Answer 1

In the declaration of the donors variable, the compiler can determine the type of donors to be List<Donor>

In the assignment within the desired code, donors must be a List<IGrouping<string, Donor>>

donors cannot simultaneously be both types.

---

Suppose you have this query:

List<IGrouping<string, Donor>> donors = _db.ONLINEDR_DONORS
  .Where(d => d.DONOR_LIST_ID == id)
  .GroupBy(d => d.SORT_NAME)
  .ToList(); 

This query is local and gives you the keys:

donors.Select(g => g.Key)

This query is mixed mode. A query is sent to the database for each item in the list to fetch its count. This is a potential performance problem.

donors.Select(g => g.Count())

This behavior is due to the difference between LinqToObjects groupby and sql's groupby.

• In sql's groupby, you get the key and the aggregates - no elemeents.
• In LinqToObjects, you get the key and the elements of the group - and can compute the aggregates from the elements.

---

Suppose you have this query:

List<IGrouping<string, Donor>> donors = _db.ONLINEDR_DONORS
  .Where(d => d.DONOR_LIST_ID == id)
  .ToList()
  .GroupBy(d => d.SORT_NAME)
  .ToList();

In the above query, the records are first hydrated, and then grouped locally. All queries on the result are local.

This query shapes the result data from IGrouping<string, Donor> to GroupShape. GroupShape is some class you make up that has SortName and Count properties.

donors.Select(g => new GroupShape()
{
  SortName = g.Key,
  Count = g.Count()
});

---

Suppose you have this query:

List<GroupShape> donors = _db.ONLINEDR_DONORS 
  .Where(d => d.DONOR_LIST_ID == id) 
  .GroupBy(d => d.SORT_NAME) 
  .Select(g => new {SortName = g.Key, Count = g.Count()})
  .ToList()
  .Select(x => new GroupShape()
  {
    SortName = x.SortName,
    Count = x.Count
  }).ToList();

Here, the grouping and counting are done in the database. Each row is first hydrated into an anonymous instance and then copied into an instance of GroupShape (a class you make up).