How to Sum, Join, Union, Group By Month And Year Using LINQ Query

jewel 801 Reputation points
2024-04-24T09:55:08.01+00:00
I have two tables. I want to find the sum of the data in one table and the subtraction of the second table. I am trying to explain the matter by attaching an image below.
Note here that I don't know which table's data will come first. Maybe the data of table No. 1 can come first, and the data of table No. 2 can also come first.
public class tbl_transaction_1
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public Decimal? Deposit_1 { get; set; }
    public Decimal? Deposit_2 { get; set; }
}
public class tbl_transaction_2
{
	public int Id { get; set; }
	public DateTime Date { get; set; }
	public Decimal? withdraw { get; set; }
	
}
   var List = from a in _context.tbl_Transaction_1s
             group a by new {Month=a.Date.Month,Year=a.Date.Year} into ga
		  select new
		  {
			  Month=ga.Key.Month,
			  Year=ga.Key.Year,
			  Deposit_1=ga.Sum(x=>x.Deposit_1),
			  Deposit_2=ga.Sum(x=>x.Deposit_2)
		  };
var List2 = from b in _context.tbl_Transaction_2s
		   group b by new { Month = b.Date.Month, Year = b.Date.Year } into gb
		   select new
		   {
			   Month = gb.Key.Month,
			   Year = gb.Key.Year,
			   withdraw = gb.Sum(x => x.withdraw)
		   };

//Here res get error
	var res = from a in List
			  join
			 b in List2 on
			 new { a.Month, a.Year } equals new { b.Month, b.Year } into ab
			  select new
			  {
				  Month = a.Month,
				  Year = a.Year,
				  Deposit_1 = a.Deposit_1,
				  Deposit_2 = a.Deposit_2,
				  withdraw = ab.Sum(x => x.withdraw)
			  };

Untitled.png
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,249 questions
{count} votes

Accepted answer
  1. Lucas Santos 75 Reputation points
    2024-04-25T17:30:47.7266667+00:00

    @Jewel you can use the @JasonPan - MSFT suggestion with a little modification transforming your lists in just one and applying a group clause, you can do with a code like this:

    var list = (from t1 in transactions1List
     		   group t1 by new { Month = t1.Date.Month, Year = t1.Date.Year } into grp
    		   select new { grp.Month, grp.Year, Deposit_1 = grp.Sum(i=>i.Deposit_1), Deposit_2 = grp.Sum(i=>i.Deposit_2), Withdraw = 0 }).Union(
    			from t2 in transactions2List
    			group t2 by new { Month = t2.Date.Month, Year = t2.Date.Year } into grp
    			select new { t2.Month, t2.Year, Deposit_1 = 0, Deposit_2 = 0, Withdraw = grp.Sum(i=>i.Withdraw) }
    			);
    
    var result = from i in list
    			 group i by new { i.Month, i.Year } into grp
    			 select new {
    				Month = grp.Month,
    				Year = grp.Year,
    				Deposit_1 = grp.Sum(i=>i.Deposit_1),
    				Deposit_2 = grp.Sum(i=>i.Deposit_2),
    				Withdraw = grp.Sum(i=>i.Withdraw),
    				Result = grp.Sum(i=> i.Deposit_1 + i.Deposit_2 - i.Withdraw)
    			 };
    

0 additional answers

Sort by: Most helpful