Support for custom windows functions like RANK or DENSE_RANK?

[InspiringCode]

Is there any way to add custom window functions like RANK or DENSE_RANK (which are used quite often in practice imho)? Looking at SqlServerDbFunctionsTranslator.cs it shouldn't be that much effort but I might be wrong.

Would it be an option to add an extension method EF.Functions.WindowFunction(string functionName, [... rest or parameters ...])? In this way we could easily use any custom window function that is not supported out of the box, even the more exotic ones. In the end the SQL follows the same structure for all (most?) window functions any way.

I really like the Thinktecture.EntityFrameworkCore package but I am stuck at the moment because I need the DENSE_RANK function for a business use case. I would really appreciate any help!

[PawelGerr]

An API for a general method is not that easy. Some functions like SUM ( [ ALL | DISTINCT ] expression ) need a method argument/expression but don't require partiton-by/order-by, others like DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > ) don't have an expression but required partition-by/order-by-clause.
Futhermore, providing a collection of parameters, won't work because I need to know whether and how much of them are method arguments and whether there is a partition-by-clause.

[InspiringCode]

@PawelGerr Thank you for your answer! You are right, it probably isn't that simple. Maybe a syntax similar to the following would be an option?

EF.Functions.WindowFunction(
    "LAG",
    e.Name, // Arg 1
    3,      // Arg 2
    EF.Functions.PartitionBy(e.Department),
    EF.Functions.OrderByDescending(e.Salary))

But I understand that something like this would require some effort. Would it be possible to add some extension points to your library so that an app developer could write their own extension methods like RowNumber or Rank (at least for the simpler cases without arguments)? If not, would it be an option for you to add some more commonly used windows functions like RANK or DENSE_RANK?

[PawelGerr]

I'll watch it on the weekend.

[InspiringCode]

Thank you for looking into it. It would also be great, if you could also support the COUNT window function. In my current project I would specifically need the version without any argument (generating COUNT(*) OVER() in SQL). I want to use it to merge the Count-All with the the actual query for pagination queries.

[PawelGerr]

I've just released 2 new pre-releases (7.4.0-beta01 and 8.1.0-beta01).
The new version have a new extension method WindowFunction.

To use the new method, you have to create a new instance of WindowFunction<TReturn> first. This instance specifies the name, the return type and whether to use * when no arguments are provided (for COUNT ( * ))
The isntances of WindowFunction<TResult> should be static because they are immutable and because the name is checked with a Regex "^[A-Z_]+$" and RegexOptions.IgnoreCase.

   private static readonly WindowFunction<int> _averageInt = new("AVG");
   private static readonly WindowFunction<long> _rank = new("RANK");
   private static readonly WindowFunction<int> _count = new("COUNT", useAsteriskWhenNoArguments: true);

Having a WindowFunction<TResult> you can use it with EF.Functions.WindowFunction( ... )

ActDbContext.TestEntities
            .Select(e => new
                         {
                            // 1 arg, 1 partition by
                            Average1 = EF.Functions.WindowFunction(_averageInt, e.Count * 2, EF.Functions.PartitionBy(e.Name)),

                           // 1 arg, 2 partition by columns
                           Average2 = EF.Functions.WindowFunction(_averageInt, e.Count, EF.Functions.PartitionBy(e.Name, e.RequiredName)),

                           // 1 arg, 1 partition by, 1 order by
                           OtherAverage = EF.Functions.WindowFunction(_averageInt, e.Count, EF.Functions.PartitionBy(e.Name), EF.Functions.OrderBy(e.Count)),

                           // No args, no partition by, no order by
                           Count = EF.Functions.WindowFunction(_count)
                         })
            .ToList();

Please check the new method out and let me know whether it works as intended.

[InspiringCode]

@PawelGerr This looks perfect! It finally gives us a powerful solution for all kinds of window functions with EF Core. Thanks a lot for your efforts!

[PawelGerr]

Released