// @nuget: EntityFramework

​

using System;

using System.Data.Entity;

using System.Linq;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations.Schema;

using System.ComponentModel.DataAnnotations;

public class Program

{

    public static void Main()

    {

        using (var db = new EntityContext())

        {

            db.Seed();

        }

​

        using (var db = new EntityContext())

        {

            // write the output

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

            //FiddleHelper.Dump(db.Hato.ToList());

            // NOTE: DO NOT use Convert.ToDateTime(string) to build a static date, use the constructor!

            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 qLocationOriginal

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

            Console.WriteLine("Original Query SQL:");

            Console.WriteLine(qLocationOriginal.ToString());

            Console.WriteLine();

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

​

            Console.WriteLine("Hato Query SQL:");

            Console.WriteLine(qLocationQuery.ToString());

            Console.WriteLine();

​

            var qLocation = qLocationQuery.ToList();

            // now perform the sort, this simulates leaving the sort to the rendering logic.

            qLocation = qLocation.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 EntityContext : DbContext

    {

        public EntityContext() : base(FiddleHelper.GetConnectionStringSqlServer())

        {

        }

        public DbSet<Hato> Hato { get; set; }

​

        public void Seed()

        {

​

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

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

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

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

            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

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

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

            this.SaveChanges();

        }

    }

    public class Hato

    {

        [Key]

        public int Id { get; set; }

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

    }

}