dotnet / dotnet/efcore

SQL Server Analytic functions

Open
#28,037 4 comments 1 reaction 0 assignees View on GitHub
area-query area-sqlserver customer-reported
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.