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

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

1. 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)
};
``````