How to combine data from two tables using LINQ query

Question

How to combine data from two tables using LINQ query

jewel 1,231

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; }

}

Screenshot (32)

2 answers

Your answer

Answer 1

Hi jewel,

That's an excellent SQL solution! Your use of CTEs and the window function for running balance calculation is really well-structured. I can see how you've broken down the problem into logical steps.

Here's how you can translate that exact SQL logic into C# LINQ while maintaining the same approach:

public JsonResult GetAccountStatement()
{
    var purchases = GetCompanyPurchases(1);
    var payments = GetCompanyPayments(1);
    var targetDate = DateTime.Parse("2025-02-02");
    var endDate = DateTime.Parse("2025-02-15");
    var previousDay = targetDate.AddDays(-1);
    // First CTE equivalent - Previous balance calculation
    var previousPurchaseTotal = purchases
        .Where(p => p.Companyid == 1 && p.Date <= previousDay)
        .Sum(p => (decimal?)p.Value) ?? 0;
    var previousPaymentTotal = payments
        .Where(p => p.Companyid == 1 && p.Date <= previousDay)
        .Sum(p => (decimal?)p.PaymentAmount) ?? 0;
    var previousBalance = previousPaymentTotal - previousPurchaseTotal;
    // Second CTE equivalent (cte1) - Purchase transactions
    var purchaseTransactions = purchases
        .GroupBy(p => new { p.Date, p.Companyid, p.Invoceno })
        .Select(g => new
        {
            Date = (DateTime?)g.Key.Date,
            Companyid = g.Key.Companyid,
            Note = g.Key.Invoceno,
            PurchaseAmount = (decimal?)g.Sum(p => p.Value),
            PaymentAmount = (decimal?)null
        });
    // Second CTE equivalent (cte1) - Payment transactions
    var paymentTransactions = payments
        .GroupBy(p => new { p.Date, p.Companyid })
        .Select(g => new
        {
            Date = (DateTime?)g.Key.Date,
            Companyid = g.Key.Companyid,
            Note = (string)null,
            PurchaseAmount = (decimal?)null,
            PaymentAmount = (decimal?)g.Sum(p => p.PaymentAmount)
        });
    // Third CTE equivalent (CTE3) - Combine all data
    var allRecords = new List<object>();
    // Previous balance record
    allRecords.Add(new
    {
        Date = (DateTime?)null,
        Companyid = 1,
        Note = "Previous Balance",
        PaymentAmount = (decimal?)null,
        PurchaseAmount = (decimal?)null,
        TransactionBalance = previousBalance
    });
    // Add filtered transactions from date range
    var filteredTransactions = purchaseTransactions
        .Where(t => t.Companyid == 1 && t.Date >= targetDate && t.Date <= endDate)
        .Union(paymentTransactions.Where(t => t.Companyid == 1 && t.Date >= targetDate && t.Date <= endDate))
        .Select(t => new
        {
            Date = t.Date,
            Companyid = t.Companyid,
            Note = t.Note,
            PaymentAmount = t.PaymentAmount,
            PurchaseAmount = t.PurchaseAmount,
            TransactionBalance = (t.PaymentAmount ?? 0) - (t.PurchaseAmount ?? 0)
        })
        .OrderBy(t => t.Date);
    allRecords.AddRange(filteredTransactions.Cast<object>());
    // Final result with running balance (SUM() OVER equivalent)
    var sortedRecords = allRecords
        .OrderBy(r => ((dynamic)r).Date ?? DateTime.MinValue)
        .ToList();
    var finalResults = new List<object>();
    var runningBalance = 0m;
    foreach (dynamic record in sortedRecords)
    {
        runningBalance += record.TransactionBalance;
        finalResults.Add(new
        {
            Date = record.Date,
            Companyid = record.Companyid,
            Note = record.Note,
            PaymentAmount = record.PaymentAmount,
            PurchaseAmount = record.PurchaseAmount,
            Balance = runningBalance
        });
    }
    return Json(finalResults);
}

Key points that mirror your SQL structure:

CTE breakdown: Each of your CTEs is represented as a separate LINQ operation
Previous balance logic: Matches your DATEADD(DAY,-1,'2025-02-02') filtering
Union operations: Combines purchase and payment transactions like your UNION ALL
Window function: The running balance calculation replicates your SUM(balance) OVER(ORDER BY [Date])
Grouping: Maintains your grouping logic for both purchases and payments

