![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(300.8, 44%, 39%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJ%3C/text%3E%3C/svg%3E)
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,586 questions
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(272, 11%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJP%3C/text%3E%3C/svg%3E)
jewel, Converting your SQL query with the ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW clause to a LINQ query can be a bit complex, but it's definitely doable. Here's how you can achieve this in C# using LINQ:
First, let's define your data models:
public class Payment
{
public int Id { get; set; }
public int? CompanyId { get; set; }
public DateTime? Date { get; set; }
public decimal? PaymentAmount { get; set; }
}
public class Purchase
{
public int Id { get; set; }
public int CompanyId { get; set; }
public DateTime Date { get; set; }
public string InvoiceNo { get; set; }
public decimal Value { get; set; }
}
Next, let's create a method to perform the LINQ query:
public class Result
{
public DateTime? Date { get; set; }
public int? CompanyId { get; set; }
public string Note { get; set; }
public decimal? PaymentAmount { get; set; }
public decimal? PurchaseAmount { get; set; }
public decimal Balance { get; set; }
}
public List<Result> GetBalance(List<Payment> payments, List<Purchase> purchases)
{
var previousBalance = (from p in purchases
where p.CompanyId == 1 && p.Date <= new DateTime(2025, 2, 1)
group p by p.CompanyId into g
select new
{
CompanyId = g.Key,
PurchaseAmount = g.Sum(x => x.Value)
})
.FullOuterJoin(
from p in payments
where p.CompanyId == 1 && p.Date <= new DateTime(2025, 2, 1)
group p by p.CompanyId into g
select new
{
CompanyId = g.Key,
PaymentAmount = g.Sum(x => x.PaymentAmount)
},
x => x.CompanyId,
y => y.CompanyId,
(x, y) => new
{
CompanyId = x?.CompanyId ?? y?.CompanyId,
Balance = (y?.PaymentAmount ?? 0) - (x?.PurchaseAmount ?? 0),
Note = "Previous Balance"
})
.Select(x => new Result
{
Date = null,
CompanyId = x.CompanyId,
Note = x.Note,
PaymentAmount = null,
PurchaseAmount = null,
Balance = x.Balance
}).ToList();
var transactions = (from p in purchases
select new Result
{
Date = p.Date,
CompanyId = p.CompanyId,
Note = p.InvoiceNo,
PaymentAmount = null,
PurchaseAmount = p.Value,
Balance = 0
})
.Union(
from p in payments
select new Result
{
Date = p.Date,
CompanyId = p.CompanyId,
Note = null,
PaymentAmount = p.PaymentAmount,
PurchaseAmount = null,
Balance = 0
})
.Where(x => x.CompanyId == 1 && x.Date >= new DateTime(2025, 2, 2) && x.Date <= new DateTime(2025, 2, 15))
.OrderBy(x => x.Date)
.ToList();
var results = previousBalance.Union(transactions).ToList();
decimal runningBalance = 0;
foreach (var result in results)
{
runningBalance += (result.PaymentAmount ?? 0) - (result.PurchaseAmount ?? 0);
result.Balance = runningBalance;
}
return results;
}
This method uses LINQ to perform the necessary calculations and ordering, similar to your SQL query. The FullOuterJoin method is a custom extension method to perform a full outer join in LINQ, which you can implement as follows:
public static class LinqExtensions
{
public static IEnumerable<TResult> FullOuterJoin<TLeft, TRight, TKey, TResult>(
this IEnumerable<TLeft> left,
IEnumerable<TRight> right,
Func<TLeft, TKey> leftKeySelector,
Func<TRight, TKey> rightKeySelector,
Func<TLeft, TRight, TResult> resultSelector)
{
var leftLookup = left.ToLookup(leftKeySelector);
var rightLookup = right.ToLookup(rightKeySelector);
var keys = new HashSet<TKey>(leftLookup.Select(p => p.Key));
keys.UnionWith(rightLookup.Select(p => p.Key));
var join = from key in keys
from leftValue in leftLookup[key].DefaultIfEmpty()
from rightValue in rightLookup[key].DefaultIfEmpty()
select resultSelector(leftValue, rightValue);
return join;
}
}
This should give you a good starting point to convert your SQL query to a LINQ query. Let me know if you need any further assistance!
Jonathan