SQL Server Analytic functions
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
Add support for [SQL Server Analytic Functions](https://docs.microsoft.com/en-us/sql/t-sql/functions/analytic-functions-transact-sql?view=sql-server-ver15)
Currently the only supported function is `ROW_NUMBER` but even this function is not supported directly, it's only used internally by the Query pipeline in certain scenarios.
For example `PERCENTILE_CONT` or `PERCENTILE_DISC` are analytic functions which can be used to calculated the median of a dateset. An operation which is overly complicated using other query methods.
Example 1 of Median calculation with Pure SQL:
```SQL
SELECT
(
(SELECT MAX(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
+
(SELECT MIN(Score) FROM
(SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median
```
Example 2 of Median calculation with `PERCENTILE_CONT`
```SQL
SELECT TOP 1 PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Score) OVER (PARTITION BY null) As Median FROM Posts
```
The first query is very hard to translate into Linq (As SELECT TOP PERCENT is not supported). It requires to query the count first, then query the max value from top half and the min value from bottom half and average them)
Desired solution:
Accessing the SQL Server Analytic functions could be done using the `EF.Functions` extensions:
Example:
```CSharp
db.Posts.Select(x => EF.Functions.PercentileCont(0.5, x.Score, null)).First();
```
The above Linq expression would translate to Example 2 SQL.
Dependencies:
* [x] #22957
* [ ] #12747
Contributor guide
Assessment
This issue has not been assessed yet.