using System;

using System.Collections.Generic;

using System.Linq;

public class Program

{

    public static void Main()

    {

        var db = FakeDbContext.Seed();

​

        // write the output

        DumpTable(db.Hato, "All HATO Records");

        DateTime startDate = new DateTime(2021,05,21);

        DateTime endDate = new DateTime(2021,05,24);

        // verify the date filter results

        DumpTable(db.Hato.Where(L => L.HatoRecDate >= startDate && L.HatoRecDate <= endDate), String.Format("HATO Between [{0:dd.MM.yyyy} - {1:dd.MM.yyyy}]", startDate, endDate));

        var qLocation = (from L in db.Hato

                         where L.HatoRecDate >= startDate && L.HatoRecDate <= endDate

                         group L by L.HatoLocation into g

                         select new { HatoLocation = g.Key, count = g.Count() })

                        .OrderByDescending(o => o.count).ToList();

​

        // write the results to the output

        DumpTable(qLocation, "qLocation Grouping Results");

        var l = qLocation[0].HatoLocation;

        var c = qLocation[0].count;

        Console.WriteLine("qLocation[0].HatoLocation: {0}", l);

        Console.WriteLine("qLocation[0].count:        {0}", c);

    }

    public class FakeDbContext

    {

        public static FakeDbContext Seed()

        {

            var db = new FakeDbContext();

            db.Hato.Add(new Hato { HatoRecDate = new DateTime(2021,05,21), HatoLocation = "A" });

            db.Hato.Add(new Hato { HatoRecDate = new DateTime(2021,05,21), HatoLocation = "A" });

            db.Hato.Add(new Hato { HatoRecDate = new DateTime(2021,05,23), HatoLocation = "B" });

            db.Hato.Add(new Hato { HatoRecDate = new DateTime(2021,05,24), HatoLocation = "A" });

            db.Hato.Add(new Hato { HatoRecDate = new DateTime(2021,05,24), HatoLocation = "B" });

            // Add one entry either side of the date to prove the filter condition

            db.Hato.Add(new Hato { HatoRecDate = new DateTime(2021,05,20), HatoLocation = "A" });

            db.Hato.Add(new Hato { HatoRecDate = new DateTime(2021,05,25), HatoLocation = "B" });

            return db;

        }

        public ICollection<Hato> Hato = new HashSet<Hato>();

    }

    public class Hato

    {

        public DateTime HatoRecDate { get;set; }

        public string HatoLocation { get;set; }

    }

    /// <summary>.Net Fiddle Helper to dump the contents of an IEnumerable out to the Console Output so we can cut'n'paste a table into Stack Overflow!</summary>

    public static void DumpTable<T>(IEnumerable<T> table, string header = null)

    {

        if(!String.IsNullOrWhiteSpace(header))

            Console.WriteLine("// {0}", header);

        var columns = typeof(T).GetProperties();

        Console.WriteLine("|{0}|", String.Join("|", columns.Select(c => c.Name)));

        Console.WriteLine("|{0}|", String.Join("|", columns.Select(c => new String('-', c.Name.Length))));

        foreach (T record in table)

        {

            Console.Write("|");

            foreach (var c in columns)

            {

                Console.Write("{0}|", c.GetValue(record, null));

            }

            Console.WriteLine();

        }

        Console.WriteLine();

    }

}