CODEWITHSUNDEEP
c#linqlinq-to-sql
lylety
lylety

Reputation: 41

Using LINQ to return a list of Ages by category

So the objective is for me to display a pie chart that contains a break down of employees by age category. Ideally I would like it to increment in years of 5 so : 15-19, 20-24, 25-29 all the way up to 65-69. Though 10-19,20-29 etc would also be useful.

All the other code is fine, but my query is off as it is returning pretty much every age and even multiple instances of the ages (ie. i have 4 counts of 28)

The code is below. Much appreciated, thanks.

//Employee Age Categories 
    public IEnumerable<KeyValuePair<int, int>> EmployeeAges()
    {
        using (var conn = Databases.HR)
        {
            var res =
                conn.Query<Employee>("SELECT EmployeeId,FirstName,Surname,DateOfBirth FROM EmployeeDetails")
                    .ToList();

            return from e in res
                       //get the difference in years since the birthdate
                   let years = DateTime.Now.Year - e.DateOfBirth?.Year
                   //get the date of the birthday this year
                   let birthdayThisYear = years.HasValue ? e.DateOfBirth?.AddYears(years.Value) : null as DateTime?

                   let age = birthdayThisYear > DateTime.Now ? years - 1 : years
                   where years != null
                   select new KeyValuePair<int, int>
                       (
                       //if the birthday hasn't passed yet this year we need years - 1
                       age.Value,
                       15 + (((int)(age - 15) / 4) * 4)
                       );

        }
    }

Upvotes: 0

Views: 700

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236248

First of all, I would not mix query and age calculation. You can add age property to employee class (it also can be a method which accepts current date or even stand-alone helper method):

 public int? Age
 {
     get
     {
         if (!DateOfBirth.HasValue)
            return null;

         var today = DateTime.Now;
         int age = today.Year - birthdate.Value.Year;
         if (birthdate.Value > today.AddYears(-age))
            age--;

         return age;
     }
 }

Now query:

from e in res
where e.DateOfBirth.HasValue
group e by e.Age.Value / 5 into g
orderby g.Key
select new {
   From = g.Key * 5,
   To = g.Key * 5 + 4,
   Count = g.Count()
};

E.g. if you have employees with following dates of birth:

[
  "1984-05-01T00:00:00",
  "1985-02-10T00:00:00",
  "1986-12-31T00:00:00",
  "1991-01-07T00:00:00",
  null,
  "1999-06-02T00:00:00",
  "2001-01-01T00:00:00",
  "1970-01-01T00:00:00"
]

Output will be

[
  { "From": 15, "To": 19, "Count": 2 },
  { "From": 25, "To": 29, "Count": 1 },
  { "From": 30, "To": 34, "Count": 3 },
  { "From": 45, "To": 49, "Count": 1 }
]

Upvotes: 2

Related Questions