c# - 按年龄范围从零开始的 Entity Framework 组

Question

我有一个车辆列表，每个车辆都有一个 BuildYear，我需要为图表提供数据，该图表将把每辆车的数量分组到一个年龄段，例如:

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

即使其中一个括号中没有车辆，它仍应出现在输出中(计数为零)。另一个 S/O 答案不会那样做。

我发现这个 S/O 答案非常接近，但我对它的理解还不足以满足我的需要: Linq select date of birth range and sum

这是使用 EntityFramework.Core 3.0.0 和 SQL Server。

这与我开始使用它一样接近:

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() }
            );

我还有一个 .net fiddle ，其中包含原始答案和我在其中的尝试: https://dotnetfiddle.net/wp59te

Answer 1

这应该让您朝着正确的方向前进。根据您的需要进行修改:

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;
    }
}