Linq Group By Query not cutting it

Question

The venerable "group by" option nicely handles multiple group by fields. The Problem : What if I want to include field(s) in the result set (that don't need to be grouped by) that are not in the group by (grp) object. Based on the gazillion examples out there, it doesn't appear to handle the situation.

In the below example all of the group by fields are nicely included in the result set using the grp object. OK, so let's say "addresses" has a "street" or "phone" that I simply want to include in the result without grouping. Options?

var qry = from addr in addresses
          group addr by new { addr.city, addr.state, addr.country } into grp
          select new
          {
            city = grp.Key.city,
            state = grp.Key.state,
            country = grp.Key.country,
            count = grp.Count(),
          };

Each group in a query would/could have multiple `Street` and `Phone`, now If you want to select a single one out of them, which one you want ? You can select the first one like `Phone = grp.First().Phone`, but would that actually represents the records you are looking for ? — Habib, Aug 21 '14 at 17:15
Can you provide some code to show what you'd like the result element type to look like? It's hard to tell where you expect the "street" or "phone" to be coming from. — Steve Ruble, Aug 21 '14 at 19:44

score 0 · Answer 1 · answered Aug 21 '14 at 17:18

Elements not in the grouping key can be selected from the group itself:

var qry = from addr in addresses
          group addr by new { addr.city, addr.state, addr.country } into grp
          select new
          {
            city = grp.Key.city,
            state = grp.Key.state,
            country = grp.Key.country,
            count = grp.Count(),
            phone = grp.Select(a => a.Phone).FirstOrDefault()
          };

You'll have to consider which "aggregate" to use (e. g. First, Last, Min, Max, FirstOrDefault, ...) depending on the logic you are going for and the query provider you are using. For example, EF supports FirstOrDefault, but not First

score 0 · Answer 2 · answered Aug 21 '14 at 17:20

You might not need to group by them, but anything that has a 1:1 relationship with the grouping is effectively being grouped anyway. So just group by the Phone as well, and you will have identical results.

var qry = from addr in addresses
      group addr by new { addr.city, addr.state, addr.country, addr.Phone } into grp
      select new
      {
        city = grp.Key.city,
        state = grp.Key.state,
        country = grp.Key.country,
        count = grp.Count(),
        phone = grp.Key.Phone
      };

Linq Group By Query not cutting it

2 Answers2