Webrox.EntityFrameworkCore

Use the repo on GitHub to create issues and feature requests.

Features

Implement various Linq functions with Entity Framework Core

SQL	Linq
ROW_NUMBER	EF.Functions.RowNumber
ROW_NUMBER-1	Select((entity, index)=>...)
RANK	EF.Functions.Rank
DENSE_RANK	EF.Functions.DenseRank
AVG	EF.Functions.Average
SUM	EF.Functions.Sum
MIN	EF.Functions.Min
MAX	EF.Functions.Max

Note : Use the method IQueryable<T>.AsSubQuery() to use window functions in predicates or sorts.

SQL Method Translations

.NET	Description
String.Equals(string, StringComparison)	case and insensitive case string comparison using collations
String.Contains(string, StringComparison)	case and insensitive case string search using collations

SubQueries

Linq	Description
IQueryable<T>.AsSubQuery()	transform a query into a subquery

Supported providers

Provider	Nuget Package	.net 6	.net 7	.net 8
SQL Server	Microsoft.EntityFrameworkCore.SqlServer	✔️ EF6	✔️ + EF7	✔️ + EF8
SQLite	Microsoft.EntityFrameworkCore.Sqlite	✔️ + EF6	✔️ + EF7	✔️ + EF8
MySQL	MySql.EntityFrameworkCore	✔️ + EF6	✔️ + EF7	✔️ + EF8
PostgreSQL	Npgsql.EntityFrameworkCore.PostgreSQL	✔️ + EF6	✔️ + EF7	✔️ + EF8

Setup

SQLServer

using Webrox.EntityFrameworkCore.SqlServer;

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    // SQL Server
    optionsBuilder.UseSqlServer("connectionstring", opts |  
    {
        opts.AddWebroxFeatures();
    });
}

MySQL

using Webrox.EntityFrameworkCore.MySql;

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseMySQL("connectionstring", opts |  
    {
        opts.AddWebroxFeatures();
    });
}

SQLite

using Webrox.EntityFrameworkCore.Sqlite;

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlite("connectionstring", opts |  
    {
        opts.AddWebroxFeatures();
    });
}

PostgreSQL

using Webrox.EntityFrameworkCore.Postgres;

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql("connectionstring", opts |  
    {
        opts.AddWebroxFeatures();
    });
}

Functions Usage

RowNumber Syntax

EF Functions

using Webrox.EntityFrameworkCore.[Provider];

// simple RowNumber + OrderBy
context.Table1.Select(t => new
{
    Id = t.Id,
    RowNumber = EF.Functions.RowNumber(EF.Functions.OrderBy(t.Id))
}

// complex RowNumber + PartitionBy + OrderBy
context.Table1.Select(t =>  new
{
    Id = t.Id,
    RowNumber = EF.Functions.RowNumber(
            EF.Functions.PartitionBy(t.GroupId)
                        .ThenPartitionBy(t.SubGroupId),
            EF.Functions.OrderByDescending(t.Id)
                        .ThenBy(t.SubId)
            )
}

Linq Select syntax

You can also use the function Select((e, index)=>{}) to access RowNumber.

It will not be ordered or partitionned and the parameter "index" is int.

context.Table1.Select((t, index) =>  new
{
    Id = t.Id,
    RowNumber = index
}

Rank Syntax

using Webrox.EntityFrameworkCore.[Provider];

// simple Rank + OrderBy
context.Table1.Select(t => new
{
    Id = t.Id,
    Rank = EF.Functions.Rank(EF.Functions.OrderBy(t.Id))
}

// complex Rank + PartitionBy + OrderBy
context.Table1.Select(t =>  new
{
    Id = t.Id,
    Rank = EF.Functions.Rank(
            EF.Functions.PartitionBy(t.GroupId)
                        .ThenPartitionBy(t.SubGroupId),
            EF.Functions.OrderByDescending(t.Id)
                        .ThenBy(t.SubId)
            )
}

DenseRank Syntax

using Webrox.EntityFrameworkCore.[Provider];

// simple DenseRank + OrderBy
context.Table1.Select(t => new
{
    Id = t.Id,
    DenseRank = EF.Functions.DenseRank(EF.Functions.OrderBy(t.Id))
}

// complex DenseRank + PartitionBy + OrderBy
context.Table1.Select(t =>  new
{
    Id = t.Id,
    DenseRank = EF.Functions.DenseRank(
            EF.Functions.PartitionBy(t.GroupId)
                        .ThenPartitionBy(t.SubGroupId),
            EF.Functions.OrderByDescending(t.Id)
                        .ThenBy(t.SubId)
            )
}

Average Syntax

using Webrox.EntityFrameworkCore.[Provider];

// simple Average + OrderBy
context.Table1.Select(t => new
{
    Id = t.Id,
    Average = EF.Functions.Average(EF.Functions.OrderBy(t.Id))
}

// complex Average + PartitionBy + OrderBy
context.Table1.Select(t =>  new
{
    Id = t.Id,
    Average = EF.Functions.Average(
            EF.Functions.PartitionBy(t.GroupId)
                        .ThenPartitionBy(t.SubGroupId),
            EF.Functions.OrderByDescending(t.Id)
                        .ThenBy(t.SubId)
            )
}

Sum Syntax

using Webrox.EntityFrameworkCore.[Provider];

// simple Sum + OrderBy
context.Table1.Select(t => new
{
    Id = t.Id,
    Sum = EF.Functions.Sum(EF.Functions.OrderBy(t.Id))
}

// complex Sum + PartitionBy + OrderBy
context.Table1.Select(t =>  new
{
    Id = t.Id,
    Sum = EF.Functions.Sum(
            EF.Functions.PartitionBy(t.GroupId)
                        .ThenPartitionBy(t.SubGroupId),
            EF.Functions.OrderByDescending(t.Id)
                        .ThenBy(t.SubId)
            )
}

Min Syntax

using Webrox.EntityFrameworkCore.[Provider];

// simple Min + OrderBy
context.Table1.Select(t => new
{
    Id = t.Id,
    Min = EF.Functions.Min(EF.Functions.OrderBy(t.Id))
}

// complex Min + PartitionBy + OrderBy
context.Table1.Select(t =>  new
{
    Id = t.Id,
    Min = EF.Functions.Min(
            EF.Functions.PartitionBy(t.GroupId)
                        .ThenPartitionBy(t.SubGroupId),
            EF.Functions.OrderByDescending(t.Id)
                        .ThenBy(t.SubId)
            )
}

Max Syntax

using Webrox.EntityFrameworkCore.[Provider];

// simple Max + OrderBy
context.Table1.Select(t => new
{
    Id = t.Id,
    Max = EF.Functions.Max(EF.Functions.OrderBy(t.Id))
}

// complex Max + PartitionBy + OrderBy
context.Table1.Select(t =>  new
{
    Id = t.Id,
    Max = EF.Functions.Max(
            EF.Functions.PartitionBy(t.GroupId)
                        .ThenPartitionBy(t.SubGroupId),
            EF.Functions.OrderByDescending(t.Id)
                        .ThenBy(t.SubId)
            )
}