How select rows with grouping by month

Question

How select rows grouping by month.
So I have an entity:

public class Security
{
    public Guid Id { get; set; }

    public string Name { get; set; }

    public string Quatation { get; set; }

    public SecurityType SecurityType { get; set; }

    public double Denomination { get; set; }

    public CurrencyType DemoniationType { get; set; }

    public virtual ICollection<ReportPeriod> ReportPeriods { get; set; }
}

Report Period entity:

 public class ReportPeriod
{
    public Guid Id { get; set; }

    public DateTime Start { get; set; }

    public DateTime End { get; set; }

    public Guid SecurityId { get; set; }

    public Guid StockExchangeId { get; set; }

    public double Amount { get; set; }

    public virtual Security Security { get; set; }
}

So I need somehow get general Amount by every month in a year for ReportPeriod. Does anyone have some ideas how to do it?

Answer 1

you want general Amount of month.

• Assuming you want it in the format Dictionary<DateTime, double> where Key is first date of Month (of which month we have general amount in Value).
• Assuming that, range of start and end doesn't go across the month.

Add this property in your Security class.

public Dictionary<DateTime, double> AmountGroupedByMonth
{
  get
  {
     Dictionary<DateTime, double> table = new Dictionary<DateTime, double>();

     if (ReportPeriods != null && ReportPeriods.Count > 0)
     {
         ReportPeriod frtReportPeriod = ReportPeriods.First();

         DateTime monthStDt = 
             new DateTime(frtReportPeriod.Start.Year, frtReportPeriod.Start.Month, 1);
         double groupedAmount = 0;

         foreach (ReportPeriod reportPeriod in ReportPeriods)
         {
             //Checking if this report should be grouped with pervious report or not
             if (monthStDt.Year == reportPeriod.Start.Year 
                 && monthStDt.Month == reportPeriod.Start.Month)
             {
                 groupedAmount += reportPeriod.Amount;
             }
             else
             {
                 //if we find that this report is of different month.
                 table.Add(monthStDt, groupedAmount);

                 groupedAmount = reportPeriod.Amount;
                 monthStDt = 
                     new DateTime(reportPeriod.Start.Year, reportPeriod.Start.Month, 1);
             }
         }
         if (groupedAmount != 0 && !table.ContainsKey(monthStDt))
              table.Add(monthStDt, groupedAmount);
     }
     return table;
  }
}

by adding this property, Month wise grouped data will be easily available to the object of Security . and also, as it not stored in any variable, you will not be required to update it (or generate) before using it. Simply call this property and it will calculate general Amount month wise with latest available data.

Security s = new Security();

DateTime nowDate = DateTime.Now;
s.ReportPeriods = new List<ReportPeriod>();
for(int i = 0; i <= 70; i = i + 5)
{
  s.ReportPeriods.Add(new ReportPeriod(nowDate.AddDays(i), nowDate.AddDays( i + 3), 200 ));
}

Dictionary<DateTime, double> AmountGroupedByMonth = s.AmountGroupedByMonth;

And output will be like:

Answer 2

We can do this by using LINQ. Please find the below code snippet in C#. Hope it helps. Here we are grouping by year and month and then summing up the amount as well.

namespace Solutions
{
    using System;
    using System.Collections.Generic;
    using System.Linq;

    public class Security
    {
        public Guid Id { get; set; }

        public string Name { get; set; }

        public string Quatation { get; set; }

        //public SecurityType SecurityType { get; set; }

        public double Denomination { get; set; }

        //public CurrencyType DemoniationType { get; set; }

        public virtual ICollection<ReportPeriod> ReportPeriods { get; set; }
    }

    public class ReportPeriod
    {
        public Guid Id { get; set; }

        public DateTime Start { get; set; }

        public DateTime End { get; set; }

        public Guid SecurityId { get; set; }

        public Guid StockExchangeId { get; set; }

        public double Amount { get; set; }

        public virtual Security Security { get; set; }
    }

    public class Entities
    {
        public static void Main(string[] args)
        {

            Security security = new Security()
            {
                Id = Guid.NewGuid(),
                Denomination = 1,
                Name = "A",
                Quatation = "Z",
                ReportPeriods = new List<ReportPeriod>()
            };
            security.ReportPeriods.Add(new ReportPeriod()
            {
                Amount = 10,
                Security = security,
                SecurityId = security.Id,
                End = DateTime.Now.AddDays(1),
                Start = DateTime.Now,
                Id = Guid.NewGuid(),
                StockExchangeId = Guid.NewGuid()
            });
            security.ReportPeriods.Add(new ReportPeriod()
            {
                Amount = 5,
                Security = security,
                SecurityId = security.Id,
                End = DateTime.Now.AddDays(1),
                Start = DateTime.Now,
                Id = Guid.NewGuid(),
                StockExchangeId = Guid.NewGuid()
            });
            security.ReportPeriods.Add(new ReportPeriod()
            {
                Amount = 5,
                Security = security,
                SecurityId = security.Id,
                End = DateTime.Now.AddDays(1),
                Start = DateTime.Now.AddMonths(-1),
                Id = Guid.NewGuid(),
                StockExchangeId = Guid.NewGuid()
            });

            foreach (var groupedReportValues in security.ReportPeriods
                .GroupBy(period => new { period.Start.Year, period.Start.Month }).Select(
                    groupedOnMonth => new
                    {
                        StartYear = groupedOnMonth.Key.Year,
                        StartMonth = groupedOnMonth.Key.Month,
                        AmountSum = groupedOnMonth.Sum(reportValue => reportValue.Amount)
                    }))
            {
                Console.WriteLine(groupedReportValues.StartYear);
                Console.WriteLine(groupedReportValues.StartMonth);
                Console.WriteLine(groupedReportValues.AmountSum);
                Console.WriteLine();
            }

            Console.ReadLine();
        }
    }
}