Group linq results by value and group null or invalid values and do counts

Question

this is a follow on question to this question. I now would like to do some counts on the groupings.

Original Query: that excluded invalid zip codes did the following:

  List<DataSourceRecord> md = (from rst in QBModel.ResultsTable
        where (!String.IsNullOrWhiteSpace(rst.CallerZipCode) && rst.CallerZipCode.Length > 2)
        group rst by rst.CallerZipCode.Substring(0, 3) into newGroup
        orderby newGroup.Key
        select new DataSourceRecord()
        {
          State = newGroup.Select(i => i.CallerState).FirstOrDefault(),
          ZipCode = newGroup.Where(z => z.CallerZipCode.StartsWith(newGroup.Key)).Select(x => x.CallerZipCode.Substring(0, 3)).FirstOrDefault(),
          Calls = newGroup.Where(x => x.CallerZipCode.StartsWith(newGroup.Key) && x.CALL_ID > 0).Distinct().GroupBy(g => new { g.CallerZipCode, g.CTR_ID, g.CALL_ID }).Count(),
          Exposures = newGroup.Where(x => x.CallerZipCode.StartsWith(newGroup.Key) && x.CALL_ID > 0 && x.ExposureCount > 0).Distinct().GroupBy(x => new { x.CallerState, x.CTR_ID, x.CALL_ID }).Count()
        }).ToList();

New Example 1: Now with the new groupings including the invalid zip code groupings:

List<DataSourceRecord> newset = (from rst in QBModel.ResultsTable
  group rst by GetGroupRepresentation(rst.CallerZipCode) into newGroup
  select new DataSourceRecord()
  {
    State = newGroup.Select(i => i.CallerState).FirstOrDefault(),
    ZipCode = newGroup.Key,
    Calls = ???
    Exposures = ???
  }).ToList();

Grouping method:

private string GetGroupRepresentation(string zipCode)
{
    if (string.IsNullOrEmpty(zipCode) || zipCode.Length < 3)
        return "<null>";
    return zipCode.Substring(0,3);
}

New Example 2: I could also do the following I think:

List<DataSourceRecord> newset = (from rst in QBModel.ResultsTable
  group rst by rst.CallerZipCode == null || rst.CallerZipCode.Trim().Length < 3 ? "<null>" : rst.CallerZipCode.Substring(0, 3) into newGroup
  select new DataSourceRecord()
  {
    State = newGroup.Select(i => i.CallerState).FirstOrDefault(),
    ZipCode = newGroup.Key,
    Calls = ???
    Exposures = ???
  }).ToList();

I am trying to figure out what I need to change in the original query for the two counts for 'Calls' and 'Exposures' for the grouping in the new query. How and what is needed to accomplish this?

[Edit] Extension to same issue:

How to configure grouping with two or more properties. Is belwo

List<DataSourceRecord> 
    newset = (from rst in QBModel.ResultsTable
              group rst by GetGroupRepresentation(rst.CallerZipCode, rst.CallerState) into newGroup
              select new MapDataSourceRecord()
              {
                State = ToTitleCase(newGroup.Select(i => i.CallerState).FirstOrDefault()),
                StateFIPS = FipsForStateCD(newGroup.Select(i => i.CallerStateCD).FirstOrDefault()),
                ZipCode = newGroup.Key[0],
                Calls = newGroup.Where(x => x.CALL_ID > 0).Distinct().Count(),
                Exposures = newGroup.Where(x => x.CALL_ID > 0 && x.EXPO_ID > 0).Distinct().Count(),
                InfoRequests = newGroup.Where(x => x.CALL_ID > 0 && x.INFO_ID > 0).Distinct().Count(),
                Population = GetZipCode3Population(newGroup.Key[0])
              }).ToList();

The method:

    private string[] GetGroupRepresentation(string ZipCode, string State)
    {
      string ZipResult;
      string StateResult;
      if (string.IsNullOrEmpty(ZipCode) || ZipCode.Length < 3)
        ZipResult = "<null>";
      else
        ZipResult = ZipCode.Substring(0, 3);

      if (string.IsNullOrEmpty(State))
        StateResult = "<null>";
      else
        StateResult = State;

      return  new string[]{ ZipResult, State };
    }

Yes, the "New Example 2" does the same thing, however, I think its harder to understand whats going on. — Thomas D., Jul 29 '19 at 05:54

score 1 · Accepted Answer · answered Jul 29 '19 at 05:53

First about the calls:

Calls = newGroup.Where(x => x.CallerZipCode.StartsWith(newGroup.Key) && x.CALL_ID > 0).Distinct().GroupBy(g => new { g.CallerZipCode, g.CTR_ID, g.CALL_ID }).Count(),

As I understand, you want for the group the distinct number of calls where CALL_ID > 0. I don't understand why you create a new group with the zip code, the CTR_ID and the CALL_ID. If I have understood correctly, the Exposures are very similar.

List<DataSourceRecord> newset = (from rst in QBModel.ResultsTable
  group rst by GetGroupRepresentation(rst.CallerZipCode) into newGroup
  select new DataSourceRecord()
  {
    State = newGroup.Select(i => i.CallerState).FirstOrDefault(),
    ZipCode = newGroup.Key,
    Calls = newGroup.Where(x => x.CALL_ID > 0).Select(x => x.CALL_ID).Distinct().Count(),
    Exposures = newGroup.Where(x => x.CALL_ID > 0 && x.ExposureCount > 0).Distinct().Count()
  }).ToList();

If you really want to group the calls/exposures meaning you want to count the unique combinations of (CTR_ID and CALL_ID / CallerState, CTR_ID and CALL_ID), you can of course do so.

I've got to admit the existing code confuses me. I'm just trying to update it. Yes Calls and exposures are very similar and the bit that confuses me is the second grouping as well — Hank, Jul 29 '19 at 06:25
well, in this case you should find out what your requirements are. Sadly, no one here can help you with that. You need to find out "what you need to do", then someone might be able to help you with the "how to do this". — Thomas D., Jul 29 '19 at 08:49
Ok I understand what you're saying about requirements. One extra question, what if I want to group by an extra property rst.CallerState or multiple properties for that matter. Would I send GetGroupRepresentation() the arguments and return an array of values. I've made an [Edit] at the end of the question as an example. — Hank, Jul 30 '19 at 00:49

Group linq results by value and group null or invalid values and do counts

1 Answers1

Linked