Linq select date of birth range and sum

Question

I have a table like the following

UserID UserName DateOfBirth
1      User1    1988-1-1
2      User2    1978-1-1
3      User3    1959-1-1
....
.....

What I intend to do is to query a result similar to following

Age range   Total User
16 - 25     10
26 - 35     20
36 - 45     15
46 - 55     16
56 - 65     30
> 70        40

The problem i am having is i do not know how to group the age range as above. I only manage to group the age of each individual user using following code

db.User
.Where(p => p.DateOfBirth != null)
.GroupBy(p => p.DateOfBirth.Value.Year)
.Select(g => new { Age = DateTime.Now.Year - g.Key, Count = g.Count() });

Please don't use the Javascript snippet support in Markdown when your post doesn't actually contain any Javascript. — Jon Skeet, Apr 07 '15 at 13:52
Actually, this doesn't have anything to do with asp.net MVC either. It's just C# and LINQ. — ataravati, Apr 07 '15 at 14:04
User ID: int, UserName:string, DateOfBirth: datetime? not sure if this is what you mean by class structure — vincentsty, Apr 07 '15 at 15:01

score 1 · Accepted Answer · edited May 23 '17 at 11:56

Something like this should work:

db.User.Where(p => p.DateOfBirth != null).ToList()
    .Select(p => new { UserId = p.UserId, Age = DateTime.Now.Year - p.DateOfBirth.Value.Year })
    .GroupBy(p => (int)((p.Age - 16) / 10))
    .Select(g => new { AgeGroup = string.Format("{0} - {1}", g.Key * 10 + 16, g.Key * 10 + 25), Count = g.Count() });

By the way, you can't just subtract the birth year from the current year to calculate the age. You'll have to create a function to calculate it. Here you can find out how to calculate the age.

UPDATE:

If you want to have a separate group for age range "> 65", then you can do it like this:

db.User.Where(p => p.DateOfBirth != null).ToList()
    .Select(p => new { UserId = p.UserId, Age = DateTime.Now.Year - p.DateOfBirth.Value.Year })
    .GroupBy(p => p.Age > 65 ? 5 : (int)((p.Age - 16) / 10))
    .Select(g => new { AgeGroup =  g.Key == 5 ? "> 65" : string.Format("{0} - {1}", g.Key * 10 + 16, g.Key * 10 + 25), Count = g.Count() });

You can do the same thing for ages below 16 too. If it gets more complicated, I recommend creating a separate function to get the Age Group string.

this example looks great for age range 16-25, 26-35 etc up till a certain age available in database. But what about the condition for grouping those after 65 years old into one group instead of 66 - 75, 76 - 85 etc. Does it need to be done with another linq grouping for querying age after age 65 and union the two linq query? or can it be done in single query — vincentsty, Apr 07 '15 at 15:05

JNYRanger · Answer 2 · 2015-04-07T14:31:14.277

You first would need to create some sort of value that would represent being within a certain age-range if you want to do it using linq. This might work better as a Stored Procedure though if you're using a SQL database.

Currently, you are performing your grouping on the year as you know. You'd need a second operation to count up the matches within each range. Use the linq query that you currently have and then do the following (this assumes you have Dictionary<string,int> ageRanges within this class, but you can always modify this to have it elsewhere):

var query = /*LINQ QUERY HERE*/;
foreach(var g in query)
{
    int ranges = g.Age / 5; //range in example is every 10 years starting at 16
    switch(ranges)
    {
         case 0: //0 -5
         case 1: //5 - 9
         case 2: //10-15
             continue; //we don't care about these values
         case 3: //16-20
         case 4: //21-25
              addToRanges("16-25",g.Count);
              break;
         ... //etc you get the idea
     }
}

Here's our addToRanges method:

private void addToRanges(string key, int value)
{
    if(ageRanges.ContainsKey(key))
    {
        ageRanges[key] += value;
    }
    else
    {
        ageRanges.Add(key, value);
    }
}

There are lots of other ways to do this, but this is just some simple example on how you can perform this operation. This is not necessarily the most performant example. Having a Stored Procedure that performs this calculation would be the best/most performant way to do this, but this method will at least show you how to do it with the linq query that you already put together.

Here's a more linq like example:

db.User
  .Where(p => p.DateOfBirth != null)
  .GroupBy(p => p.DateOfBirth.Value.Year)
  .Select(g => 
   {
       int age = DateTime.Now.Year - g.Key;
       int range = (age / 5) - 3; //Assumes that everything is at least age > 15
       if(range % 2 == 1)
            range -= 1; //to make sure we group 10s not 5s
       return new { Age = age, Count = g.Count(), Range = range } 
   })
  .GroupBy(a => g.Range)
  .Select(g => new {RangeValue = g.Range, Count = g.Count()});

Now this will produce a similar result with an IEnumerable instead of a dictionary.

@vincentsty I wanted to be as verbose as possible for OP to see why they were only grouping on the Age, not the range. You are right, there are shorter ways to do this. Personally, I think a StoredProc would be the best way to go, but they asked for a linq answer. — JNYRanger, Apr 07 '15 at 15:27

Linq select date of birth range and sum

2 Answers2

Linked