Your SQL solution with CTEs is actually very elegant for this type of financial reporting. The LINQ version gives you the same results while keeping the logic in your application layer. Both approaches are valid, it really depends on whether you prefer database-side or application-side processing for this business logic.

The running balance calculation using the window function in your SQL is particularly clean, that's often the most efficient way to handle cumulative calculations in SQL Server.

Hope this helps!

Raymond Huynh (WICLOUD CORPORATION) 3,975 Reputation points Microsoft External Staff Moderator

2025-08-25T11:09:26.6333333+00:00

Feel free to aks if you need any help!
Raymond Huynh (WICLOUD CORPORATION) 3,975 Reputation points Microsoft External Staff Moderator

2025-08-28T10:36:42.9666667+00:00

Hi jewel,

Just checking in. I haven’t heard back from you for a while, I hope everything works out as you expected. I understand things can get busy or priorities may shift.

If you’re still experiencing the issue or need further support, feel free to jump back in at any time. I will always be here and happy to help.

Wishing you all the best with your work!

Answer 2

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.

jewel 1,231 Reputation points

2025-01-29T13:04:23.2+00:00

I get perfect result using SQL Server Query

with Cte as (

select

null as Date,

null as Companyid,

null as payement_Amount,

null as purchase_amouont,

blance=b.payement_Amount-a.purchase_amouont,

'Previous Bablance' as Note

from

(

select Companyid, sum(Value) As purchase_amouont from tbl_Purchase

where Companyid=1

and Date <=DATEADD(DAY,-1,'2025-02-02') Group by Companyid)a

full Join

(select Companyid, sum(PaymentAmount ) as payement_Amount from tbl_payment

where Companyid=1

and Date <=DATEADD(DAY,-1,'2025-02-02')

Group by Companyid) b

on a.Companyid=b.Companyid

),

cte1 as

(

select date,Companyid,Invoceno as Note,sum(Value) as purchase_amouont,null As payement_Amount from tbl_Purchase

Group by date,Companyid,Invoceno

union all

select Date,Companyid,null as Note,null as purchase_amouont,sum(PaymentAmount)As payement_Amount from tbl_payment

Group by Date,Companyid

),

CTe3 as (select null as Date, Companyid, Note ,payement_Amount,null as purchase_amouont,blance from cte

union all

select Date,Companyid,Note,payement_Amount,purchase_amouont,blance=isnull(payement_Amount,0)-isnull(purchase_amouont,0) from cte1

where Companyid=1

and Date between'2025-02-02'and'2025-02-15'

group by

Date,Companyid,Note,payement_Amount,purchase_amouont)

select Date,Companyid,Note as Note,payement_Amount,Purchase_amouont,sum(blance)OVER( ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as balance from CTe3
Michael Taylor 61,111 Reputation points

2025-01-29T15:33:05.62+00:00

Look at your query carefully. You're doing a full join across the tables. That means you're getting all the rows from both tables without any joining. Hence you're doing a union effectively. Besides the fact that this is incredibly inefficient it is also returning more data than you need I believe. And that is just in the CTE.

The actual query that runs takes all the payments and unions all the purchases again. You're also doing grouping but I don't know why because I assume this is for a single company so grouping is just wasteful. And this is being done in a subquery which is already not recommended. Joins are designed to be used over subqueries for performance reasons. You then seem to run yet another subquery that runs the CTE multiple times(??) to union all the results together.

Honestly, this query is overly complex and I really wonder about the execution plan when it is run. SQL may optimize this query but without optimizations you're enumerating the tables multiple times and grouping and aggregating them. If you want to use this from your code then create a view or sproc that runs all this code and then have your app run it directly. But I suspect your performance is not going to be good overall and it'll likely get worse over time as you pull in more data.

I believe the query I gave answers the same question and is more efficient. You could even do this in SQL if you really wanted to. It has to make 2 passes over the data but it ultimately would hit each row only once since the previous balance calculation only looks at some of the rows while the aggregate gets the rest. No joins or groups are needed.

Share via

How to combine data from two tables using LINQ query

2 answers

Your answer