zomp-efcore-extensions - Zomp.EFCore.WindowFunctions.SqlServer 8.0.15-beta
Window functions for SQL Server database provider for Entity Framework Core
PM> Install-Package Zomp.EFCore.WindowFunctions.SqlServer -Version 8.0.15-beta -Source https://www.myget.org/F/zomp-efcore-extensions/api/v3/index.json
> nuget.exe install Zomp.EFCore.WindowFunctions.SqlServer -Version 8.0.15-beta -Source https://www.myget.org/F/zomp-efcore-extensions/api/v3/index.json
> dotnet add package Zomp.EFCore.WindowFunctions.SqlServer --version 8.0.15-beta --source https://www.myget.org/F/zomp-efcore-extensions/api/v3/index.json
<PackageReference Include="Zomp.EFCore.WindowFunctions.SqlServer" Version="8.0.15-beta" />
Copy to clipboard
source https://www.myget.org/F/zomp-efcore-extensions/api/v3/index.json
nuget Zomp.EFCore.WindowFunctions.SqlServer ~> 8.0.15-beta
Copy to clipboard
> choco install Zomp.EFCore.WindowFunctions.SqlServer --version 8.0.15-beta --source https://www.myget.org/F/zomp-efcore-extensions/api/v2
Import-Module PowerShellGet
Register-PSRepository -Name "zomp-efcore-extensions" -SourceLocation "https://www.myget.org/F/zomp-efcore-extensions/api/v2"
Install-Module -Name "Zomp.EFCore.WindowFunctions.SqlServer" -RequiredVersion "8.0.15-beta" -Repository "zomp-efcore-extensions" -AllowPreRelease
Copy to clipboard
Browse the sources in this package using Visual Studio or WinDbg by configuring the following symbol server URL: https://www.myget.org/F/zomp-efcore-extensions/api/v2/symbolpackage/
Zomp EF Core Extensions
This repository is home to two packages which extend Entity Framework Core:
- Zomp.EFCore.WindowFunctions
- Zomp.EFCore.BinaryFunctions
Zomp.EFCore.WindowFunctions
Provides Window functions or analytics functions for providers. Currently supported for:
Provider | Package |
---|---|
SQL Server | |
PostgreSQL | |
SQLite |
Window functions supported:
- MIN
- MAX
- SUM
- AVG
- COUNT
- ROW_NUMBER
- RANK
- DENSE_RANK
- PERCENT_RANK
- LEAD
- LAG
Installation
To add provider-specific library use:
dotnet add package Zomp.EFCore.WindowFunctions.SqlServer
dotnet add package Zomp.EFCore.WindowFunctions.Npgsql
dotnet add package Zomp.EFCore.WindowFunctions.Sqlite
To add provider-agnostic library use:
dotnet add package Zomp.EFCore.WindowFunctions
Set up your specific provider to use Window Functions with DbContextOptionsBuilder.UseWindowFunctions
. For example here is the SQL Server syntax:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
myConn,
sqlOptions => sqlOptions.UseWindowFunctions());
}
Basic usage
LINQ query
using var dbContext = new MyDbContext();
var query = dbContext.TestRows
.Select(r => new
{
Max = EF.Functions.Max(
r.Col1,
EF.Functions.Over()
.OrderBy(r.Col2)),
});
translates into the following SQL on SQL Server:
SELECT MAX([t].[Col1]) OVER(ORDER BY [t].[Col2]) AS [Max]
FROM [TestRows] AS [t]
ORDER BY [t].[Id]
Advanced usage
This example shows:
- Partition clause (can be chained)
- Order by clause
- Can me chained
- Used in ascending or descending order
- Range or Rows clause
using var dbContext = new MyDbContext();
var query = dbContext.TestRows
.Select(r => new
{
Max = EF.Functions.Max(
r.Col1,
EF.Functions.Over()
.PartitionBy(r.Col2).ThenBy(r.Col3)
.OrderBy(r.Col4).ThenByDescending(r.Col5)
.Rows().FromUnbounded().ToCurrentRow()),
});
Zomp.EFCore.BinaryFunctions
Provides Window functions or analytics functions for providers. Currently supported for:
Provider | Package |
---|---|
SQL Server | |
PostgreSQL | |
SQLite |
The following extension methods are available
DbFunctions.GetBytes
- converts an expression into binary expressionDbFunctions.ToValue<T>
- Converts binary expression to type TDbFunctions.BinaryCast<TFrom, TTo>
- Converts one type to another by taking least significant bytes when overflow occurs.DbFunctions.Concat
- concatenates two binary expressionsDbFunctions.Substring
- Returns part of a binary expression
Usage
Set up your specific provider to use Binary Functions with DbContextOptionsBuilder.UseWindowFunctions
. For example here is the SQL Server syntax:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
myConn,
sqlOptions => sqlOptions.UseBinaryFunctions());
}
LINQ query
using var dbContext = new MyDbContext();
var query = dbContext.TestRows
.Select(r => EF.Functions.GetBytes(r.Id));
translates into the following SQL on SQL Server:
SELECT CAST([t].[Id] AS binary(4))
FROM [TestRows] AS [t]
Applications
Last non null puzzle
One problem window functions are solving is displaying last non-null values for a given column / expressions. The problem is described in Itzik Ben-Gan's article. Below are 2 effective approaches of solving this issue.
Binary approach
Solution 2 of the article above uses both binary functions and window functions. Here is how it can be combined using this library:
// Relies on Max over binary.
// Currently works with SQL Server only.
var query = dbContext.TestRows
.Select(r => new
{
LastNonNull =
EF.Functions.ToValue<int>(
EF.Functions.Substring(
EF.Functions.Max(
EF.Functions.Concat(
EF.Functions.GetBytes(r.Id),
EF.Functions.GetBytes(r.Col1)),
EF.Functions.Over().OrderBy(r.Id)),
5,
4)),
});
In case of limitations of combining bytes (SQLite) and window max function on binary data (PostgreSQL) it might be possible to combine columns into 8-bit integer expression(s) and perform max window function on it:
var query = dbContext.TestRows
.Select(r => new
{
LastNonNull =
EF.Functions.BinaryCast<long, int>(
EF.Functions.Max(
r.Col1.HasValue ? r.Id * (1L << 32) | r.Col1.Value & uint.MaxValue : (long?)null,
EF.Functions.Over().OrderBy(r.Id))),
});
LAG approach
Starting with SQL Server 2022 (16.x) it is possible to use LAG with IGNORE NULLS to retrieve last non-null value. Ensure the latest cumulative update is applied due to a bug fix.
Use the following expression:
Expression<Func<TestRow, int?>> lastNonNullExpr = r => EF.Functions.Lag(r.Col1, 0, NullHandling.IgnoreNulls, EF.Functions.Over().OrderBy(r.Id)
More SQL Server related information on the LAG function here available here.
Note: PostgreSQL and SQLite don't support RESPECT NULLS / IGNORE NULLS at this time.
Examples
See the
- Zomp.EFCore.WindowFunctions.Testing
- Zomp.EFCore.BinaryFunctions.Testing
- Zomp.EFCore.Combined.Testing
projects for more examples.
-
.NETFramework 8.0
- Microsoft.EntityFrameworkCore.SqlServer (>= 8.0.0)
- Zomp.EFCore.WindowFunctions (>= 8.0.15-beta)
- .NETFramework 8.0: 8.0.0.0
Signature validation information
Informational
Signature Hash Algorithm: SHA256 Timestamp: 1/31/2024 11:20:31 PM Verifying author primary signature's timestamp with timestamping service certificate: Subject Name: CN=DigiCert Timestamp 2023, O="DigiCert, Inc.", C=US SHA1 hash: 66F02B32C2C2C90F825DCEAA8AC9C64F199CCF40 SHA256 hash: D2F6E46DED7422CCD1D440576841366F828ADA559AAE3316AF4D1A9AD40C7828 Issued by: CN=DigiCert Trusted G4 RSA4096 SHA256 TimeStamping CA, O="DigiCert, Inc.", C=US Valid from: 7/14/2023 12:00:00 AM to 10/13/2034 11:59:59 PM Signature type: Author Verifying the author primary signature with certificate: Subject Name: CN=Zomp Inc., O=Zomp Inc., S=Ontario, C=CA SHA1 hash: 4AB0F2CBB11B34AF1A7FB18922138F8D946FF5A7 SHA256 hash: 06AB74C00D1B3CE3411F555BA1089030B9867E8DCBA2D0D93F0B03C1B8DB6D62 Issued by: CN=Sectigo Public Code Signing CA R36, O=Sectigo Limited, C=GB Valid from: 2/9/2022 12:00:00 AM to 2/8/2025 11:59:59 PM
OwnersVictor Irzak |
AuthorsVictor Irzak |
Project URLhttps://github.com/zompinc/efcore-extensions |
LicenseUnknown |
SignatureValidation: Valid |
Info3 total downloads |
0 downloads for version 8.0.15-beta |
Download (25.69 KB) |
Download symbols (12.29 KB) |
Found on the current feed only |
Package history
Version | Size | Last updated | Downloads | Mirrored? | |||
---|---|---|---|---|---|---|---|
![]() |
8.0.36-beta-g2c0ae03484 | 26.05 KB | Sun, 18 Feb 2024 23:19:56 GMT | 0 |
|
||
![]() |
8.0.35-beta-g14010d6be2 | 26.05 KB | Sun, 18 Feb 2024 23:05:04 GMT | 0 |
|
||
![]() |
8.0.34-beta-g58ebe81f9a | 26.06 KB | Sun, 18 Feb 2024 21:25:41 GMT | 0 |
|
||
![]() |
8.0.34-beta-g29edbaa8b2 | 26.05 KB | Sun, 18 Feb 2024 21:12:03 GMT | 0 |
|
||
![]() |
8.0.33-beta-gf350025159 | 26.05 KB | Sun, 18 Feb 2024 20:37:54 GMT | 0 |
|
||
![]() |
8.0.31-beta-g318aa15d6e | 26.04 KB | Sat, 17 Feb 2024 22:22:17 GMT | 0 |
|
||
![]() |
8.0.30-beta-g648b99eabe | 26.05 KB | Sat, 17 Feb 2024 17:09:28 GMT | 0 |
|
||
![]() |
8.0.29-g5fff5afefb | 25.15 KB | Tue, 02 Jan 2024 00:35:31 GMT | 2 |
|
||
![]() |
8.0.29-beta-g727c28cfac | 26.05 KB | Sat, 17 Feb 2024 13:50:43 GMT | 0 |
|
||
![]() |
8.0.28-gf6db188097 | 25.15 KB | Mon, 01 Jan 2024 16:36:38 GMT | 0 |
|
||
![]() |
8.0.28-beta-gc738f1c905 | 26.05 KB | Sat, 17 Feb 2024 13:19:42 GMT | 0 |
|
||
![]() |
8.0.27-g6095ddd124 | 25.15 KB | Mon, 01 Jan 2024 14:49:04 GMT | 0 |
|
||
![]() |
8.0.27-beta-gcd47480703 | 26.05 KB | Sat, 17 Feb 2024 03:58:56 GMT | 0 |
|
||
![]() |
8.0.27-beta-gb193f77fa0 | 26.05 KB | Sat, 17 Feb 2024 03:34:15 GMT | 0 |
|
||
![]() |
8.0.27-beta-g50c5227a31 | 26.05 KB | Sat, 17 Feb 2024 04:24:11 GMT | 0 |
|
||
![]() |
8.0.26-beta-gbb9f617438 | 26.05 KB | Fri, 16 Feb 2024 23:17:49 GMT | 0 |
|
||
![]() |
8.0.25-beta-ga2d489f8b9 | 26.05 KB | Fri, 16 Feb 2024 21:34:49 GMT | 0 |
|
||
![]() |
8.0.24-beta-ge75b9458e1 | 26.05 KB | Fri, 16 Feb 2024 19:13:01 GMT | 0 |
|
||
![]() |
8.0.24-beta-g47ee3d0ce6 | 26.08 KB | Mon, 15 Apr 2024 15:55:11 GMT | 0 |
|
||
![]() |
8.0.24-beta | 26.06 KB | Mon, 15 Apr 2024 16:15:01 GMT | 0 |
|
||
![]() |
8.0.23-beta-g14cb26cd44 | 26.05 KB | Fri, 16 Feb 2024 15:32:02 GMT | 0 |
|
||
![]() |
8.0.23-beta | 26.04 KB | Tue, 20 Feb 2024 03:35:47 GMT | 0 |
|
||
![]() |
8.0.22-beta | 26.04 KB | Sun, 18 Feb 2024 23:37:44 GMT | 0 |
|
||
![]() |
8.0.21-beta | 26.03 KB | Tue, 13 Feb 2024 22:27:28 GMT | 0 |
|
||
![]() |
8.0.20-beta | 26.01 KB | Fri, 09 Feb 2024 04:46:27 GMT | 0 |
|
||
![]() |
8.0.19-beta-g445b75ca4d | 25.71 KB | Wed, 07 Feb 2024 06:19:22 GMT | 0 |
|
||
![]() |
8.0.19-beta | 26.02 KB | Thu, 08 Feb 2024 21:10:01 GMT | 0 |
|
||
![]() |
8.0.18-beta-g4757e3bd64 | 26.03 KB | Thu, 08 Feb 2024 15:17:27 GMT | 0 |
|
||
![]() |
8.0.18-beta-g1c140bb6c0 | 25.7 KB | Wed, 07 Feb 2024 06:10:25 GMT | 0 |
|
||
![]() |
8.0.18-beta | 26.01 KB | Thu, 08 Feb 2024 15:24:03 GMT | 0 |
|
||
![]() |
8.0.17-beta-g9efbb28a44 | 25.71 KB | Thu, 08 Feb 2024 15:13:28 GMT | 0 |
|
||
![]() |
8.0.17-beta | 25.69 KB | Mon, 05 Feb 2024 14:48:23 GMT | 0 |
|
||
![]() |
8.0.15-beta | 25.69 KB | Wed, 31 Jan 2024 23:20:41 GMT | 0 |
|
||
![]() |
8.0.14-beta | 25.69 KB | Mon, 29 Jan 2024 18:48:20 GMT | 0 |
|
||
![]() |
8.0.13-beta | 25.69 KB | Mon, 29 Jan 2024 07:00:56 GMT | 0 |
|
||
![]() |
8.0.12 | 25.13 KB | Sun, 07 Jan 2024 00:08:35 GMT | 0 |
|
||
![]() |
8.0.11 | 25.14 KB | Wed, 03 Jan 2024 03:32:14 GMT | 0 |
|
||
![]() |
8.0.10 | 25.14 KB | Wed, 03 Jan 2024 01:04:00 GMT | 0 |
|
||
![]() |
8.0.9 | 25.14 KB | Mon, 01 Jan 2024 14:45:01 GMT | 0 |
|
||
![]() |
8.0.8-g2db32e196d | 25.14 KB | Mon, 01 Jan 2024 03:03:06 GMT | 0 |
|
||
![]() |
8.0.6 | 25.2 KB | Wed, 27 Dec 2023 21:10:20 GMT | 0 |
|
||
![]() |
8.0.5 | 25.19 KB | Tue, 26 Dec 2023 17:39:09 GMT | 0 |
|
||
![]() |
8.0.2 | 25.2 KB | Sun, 10 Dec 2023 20:52:12 GMT | 0 |
|
||
![]() |
8.0.1 | 25.17 KB | Sun, 10 Dec 2023 19:30:32 GMT | 0 |
|
||
![]() |
8.0.1-rc2 | 24.8 KB | Thu, 12 Oct 2023 11:19:53 GMT | 0 |
|
||
![]() |
8.0.1-rc1 | 24.79 KB | Thu, 14 Sep 2023 19:13:56 GMT | 0 |
|
||
![]() |
8.0.1-preview7 | 24.82 KB | Fri, 18 Aug 2023 19:55:16 GMT | 0 |
|
||
![]() |
8.0.1-preview5 | 24.82 KB | Fri, 18 Aug 2023 18:05:42 GMT | 0 |
|
||
![]() |
8.0.1-preview4-gbf5a8389b5 | 25 KB | Sun, 21 May 2023 02:10:57 GMT | 0 |
|
||
![]() |
8.0.1-preview4-gb6bef50d13 | 25.01 KB | Tue, 30 May 2023 15:47:57 GMT | 0 |
|
||
![]() |
8.0.1-preview4-g0b9cdd0033 | 25.01 KB | Tue, 30 May 2023 15:25:47 GMT | 0 |
|
||
![]() |
8.0.1-preview4 | 25 KB | Tue, 30 May 2023 16:07:59 GMT | 0 |
|
||
![]() |
7.0.5 | 24.84 KB | Fri, 16 Dec 2022 06:08:27 GMT | 1 |
|
||
![]() |
7.0.4 | 24.96 KB | Thu, 09 Mar 2023 05:34:57 GMT | 0 |
|
||
![]() |
7.0.3 | 24.95 KB | Thu, 09 Mar 2023 05:26:39 GMT | 0 |
|
||
![]() |
7.0.2 | 24.95 KB | Tue, 07 Mar 2023 23:57:43 GMT | 0 |
|
||
![]() |
7.0.1 | 24.94 KB | Fri, 16 Dec 2022 19:18:48 GMT | 0 |
|
||
![]() |
7.0.1-g2a5277ad59 | 24.95 KB | Fri, 16 Dec 2022 18:27:18 GMT | 0 |
|
||
![]() |
6.0.5 | 24.76 KB | Fri, 16 Dec 2022 06:08:26 GMT | 0 |
|
||
![]() |
1.0.5-g4cdfea9bce | 24.76 KB | Fri, 16 Dec 2022 05:48:01 GMT | 0 |
|
||
![]() |
1.0.5-g1583359f49 | 24.85 KB | Fri, 16 Dec 2022 05:31:41 GMT | 0 |
|
||
![]() |
1.0.4-gd593635bc9 | 24.85 KB | Thu, 15 Dec 2022 22:47:40 GMT | 0 |
|
||
![]() |
1.0.3 | 30.16 KB | Thu, 15 Dec 2022 20:07:50 GMT | 0 |
|
||
![]() |
1.0.2 | 30.15 KB | Sun, 13 Nov 2022 19:15:50 GMT | 0 |
|
||
![]() |
1.0.1 | 30.14 KB | Fri, 11 Nov 2022 03:17:25 GMT | 0 |
|
||
![]() |
0.1.28-alpha | 30.12 KB | Wed, 27 Jul 2022 13:38:54 GMT | 0 |
|
||
![]() |
0.1.25-alpha | 30.12 KB | Fri, 22 Jul 2022 13:17:26 GMT | 0 |
|
||
![]() |
0.1.24-alpha | 30.12 KB | Fri, 22 Jul 2022 11:51:26 GMT | 0 |
|