How to combine data from two tables using LINQ query

Question

I want to merge between two tables.I want - when going to view the data between two dates first show me the balance before these two dates then show the data. A new column will also display balance information. I have given an image for easy understanding of the experienced

public JsonResult Result()

{

 List<tbl_purchase> purchase = new List<tbl_purchase>()

 {

    new tbl_purchase{Id = 1,Companyid=1 ,Date = DateTime.Parse("2025,2,1"),Invoceno="1234",Value=1000},

    new tbl_purchase{Id = 2,Companyid=1 , Date = DateTime.Parse("2025,2,2"),Invoceno="1235",Value=2000},

    new tbl_purchase{Id = 3,Companyid=1 , Date = DateTime.Parse("2025,2,3"),Invoceno="1236",Value=2200},

    new tbl_purchase{Id = 4,Companyid=1 , Date = DateTime.Parse("2025,2,4"),Invoceno="1237",Value=2500},

    new tbl_purchase{Id = 5,Companyid=1 , Date = DateTime.Parse("2025,2,5"),Invoceno="1238",Value=3000},

    new tbl_purchase{Id = 6,Companyid=1 , Date = DateTime.Parse("2025,2,6"),Invoceno="1239",Value=1500},

    new tbl_purchase{Id = 7, Companyid=1 ,Date = DateTime.Parse("2025,2,15"),Invoceno="1240",Value=3000},

 };

 List<tbl_payment> payment = new List<tbl_payment>()

 {

     new tbl_payment{Id = 1,Companyid=1 , Date = DateTime.Parse("2025,1,30"),PaymentAmount=1200},

     new tbl_payment{Id = 2,Companyid=1 , Date = DateTime.Parse("2025,1,31"),PaymentAmount=2100},

     new tbl_payment{Id = 3,Companyid=1 , Date = DateTime.Parse("2025,2,1"),PaymentAmount=2200},

    new tbl_payment{Id = 4,Companyid=1 , Date = DateTime.Parse("2025,2,2"),PaymentAmount=2700},

    new tbl_payment{Id = 5,Companyid=1 , Date = DateTime.Parse("2025,2,4"),PaymentAmount=2900},

    new tbl_payment{Id = 6,Companyid=1 , Date = DateTime.Parse("2025,2,6"),PaymentAmount=1600},

    new tbl_payment{Id = 7, Companyid=1 ,Date = DateTime.Parse("2025,2,9"),PaymentAmount=3200},

    new tbl_payment{Id = 8,Companyid=1 , Date = DateTime.Parse("2025,2,12"),PaymentAmount=4200},

 };

 var data = from a in purchase

            join b in payment

            on a.Companyid equals b.Companyid    into g

           

            select new

            {

              

            };

 return Json(data);

}

public class tbl_purchase

{

 public int Id { get; set; }

 public DateTime Date { get; set; }

 public int Companyid { get; set; }

 public string Invoceno { get; set; }

 public Decimal Value { get; set; }

}

public class tbl_payment

{

 public int Id { get; set; }

 public int Companyid { get; set; }

 public DateTime Date { get; set; }

 public Decimal PaymentAmount { get; set; }

}

Answer 1

A join isn't correct here. A join is used to get a record plus related records. Here you're wanting to aggregate all the payments and purchases which doesn't require a join. All you need to do is aggregate all the payments and purchases together into a normalized structure that then can be used by your front end.

Note that calculating the previous balance would require you to load all historical data and is going to get slower the longer you go. Ideally either this should be entirely done in the DB and just a "get balance" call in your app or it should be stored, perhaps as an end of month process. The point is that this isn't part of your overall aggregation as it requires data outside the set of data you're ultimately showing.

Another issue is that your payments and purchases appear to be positive numbers which makes calculating the actual balance harder. In general subtractions from the account (purchases) should be negative. This can be handled in the code, which is how I solve it. If the UX doesn't want to show these negative values as negative then it can fix up the display.

Here's my first pass at a solution to your problem.

var payments = GetCompanyPayments(1);
var purchases = GetCompanyPurchases(1);

var beginDate = DateTime.Parse("02/02/2025");
var endDate = DateTime.Parse("02/15/2025");

//Get the previous balance
var previousBalance = GetBalance(payments, purchases, beginDate);

//Aggregate the new payments and purchases
// Get payments and purchases in the given range
// Build a normalized structure to track changes
// Purchases are negative values so treat them as such, UX can hide the sign if not desired but this allows for calculating balance
// Order by date and then ensure payments come before purchases
var changes = (from payment in payments
               where payment.Date >= beginDate && payment.Date <= endDate
               select new AccountChangeRecord() {
                   Date = payment.Date,
                   CompanyId = payment.Companyid,
                   Amount = payment.PaymentAmount
               }).Union(
                from purchase in purchases
                where purchase.Date >= beginDate && purchase.Date <= endDate
                select new AccountChangeRecord() {
                    Date = purchase.Date,
                    CompanyId = purchase.Companyid,
                    Amount = -purchase.Value,
                    ReferenceNumber = purchase.Invoceno
                }
               ).OrderBy(x => x.Date).ThenBy(x => x.IsPayment ?  0: 1); 

