Entity Framework group by age range starting at zero

Question

I have a list of vehicles, each with a BuildYear and I need to provide data for a chart which will group the count of each of those vehicles into an age bracket like:

0 to 4
5 to 9
10 to 14
15 to 19
20 to 24
25 to 29
30 to 34
35 to 39
40 +

Even if one of the brackets has no vehicles in it, it should still appear in the output (with a count of zero). The other S/O answer does not do that.

I found this S/O answer which is very close, but I do not understand it enough to modify for my needs: Linq select date of birth range and sum

This is using EntityFramework.Core 3.0.0 and SQL Server.

This was as close as I came to getting it to work:

var thingsByAge = things
            .Select(p => new { ThingId = p.ID, Age = DateTime.Now.Year - p.YearBuilt })
            .GroupBy(p => p.Age > 40 ? 8 : (int)((p.Age + 1) / 5))
            .Select(g => new { 
                AgeGroup = g.Key == 8 ? "> 40" : string.Format("{0} - {1}",  //"g.Key == " puts that group at the end?
                g.Key,
                g.Key * 5 + 4,
                g.Key * 5 + 9,
                g.Key * 5 + 14,
                g.Key * 5 + 19,
                g.Key * 5 + 24,
                g.Key * 5 + 29,
                g.Key * 5 + 34,
                g.Key * 5 + 39), 
                Count = g.Count() }
            );

I also have a .net fiddle with the original answer and my attempt in it: https://dotnetfiddle.net/wp59te

have you considered using a Pivot table? I believe that it will give you a cleaner result on the UI + rather then writing complex linq code which may need changes in the future, I would write a raw sql command to pivot the columns, call it with entity framework and store the result into a class — Asım Gündüz, Nov 19 '19 at 21:52

score 4 · Answer 1 · answered Nov 19 '19 at 20:31

This should get you headed in the right direction.. modify to suit your needs:

using System;
using System.Linq;
using System.Collections.Generic;
using System.Text.RegularExpressions;

public class Program
{



    public static void Main()
    {

        var things = GetThings();
        var ageRanges = new Dictionary<AgeRange, int> {
            {new AgeRange(0, 4), 0},
            {new AgeRange(5, 9), 0},
            {new AgeRange(10, 14), 0},
            {new AgeRange(15, 19), 0},
            {new AgeRange(20, 24), 0},
            {new AgeRange(25, 29), 0},
            {new AgeRange(30, 34), 0},
            {new AgeRange(35, 39), 0}
        };
        var thingsByAge = ageRanges.Select(r => new KeyValuePair<AgeRange, int>(r.Key, things.Count(t => r.Key.Lower <= (DateTime.Now.Year - t.YearBuilt) && (DateTime.Now.Year - t.YearBuilt) <= r.Key.Upper))).ToList();

        var json = Newtonsoft.Json.JsonConvert.SerializeObject(thingsByAge, Newtonsoft.Json.Formatting.Indented);
        //var json = Newtonsoft.Json.JsonConvert.SerializeObject(stackFlowAnswer, Newtonsoft.Json.Formatting.Indented);
        Console.WriteLine(json);

    }

    public static List<Thing> GetThings()
    {
         var things = new List<Thing>();
            things.AddRange(MakeThings(2, 2019)); things.AddRange(MakeThings(12, 2018)); things.AddRange(MakeThings(2, 2017)); things.AddRange(MakeThings(0, 2016)); things.AddRange(MakeThings(0, 2016));
            things.AddRange(MakeThings(7, 2015)); things.AddRange(MakeThings(8, 2014)); things.AddRange(MakeThings(15, 2013)); things.AddRange(MakeThings(5, 2012)); things.AddRange(MakeThings(5, 2011));
            things.AddRange(MakeThings(0, 2010)); things.AddRange(MakeThings(0, 2009)); things.AddRange(MakeThings(0, 2008)); things.AddRange(MakeThings(0, 2007)); things.AddRange(MakeThings(0, 2006));
            things.AddRange(MakeThings(9, 2005)); things.AddRange(MakeThings(4, 2004)); things.AddRange(MakeThings(5, 2003)); things.AddRange(MakeThings(5, 2002)); things.AddRange(MakeThings(5, 2001));
            things.AddRange(MakeThings(1, 2000)); things.AddRange(MakeThings(5, 1999)); things.AddRange(MakeThings(5, 1998)); things.AddRange(MakeThings(5, 1997)); things.AddRange(MakeThings(5, 1996));
            things.AddRange(MakeThings(1, 1995));
            things.AddRange(MakeThings(1, 1990));
            things.AddRange(MakeThings(1, 1985));
            things.AddRange(MakeThings(1, 1980));
            things.AddRange(MakeThings(1, 1975));
            things.AddRange(MakeThings(1, 1970));
            things.AddRange(MakeThings(1, 1965));
            things.AddRange(MakeThings(1, 1960));
            things.AddRange(MakeThings(1, 1955));
            things.AddRange(MakeThings(1, 1950));
            things.AddRange(MakeThings(1, 1945));

        return things;
    }

    public static List<Thing> MakeThings(int count, int year)
    {
        var ret = new List<Thing>();
        for(var i = 0; i < count; i++)
        {
            ret.Add(new Thing(year));
        }
        return ret;
    }

    public class Thing
    {
        public Thing(int yearBuilt)
        {
            this.ID = Guid.NewGuid().ToString();
            this.YearBuilt = yearBuilt;
        }
        public string ID { get; set; }
        public int YearBuilt { get; set; }

    }


    public class AgeRange {
        public AgeRange(int lower, int upper) { Lower = lower; Upper = upper;}
        public int Lower {get;set;} = -1;
        public int Upper {get;set;} = -1;
    }
}

Entity Framework group by age range starting at zero

1 Answers1