Reusable Queries Method - Query Syntax

Group By Payee

       private static void GroupByPayee(int yearNumber, int monthNumber)
 {
     var qry1 = lstExpenses.Where(x => x.TransactionType == 1 && x.PurchaseDate.Month.Equals(monthNumber));
     DateTime dt = new(yearNumber, monthNumber, 1);

     decimal total = 0;
     var result = from item in qry1
                  group item by new { item.Payee.PayeeName } into grouped
                  select (new
                  {
                      PayeeName = grouped.Key.PayeeName,
                      TotalAmount = grouped.Sum(x => Math.Abs(x.Amount))
                  });
     var final = result.OrderByDescending(x => x.TotalAmount).ToList();
     Console.WriteLine(sep);
     Console.WriteLine(" Report By Payee " + dt.ToString("MMM - yyyy"));
     Console.WriteLine(sep);
     foreach (var item in final)
     {
         total += item.TotalAmount;
         Console.WriteLine("  {0}  Amount: {1:C}", item.PayeeName, item.TotalAmount);
     }
     Console.WriteLine(sep);
     Console.WriteLine(" Total: {0:C}", total);
     Console.WriteLine(sep);
 }

    

Group By Category

       private static void GroupByCategory(int yearNumber, int monthNumber)
 {
     DateTime dt = new(yearNumber, monthNumber, 1);

     var qry1 = lstExpenses.Where(x => x.TransactionType == 1 && x.PurchaseDate.Month.Equals(monthNumber));
     decimal total = 0;
     var result = from item in qry1
                  group item by new { item.Category.CategoryName } into grouped
                  select (new
                  {
                      CategoryName = grouped.Key.CategoryName,
                      TotalAmount = grouped.Sum(x => Math.Abs(x.Amount))
                  });
     var final = result.OrderByDescending(x => x.TotalAmount).ToList();

     Console.WriteLine(sep);
     Console.WriteLine(" Report By Category " + dt.ToString("MMM - yyyy"));
     Console.WriteLine(sep);
     foreach (var item in final)
     {
         total += item.TotalAmount;
         Console.WriteLine(" {0} Amount: {1:C}", item.CategoryName, item.TotalAmount);
     }
     Console.WriteLine(sep);
     Console.WriteLine(" Total: {0:C}", total);
     Console.WriteLine(sep);
 }

    

Get Categories in a given Month (List Of Catgory)

       private static List<Category> GetCategoriesInMonth(int monthNumber)
 {
     var qry1 = lstExpenses.Where(x => x.TransactionType == 1 
	 && x.PurchaseDate.Month.Equals(monthNumber));

     // Distinct List of Categories in a specific Month
     var result = qry1.GroupBy(e => new
     {
         e.Category.CategoryID,
         e.Category.CategoryName
     }).Distinct()
     .Select(g => new Category
     {
         CategoryID = g.Key.CategoryID,
         CategoryName = g.Key.CategoryName
     }).ToList();
     return result;
 }

    

Get Payees in a given Month (List Of Payee)

       private static List<Payee> GetPayeesInMonth(int monthNumber)
 {
     var qry1 = lstExpenses.Where(x => x.TransactionType == 1 && 
	 x.PurchaseDate.Month.Equals(monthNumber));

     // Distinct List of Categories in a specific Month
     var result = qry1.GroupBy(e => new
     {
         e.Payee.PayeeId,
         e.Payee.PayeeName
     }).Distinct()
     .Select(g => new Payee
     {
         PayeeId = g.Key.PayeeId,
         PayeeName = g.Key.PayeeName
     }).ToList();
     return result;
 }

    

Method - Query Syntax

           private static void TestIGrouping1()
  {
      // Method Syntax
      var qry1 = lstExpenses.Where(x => x.TransactionType == 1 && x.PurchaseDate.Month.Equals(1))
      .GroupBy(e => e.Category.CategoryName)
      .Select(g => new
      {
          CategoryName = g.Key,
          TotalAmount = g.Sum(x => Math.Abs(x.Amount)),
          Payees = g.Select(p => new
          {
              p.Payee.PayeeName,
              p.Amount,
              p.PurchaseDate
          }).Distinct()
      }).ToList();

      // Query Syntax
      var qry2 = from x in lstExpenses
                 where x.TransactionType == 1 && x.PurchaseDate.Month.Equals(1)
                 group x by x.Category.CategoryName into e
                 select new
                 {
                     CategoryName = e.Key,
                     TotalAmount = e.Sum(x => Math.Abs(x.Amount)),
                     Payees = (from p in e
                               select new
                               {
                                   p.Payee.PayeeName,
                                   p.Amount,
                                   p.PurchaseDate
                               }).Distinct()
                 };


      var qryOutFlow = lstExpenses.Where(o => o.TransactionType == 1).Sum(x => x.Amount);
      var qryInFlow = lstExpenses.Where(o => o.TransactionType == 2).Sum(x => x.Amount);

      Console.WriteLine("\tOut Flow: {0:C}", qryOutFlow);
      Console.WriteLine("\tIn Flow: {0:C}", qryInFlow);
      Console.WriteLine("\tDiff: {0:C}", qryOutFlow + qryInFlow);

      foreach (var group in qry2)
      {
          Console.WriteLine(sep);
          Console.WriteLine("Category: {0} Total:{1:C} Count:{2}",
              group.CategoryName, group.TotalAmount, group.Payees.Count());
          Console.WriteLine(sep);

          foreach (var payee in group.Payees)
          {
              Console.WriteLine("   Payee: {0} {1:C} - {2:d}",
                  payee.PayeeName, Math.Abs(payee.Amount), payee.PurchaseDate);
          }
      }
  }