Console.WriteLine(changes);

//This is very inefficient as you have to load all historical records, ideally this should be stored somewhere and retrievable directly from DB
decimal GetBalance ( IEnumerable<tbl_payment> payments, IEnumerable<tbl_purchase> purchases, DateTime endDate )
{
    //Do not include any on the end date
    var totalPayments = payments.Where(x => x.Date < endDate).Sum(x => x.PaymentAmount);
    var totalPurchases = purchases.Where(x => x.Date < endDate).Sum(x => x.Value);

    return totalPayments - totalPurchases;
}

IEnumerable<tbl_payment> GetCompanyPayments ( int companyId )
{
    List<tbl_payment> payment = new List<tbl_payment>()
    {
         new tbl_payment{Id = 1,Companyid=1 , Date = DateTime.Parse("2025,1,30"),PaymentAmount=1200},
         new tbl_payment{Id = 2,Companyid=1 , Date = DateTime.Parse("2025,1,31"),PaymentAmount=2100},
         new tbl_payment{Id = 3,Companyid=1 , Date = DateTime.Parse("2025,2,1"),PaymentAmount=2200},
        new tbl_payment{Id = 4,Companyid=1 , Date = DateTime.Parse("2025,2,2"),PaymentAmount=2700},
        new tbl_payment{Id = 5,Companyid=1 , Date = DateTime.Parse("2025,2,4"),PaymentAmount=2900},
        new tbl_payment{Id = 6,Companyid=1 , Date = DateTime.Parse("2025,2,6"),PaymentAmount=1600},
        new tbl_payment{Id = 7, Companyid=1 ,Date = DateTime.Parse("2025,2,9"),PaymentAmount=3200},
        new tbl_payment{Id = 8,Companyid=1 , Date = DateTime.Parse("2025,2,12"),PaymentAmount=4200},
    };

    return payment.Where(x => x.Companyid == companyId);
}

IEnumerable<tbl_purchase> GetCompanyPurchases ( int companyId )
{
    List<tbl_purchase> purchase = new List<tbl_purchase>()
    {
        new tbl_purchase{Id = 1,Companyid=1 ,Date = DateTime.Parse("2025,2,1"),Invoceno="1234",Value=1000},
        new tbl_purchase{Id = 2,Companyid=1 , Date = DateTime.Parse("2025,2,2"),Invoceno="1235",Value=2000},
        new tbl_purchase{Id = 3,Companyid=1 , Date = DateTime.Parse("2025,2,3"),Invoceno="1236",Value=2200},
        new tbl_purchase{Id = 4,Companyid=1 , Date = DateTime.Parse("2025,2,4"),Invoceno="1237",Value=2500},
        new tbl_purchase{Id = 5,Companyid=1 , Date = DateTime.Parse("2025,2,5"),Invoceno="1238",Value=3000},
        new tbl_purchase{Id = 6,Companyid=1 , Date = DateTime.Parse("2025,2,6"),Invoceno="1239",Value=1500},
        new tbl_purchase{Id = 7, Companyid=1 ,Date = DateTime.Parse("2025,2,15"),Invoceno="1240",Value=3000},
    };

    return purchase.Where(x => x.Companyid == companyId);
}

public class AccountChangeRecord
{
    public int CompanyId { get; set; }

    public DateTime Date { get; set; }

    public decimal Amount { get; set; }

    public bool IsPayment => Amount > 0;

    public string ReferenceNumber { get; set; }
}


public class tbl_purchase
{
    public int Id { get; set; }

    public DateTime Date { get; set; }

    public int Companyid { get; set; }

    public string Invoceno { get; set; }

    public Decimal Value { get; set; }
}

public class tbl_payment
{
    public int Id { get; set; }

    public int Companyid { get; set; }

    public DateTime Date { get; set; }

    public Decimal PaymentAmount { get; set; }
}

Let's talk through the code.

• The GetCompanyXYZX methods are just calls to your backend to get the data for a company. How this works is up to you and not relevant for the solution.
• GetBalance enumerates all the records as mentioned earlier. Ideally this should be a single call to the backend to get the calculated value rather than you having to pull all the records and calculate it yourself. This gets inefficient the longer you go.
• The aggregation normalizes the payments and purchases into a single structure that supports both and can be returned to your front end. You can add/adjust any data you need but mimics what your graph showed. Note that it distinguishes between payments and purchases by whether the amount is negative or not. This makes summing the balance easier in the front end.
• The running balance is a feature of the front end, not your code. Every front end has a way to sum up a running total when building tables. This is how you solve this issue and not putting it into your code.
• The previous balance is not included in the resultset. This is a separate datapoint and should be returned to the front end in addition to the aggregated data.