How can I convert from SQL Server ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to LinQ Query?

Question

I have a SQL query and want to convert it to LINQ. It would be great if some experienced people could help.

CREATE TABLE [dbo].[tbl_payment](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Companyid] [int] NULL,

[Date] [datetime] NULL,

[PaymentAmount] [decimal](18, 0) NULL
```) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[tbl_purchase]    Script Date: 1/30/2025 10:01:44 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[tbl_purchase](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Companyid] [int] NOT NULL,

[Date] [datetime] NOT NULL,

[Invoceno] nvarchar NOT NULL,

[Value] decimal NOT NULL

GO

SET IDENTITY_INSERT [dbo].[tbl_payment] ON 

INSERT [dbo].[tbl_payment] ([Id], [Companyid], [Date], [PaymentAmount]) VALUES (1, 1, CAST(N'2025-01-30T00:00:00.000' AS DateTime), CAST(1200 AS Decimal(18, 0)))

INSERT [dbo].[tbl_payment] ([Id], [Companyid], [Date], [PaymentAmount]) VALUES (2, 1, CAST(N'2025-01-31T00:00:00.000' AS DateTime), CAST(2100 AS Decimal(18, 0)))

INSERT [dbo].[tbl_payment] ([Id], [Companyid], [Date], [PaymentAmount]) VALUES (3, 1, CAST(N'2025-02-01T00:00:00.000' AS DateTime), CAST(2200 AS Decimal(18, 0)))

INSERT [dbo].[tbl_payment] ([Id], [Companyid], [Date], [PaymentAmount]) VALUES (4, 1, CAST(N'2025-02-02T00:00:00.000' AS DateTime), CAST(2700 AS Decimal(18, 0)))

INSERT [dbo].[tbl_payment] ([Id], [Companyid], [Date], [PaymentAmount]) VALUES (5, 1, CAST(N'2025-02-04T00:00:00.000' AS DateTime), CAST(2900 AS Decimal(18, 0)))

INSERT [dbo].[tbl_payment] ([Id], [Companyid], [Date], [PaymentAmount]) VALUES (6, 1, CAST(N'2025-02-06T00:00:00.000' AS DateTime), CAST(1600 AS Decimal(18, 0)))

INSERT [dbo].[tbl_payment] ([Id], [Companyid], [Date], [PaymentAmount]) VALUES (7, 1, CAST(N'2025-02-09T00:00:00.000' AS DateTime), CAST(3200 AS Decimal(18, 0)))

INSERT [dbo].[tbl_payment] ([Id], [Companyid], [Date], [PaymentAmount]) VALUES (8, 1, CAST(N'2025-02-12T00:00:00.000' AS DateTime), CAST(4200 AS Decimal(18, 0)))

SET IDENTITY_INSERT [dbo].[tbl_payment] OFF

GO

SET IDENTITY_INSERT [dbo].[tbl_purchase] ON 

INSERT [dbo].[tbl_purchase] ([Id], [Companyid], [Date], [Invoceno], [Value]) VALUES (1, 1, CAST(N'2025-02-01T00:00:00.000' AS DateTime), N'1234', CAST(1000 AS Decimal(18, 0)))

INSERT [dbo].[tbl_purchase] ([Id], [Companyid], [Date], [Invoceno], [Value]) VALUES (2, 1, CAST(N'2025-02-02T00:00:00.000' AS DateTime), N'1235', CAST(2000 AS Decimal(18, 0)))

INSERT [dbo].[tbl_purchase] ([Id], [Companyid], [Date], [Invoceno], [Value]) VALUES (3, 1, CAST(N'2025-02-03T00:00:00.000' AS DateTime), N'1236', CAST(2200 AS Decimal(18, 0)))

INSERT [dbo].[tbl_purchase] ([Id], [Companyid], [Date], [Invoceno], [Value]) VALUES (4, 1, CAST(N'2025-02-04T00:00:00.000' AS DateTime), N'1237', CAST(2500 AS Decimal(18, 0)))

INSERT [dbo].[tbl_purchase] ([Id], [Companyid], [Date], [Invoceno], [Value]) VALUES (5, 1, CAST(N'2025-02-05T00:00:00.000' AS DateTime), N'1238', CAST(3000 AS Decimal(18, 0)))

INSERT [dbo].[tbl_purchase] ([Id], [Companyid], [Date], [Invoceno], [Value]) VALUES (6, 1, CAST(N'2025-02-06T00:00:00.000' AS DateTime), N'1239', CAST(1500 AS Decimal(18, 0)))

INSERT [dbo].[tbl_purchase] ([Id], [Companyid], [Date], [Invoceno], [Value]) VALUES (7, 1, CAST(N'2025-02-15T00:00:00.000' AS DateTime), N'1240', CAST(3000 AS Decimal(18, 0)))

SET IDENTITY_INSERT [dbo].[tbl_purchase] OFF

GO

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

Answer 1

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