Sum With Over PARTITION Support?

[rbsdotnet]

Hello Does this package support Over PARTITION in the Sum function? for example :

SUM(ISNULL(BillRows.Debtor, 0) - ISNULL(BillRows.Creditor, 0)) OVER (PARTITION BY AccountType
                                                                                                 ORDER BY T.AccountType,
                                                                                                          Date,
                                                                                                          BillNumber,
                                                                                                          RowId)

[PawelGerr]

The released versions supports ROW_NUMBER only.

I just made a POC for SUM (SUM tests) and it looks good.

With PARTITION BY only:

var result = ActDbContext.TestEntities
                         .Select(e => new
                                      {
                                         e.Name,
                                         Sum = EF.Functions.Sum(e.Count * 2, e.Name)
                                      })
                         .ToList();

generates

SELECT
   [t].[Name],
   SUM ([t].[Count] * 2) OVER (PARTITION BY [t].[Name]) AS [Sum]
FROM [tests_6].[TestEntities] AS [t]

and with ORDER BY

var result = ActDbContext.TestEntities
                         .Select(e => new
                                      {
                                         e.Name,
                                         e.Count,
                                         Sum = EF.Functions.Sum(e.Count, e.Name, EF.Functions.OrderBy(e.Count))
                                      })
                         .ToList();

generates

SELECT
   [t].[Name],
   [t].[Count],
   SUM ([t].[Count]) OVER (PARTITION BY [t].[Name] ORDER BY [t].[Count]) AS [Sum]
FROM [tests_6].[TestEntities] AS [t]

I'll try to find more time in the following weeks to add more aggregate functions, check for NULL handling and write additional tests.

[rbsdotnet]

Very cool, I'm surprised, when will the final version be ready? Is it available for dot net version 7 now?

[PawelGerr]

I released a preview version 7.3.0-beta01 for EF Core 7. I implemented Sum, Average, Min and Max. Please take a look at the window functions and give me feedback.

[rbsdotnet]

After receiving and installing the package, I encountered the following error in the sum function Incorrect syntax near '.'. Incorrect syntax near '.'.

After searching the codes, the problem is in the SqlServerDbContextOptionsExtension class And it was the AddCustomQuerySqlGeneratorFactory feature that solved the problem after adding the "AddWindowFunctionsSupport" feature

public bool AddCustomQuerySqlGeneratorFactory
{
   get => _addCustomQuerySqlGeneratorFactory || AddBulkOperationSupport || AddTenantDatabaseSupport || AddWindowFunctionsSupport || AddTableHintSupport;
   set => _addCustomQuerySqlGeneratorFactory = value;
}

[PawelGerr]

You are right, I forgot to tell you that the feature must be activated first. Besides that, is it working as expected?

[rbsdotnet]

Yes, it works accurately and without errors, and in terms of translation into sql language, it is excellent and flawless

[PawelGerr]

If release v7.3.0 for EF Core 7 and v8.0.0-beta05 for EF Core 8

[rbsdotnet]

I Activate AddWindowFunctionsSupport()

services.AddDbContextPool<ApplicationDbContext>((serviceProvider, optionsBuilder) =>
{
    optionsBuilder.UseSqlServer(applicationSettingModel.SqlServerDB.ConnectionString, serverDbContextOptionsBuilder =>
    {
        serverDbContextOptionsBuilder.CommandTimeout((int)TimeSpan.FromMinutes(3).TotalSeconds);
        serverDbContextOptionsBuilder.UseNetTopologySuite();
        serverDbContextOptionsBuilder.UseDateOnlyTimeOnly();
        serverDbContextOptionsBuilder.AddWindowFunctionsSupport();
    });
});

The following code is not added in the SqlServerDbContextOptionsExtension class and it still gives the previous error

Cuurent Version : 7.3.0

  public bool AddCustomQuerySqlGeneratorFactory
   {
      get => _addCustomQuerySqlGeneratorFactory || AddBulkOperationSupport || AddTenantDatabaseSupport || AddTableHintSupport;
      set => _addCustomQuerySqlGeneratorFactory = value;
   }

The following feature is missing from the conditions : AddWindowFunctionsSupport

public bool AddCustomQuerySqlGeneratorFactory
{
   get => _addCustomQuerySqlGeneratorFactory || AddBulkOperationSupport || AddTenantDatabaseSupport || AddWindowFunctionsSupport || AddTableHintSupport;
   set => _addCustomQuerySqlGeneratorFactory = value;
}

[PawelGerr]

7.3.1 / 8.0.0-beta06 are available now

[rbsdotnet]

The new version was received and I tested it on the main project and the problem was completely solved. Great, thank you