Linq Union Quarry

Question

I have a table data with two types of data one is payment the other is receive. I need to make calculation of this data on month year basis. I have shown the idea in the image below. I would appreciate it if someone could help.

public class tbl_calculation
{
[Key]
public int id { get; set; }
public DateTime Date { get; set; }
public decimal Amount{ get; set; }
public String Type { get; set; }
public DateTime? AgainstMonth { get; set; }
}

Answer 1

simple linq:

define ResultData class:

	public class ResultData
	{
		public int Company_ID { get; set; }
		public int Year { get; set; }
		public int Month { get; set; }
		public decimal Payment { get; set; }
		public decimal Received { get; set; }
	}

query:

        var result = source.Select(r => new ResultData
		{
			Company_ID = r.Company_ID,
			Year = (r.AgainstMonth ?? r.Date).Year,
			Month = (r.AgainstMonth ?? r.Date).Month,
			Payment = r.Type == "Payment" ? r.Amount : 0, 
			Received = r.Type == "Received" ? r.Amount : 0,
			Balance = r.Type == "Payment" ? r.Amount 
				: r.Type == "Received" ? -r.Amount 
				: 0
		}).GroupBy(
			r => (r.Company_ID, r.Year, r.Month),
			r => r,
			(k, v) => new ResultData
			{
				Company_ID = k.Item1,
				Year = k.Item2,
				Month = k.Item3,
				Payment = v.Sum(s => s.Payment),
				Received = v.Sum(s => s.Received),
				Balance = v.Sum(s => s.Balance)
			}
		);

Answer 2

Hi jewel, based on my understanding, we can design the CalculationResult Class like below. Because there might be some other Amount Type, so that we'd better to define the Type Property instead of Payment/Received/Balance properties.

public class CalculationResult
{
    public int Company_ID { get; set; }
    public DateTime Date { get; set; }
    public string Type { get; set; }
    public decimal TotalAmount { get; set; }
}

Then here's my code snippet and my test result.

public void test() {
    var calculations = new List<tbl_calculation>
    {
        new tbl_calculation { id = 1, Company_ID = 1, Date = DateTime.Now.Date, Amount = 100, Type = "Payment" },
        new tbl_calculation { id = 2, Company_ID = 1, Date = DateTime.Now.Date, Amount = 200, Type = "Payment" },
        new tbl_calculation { id = 3, Company_ID = 1, Date = DateTime.Now.Date, Amount = 200, Type = "Received" },
        new tbl_calculation { id = 4, Company_ID = 1, Date = DateTime.Now.Date.AddDays(1), Amount = 150, Type = "Payment" },
        new tbl_calculation { id = 5, Company_ID = 2, Date = DateTime.Now.Date, Amount = 300, Type = "Payment" },
        new tbl_calculation { id = 6, Company_ID = 2, Date = DateTime.Now.Date, Amount = 50, Type = "Received" },
    };

    var result = calculations
        .Where(c => c.Company_ID == 1)
        .GroupBy(c => new { c.Company_ID, c.Date, c.Type })
        .Select(g => new CalculationResult
        {
            Company_ID = g.Key.Company_ID,
            Date = g.Key.Date,
            Type = g.Key.Type,
            TotalAmount = g.Sum(c => c.Amount)
        })
        .OrderBy(r => r.Company_ID)
        .ThenBy(r => r.Date)
        .ThenBy(r => r.Type)
        .ToList();

    foreach (var item in result)
    {
        _logger.LogInformation($"Date: {item.Date.ToShortDateString()}, Company ID: {item.Company_ID}, Type: {item.Type}, Total Amount: {item.TotalAmount}");
    }
}

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Tiny

Answer 3

It not clear how the dates are determined in the result table.