EF Core (Entity Framework Core) 9 adalah ORM framework versi terbaru yang disempurnakan yang ditujukan untuk pengembangan aplikasi berbasis .NET sehingga pengembang dapat bekerja dan mengembangkan aplikasi dalam lingkungan object/class berbasis .NET. EF Core 9 memang dirancang untuk menggunakan versi .NET 9 terbaru dan memanfaatkan peningkatan performa yang didapat dari SQL Server versi terbaru, terutama fitur terbaru SQL SERVER 2022. Akan tetapi , beberapa use case penggunaan EF Core terkadang tidak sesuai ekspektasi yang kita bayangkan, karena ternyata masih banyak perusahaan kecil terutama sektor Usaha Kecil Menengah (UMKM) / Small to Medium Enterprise (SME) yang menggunakan Sql Server 2008. Apabila dilansir dari website microsoft itu sendiri , lifecycle dari SQL Server yang masih disupport di tahun 2025 ini adalah SQL Server 2016 ke atas, karena diharapkan para pengguna SQL Server telah mengupgrade atau menggunakan SQL Server terbaru untuk dapat memanfaatkan peningkatan dan fitur baru yang ada.
Jika kita menggunakan EF Core 9 dengan database Sql Server 2008, besar kemungkinan terjadi error ketika dilakukan LINQ dan select data ke database. Seperti yang kita ketahui, framework ORM seperti entity framework ini bekerja dengan mentranslasikan fungsi fungsi pada .NET dan object entity .NET ke dalam perintah SELECT ke tabel database yang sudah dimappingkan dengan class entity terkait. Translasi ke dialek SQL inilah yang menjadi akar permasalahan ketika kita menggunakan SQL Server 2008 ke bawah yang sudah tidak disupport. Beberapa error terjadi antara lain
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement.Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statementContoh tersebut error karena driver yang bertugas berinteraksi dengan SQL Server dan men-generate perintah query SQL ke database menghasilkan perintah query yang belum didukung oleh SQL Server 2008 namun justru merupakan query paling optimal jika dijalankan di SQL Server 2022. Query yang dihasilkan untuk select dan paging oleh EF Core 9, ternyata menggunakan OFFSET dan FETCH, sementara penggunakan OFFSET dan FETCH baru dikenalkan mulai SQL Server 2012.
Solusi
Opsi pertama, Langkah paling mudah adalah mengganti database server menjadi SQL Server 2022 atau yang terbaru. Tentu hal ini tidak semua bisa dilakukan , terutama jika memang belum ada opsi upgrade server di perusahaan sementara data yang masih dipakai juga lumayan besar, kecil kemungkinan menggunakan opsi SQL Server terbaru.
Opsi kedua adalah menggunakan opsi optionbuilder dengan UseRowNumberForPaging. Namun sayangnya UseRowNumberForPaging ini sudah obsolete dan tidak bisa digunakan sejak .NET Core 3.x, yang artinya opsi tersebut sudah tidak tersedia di EF Core 9 kecuali jika kita menggunakan .NET Core 2 dan EF Core versi lama.
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var coonectionString = "Data Source=localhost\\MSSQL2008;Initial Catalog=AdventureWorks;Integrated Security=True";
optionsBuilder.UseSqlServer(coonectionString, builder => builder.UseRowNumberForPaging());
}
Opsi ketiga adalah mengganti class postprocessor yang digunakan untuk mentranslasikan Linq dari entity framework ke dialek SQL, kita membuat fungsi tiruan dari UseRowNumberForPaging sehingga query yang dihasilkan untuk paging akan menggunakan fungsi SQL RowNumber() alih alih menggunakan sintaks OFFSET FETCH … NEXT.
Pertama kita buat class SqlServer2008QueryTranslationPostprocessorFactory yang bertugas mengubah query SQL agar menggunakan RowNumber.
// File :SqlServer2008QueryTranslationPostprocessorFactory.cs
using System.Collections.Generic;
using System.Linq;
using System;
using System.Linq.Expressions;
using System.Reflection;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
namespace EF
{
public class SqlServer2008QueryTranslationPostprocessorFactory : IQueryTranslationPostprocessorFactory
{
private readonly QueryTranslationPostprocessorDependencies _dependencies;
private readonly RelationalQueryTranslationPostprocessorDependencies _relationalDependencies;
public SqlServer2008QueryTranslationPostprocessorFactory(QueryTranslationPostprocessorDependencies dependencies, RelationalQueryTranslationPostprocessorDependencies relationalDependencies)
{
_dependencies = dependencies;
_relationalDependencies = relationalDependencies;
}
public virtual QueryTranslationPostprocessor Create(QueryCompilationContext queryCompilationContext)
=> new SqlServer2008QueryTranslationPostprocessor(
_dependencies,
_relationalDependencies,
#if NET9_0_OR_GREATER
(RelationalQueryCompilationContext)queryCompilationContext
#else
queryCompilationContext
#endif
);
public class SqlServer2008QueryTranslationPostprocessor : RelationalQueryTranslationPostprocessor
{
public SqlServer2008QueryTranslationPostprocessor(
QueryTranslationPostprocessorDependencies dependencies,
RelationalQueryTranslationPostprocessorDependencies relationalDependencies,
#if NET9_0_OR_GREATER
RelationalQueryCompilationContext queryCompilationContext
#else
QueryCompilationContext queryCompilationContext
#endif
)
: base(dependencies, relationalDependencies, queryCompilationContext)
{
}
public override Expression Process(Expression query)
{
query = base.Process(query);
#if NET9_0_OR_GREATER
query = new Offset2RowNumberConvertVisitor(query, RelationalDependencies.SqlExpressionFactory, RelationalQueryCompilationContext.SqlAliasManager).Visit(query);
#else
query = new Offset2RowNumberConvertVisitor(query, RelationalDependencies.SqlExpressionFactory).Visit(query);
#endif
return query;
}
#if NET9_0_OR_GREATER
// new class for patch on EFCore9 .net9
internal class Offset2RowNumberConvertVisitor(
Expression root,
ISqlExpressionFactory sqlExpressionFactory,
SqlAliasManager sqlAliasManager
) : ExpressionVisitor
{
private readonly Expression root = root;
private readonly ISqlExpressionFactory sqlExpressionFactory = sqlExpressionFactory;
private readonly SqlAliasManager sqlAliasManager = sqlAliasManager;
protected override Expression VisitExtension(Expression node) => node switch
{
ShapedQueryExpression shapedQueryExpression => shapedQueryExpression.Update(Visit(shapedQueryExpression.QueryExpression), Visit(shapedQueryExpression.ShaperExpression)),
SelectExpression se => VisitSelect(se),
_ => base.VisitExtension(node),
};
private SelectExpression VisitSelect(SelectExpression selectExpression)
{
// if we have no offset, we do not need to use ROW_NUMBER for offset calculations
if (selectExpression.Offset == null)
{
return selectExpression;
}
var isRootQuery = selectExpression == root;
// store offset, limit and orderings
var oldOffset = selectExpression.Offset;
var oldLimit = selectExpression.Limit;
var oldOrderings = selectExpression.Orderings;
var newOrderings = oldOrderings.Count > 0 && (oldLimit != null || selectExpression == root)
? oldOrderings.ToList()
: new List<OrderingExpression>();
// remove offset and limit by creating new select expression from old one
// we can't use SelectExpression.Update because that breaks PushDownIntoSubquery
var enhancedSelect = new SelectExpression(
alias: null,
tables: new(selectExpression.Tables),
predicate: selectExpression.Predicate,
groupBy: new(selectExpression.GroupBy),
having: selectExpression.Having,
projections: new(selectExpression.Projection),
distinct: selectExpression.IsDistinct,
//orderings: isRootQuery ? new(selectExpression.Orderings) : [],
orderings: newOrderings,
offset: null,
limit: null,
tags: selectExpression.Tags,
annotations: null,
sqlAliasManager: sqlAliasManager,
isMutable: true
);
// set up row_number expression
var rowOrderings = isRootQuery || oldOrderings.Count != 0 ?
oldOrderings
: [new(new SqlFragmentExpression("(SELECT 1)"), true)];
//var rowNumber = new RowNumberExpression([], !isRootQuery ? [new(new SqlFragmentExpression("(SELECT 1)"), true)] : oldOrderings, oldOffset.TypeMapping);
var rowNumber = new RowNumberExpression([], rowOrderings, oldOffset.TypeMapping);
enhancedSelect.AddToProjection(rowNumber);
enhancedSelect.PushdownIntoSubquery();
// restore ordering to outer select after earlier removal
if (isRootQuery)
{
foreach (var orderingClause in oldOrderings)
{
selectExpression.AppendOrdering(orderingClause);
}
}
// generate subselect rownumber access expression
var innerTable = enhancedSelect.Tables[0];
var rowNumberColname = enhancedSelect.Projection[enhancedSelect.Projection.Count - 1].Alias;
var rowNumberAlias = enhancedSelect.CreateColumnExpression(innerTable, rowNumberColname, typeof(int), null, false);
// apply offset and limit
var rowNumberGtOffset = sqlExpressionFactory.GreaterThan(rowNumberAlias, oldOffset);
enhancedSelect.ApplyPredicate(rowNumberGtOffset);
if (oldLimit != null)
{
if (oldOrderings.Count == 0)
{
var rowNumberLimiting = sqlExpressionFactory.LessThanOrEqual(rowNumberAlias, sqlExpressionFactory.Add(oldOffset, oldLimit));
enhancedSelect.ApplyPredicate(rowNumberLimiting);
}
else
{
enhancedSelect.ApplyLimit(oldLimit);
}
}
enhancedSelect.ApplyProjection(); // to make immutable
var restoredProjections = enhancedSelect.Projection
.Where(p => p.Alias != rowNumberColname)
.ToList();
var result = enhancedSelect.Update(
enhancedSelect.Tables,
enhancedSelect.Predicate,
enhancedSelect.GroupBy,
enhancedSelect.Having,
restoredProjections,
enhancedSelect.Orderings,
enhancedSelect.Offset,
enhancedSelect.Limit
);
// restore projection member binding lookup capabilities via reflection magic
var clientProjections = typeof(SelectExpression).GetField("_clientProjections", BindingFlags.NonPublic | BindingFlags.Instance);
clientProjections.SetValue(result, clientProjections.GetValue(selectExpression));
var projectionMapping = typeof(SelectExpression).GetField("_projectionMapping", BindingFlags.NonPublic | BindingFlags.Instance);
projectionMapping.SetValue(result, projectionMapping.GetValue(selectExpression));
return result;
}
}
#endif
#if !NET9_0_OR_GREATER
internal class Offset2RowNumberConvertVisitor : ExpressionVisitor
{
#if !NET5_0
private static readonly MethodInfo GenerateOuterColumnAccessor;
private static readonly Type TableReferenceExpressionType;
#else
private static readonly Func<SelectExpression, SqlExpression, string, ColumnExpression> GenerateOuterColumnAccessor;
#endif
private readonly Expression root;
private readonly ISqlExpressionFactory sqlExpressionFactory;
static Offset2RowNumberConvertVisitor()
{
var method = typeof(SelectExpression).GetMethod("GenerateOuterColumn", BindingFlags.NonPublic | BindingFlags.Instance);
if (!typeof(ColumnExpression).IsAssignableFrom(method?.ReturnType))
throw new InvalidOperationException("SelectExpression.GenerateOuterColumn() is not found.");
#if !NET5_0
TableReferenceExpressionType = method.GetParameters().First().ParameterType;
GenerateOuterColumnAccessor = method;
#else
GenerateOuterColumnAccessor = (Func<SelectExpression, SqlExpression, string, ColumnExpression>)method.CreateDelegate(typeof(Func<SelectExpression, SqlExpression, string, ColumnExpression>));
#endif
}
public Offset2RowNumberConvertVisitor(Expression root, ISqlExpressionFactory sqlExpressionFactory)
{
this.root = root;
this.sqlExpressionFactory = sqlExpressionFactory;
}
protected override Expression VisitExtension(Expression node)
{
if (node is ShapedQueryExpression shapedQueryExpression)
{
return shapedQueryExpression.Update(Visit(shapedQueryExpression.QueryExpression), shapedQueryExpression.ShaperExpression);
}
if (node is SelectExpression se)
node = VisitSelect(se);
return base.VisitExtension(node);
}
private Expression VisitSelect(SelectExpression selectExpression)
{
var oldOffset = selectExpression.Offset;
if (oldOffset == null)
return selectExpression;
var oldLimit = selectExpression.Limit;
var oldOrderings = selectExpression.Orderings;
var newOrderings = oldOrderings.Count > 0 && (oldLimit != null || selectExpression == root)
? oldOrderings.ToList()
: new List<OrderingExpression>();
selectExpression = selectExpression.Update(selectExpression.Projection.ToList(),
selectExpression.Tables.ToList(),
selectExpression.Predicate,
selectExpression.GroupBy.ToList(),
selectExpression.Having,
orderings: newOrderings,
limit: null,
offset: null);
var rowOrderings = oldOrderings.Count != 0 ? oldOrderings
: new[] { new OrderingExpression(new SqlFragmentExpression("(SELECT 1)"), true) };
selectExpression.PushdownIntoSubquery();
var subQuery = (SelectExpression)selectExpression.Tables[0];
var projection = new RowNumberExpression(Array.Empty<SqlExpression>(), rowOrderings, oldOffset.TypeMapping);
#if !NET5_0
var left = GenerateOuterColumnAccessor.Invoke(subQuery
, new object[]
{
Activator.CreateInstance(TableReferenceExpressionType, new object[] { subQuery,subQuery.Alias! })!,
projection,
"row",
true
}) as ColumnExpression;
#else
var left = GenerateOuterColumnAccessor(subQuery, projection, "row");
#endif
selectExpression.ApplyPredicate(sqlExpressionFactory.GreaterThan(left!, oldOffset));
if (oldLimit != null)
{
if (oldOrderings.Count == 0)
{
selectExpression.ApplyPredicate(sqlExpressionFactory.LessThanOrEqual(left, sqlExpressionFactory.Add(oldOffset, oldLimit)));
}
else
{
selectExpression.ApplyLimit(oldLimit);
}
}
return selectExpression;
}
}
#endif
}
}
}
Selanjutnya, kita buat class SqlServerDbContextOptionsBuilderExtensions agar supaya bisa menggunakan UseRowNumberForPaging saat mengatur DB Options. Berikut kode SqlServerDbContextOptionsBuilderExtensions
// File: SqlServerDbContextOptionsBuilderExtensions.cs
using System;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Query;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EF
{
public static class SqlServerDbContextOptionsBuilderExtensions
{
public static SqlServerDbContextOptionsBuilder UseRowNumberForPaging(this SqlServerDbContextOptionsBuilder optionsBuilder)
{
((IRelationalDbContextOptionsBuilderInfrastructure)optionsBuilder).OptionsBuilder
.ReplaceService<IQueryTranslationPostprocessorFactory, SqlServer2008QueryTranslationPostprocessorFactory>();
return optionsBuilder;
}
}
}
Apabila dilihat di solution view, maka di project kita seharusnya sudah ada dua file tambahan yang telah kita buat seperti pada gambar 2 berikut
Langkah terakhir adalah memanggil dbcontext dengan parameter tambahan option yang menggunakan UseRowNumberForPaging. Perhatikan kode berikut ini
using EF;
using EF.AppDbContext;
using EF.Entities;
using EF.Models;
using Microsoft.EntityFrameworkCore;
namespace ConsoleApp123
{
internal class Program
{
static void Main(string[] args)
{
Console.WriteLine("Hello, World!");
// persiapkan connection string bisa dari membaca appsettings.json
var connectionstring = $"Data Source=.\\MSSQL2008;Initial Catalog=AdventureWork2008;Persist Security Info=True;Integrated Security=true;MultipleActiveResultSets=False;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;";
// create new option object
var options = new DbContextOptionsBuilder<AppDbContext>();
options.UseSqlServer(connectionstring, opt => opt.UseCompatibilityLevel(100).UseRowNumberForPaging());
// new db context dengan parameter tambahan option
var dbContext = new AppDbContext(options.Options);
var salesGendut = dbContext.SalesPeople.Where(s => s.Bonus > 2000).ToList();
Console.WriteLine("Sales dengan bonus diatas 2000 USD :");
for (int i = 0; i < salesGendut.Count; i++)
{
int nomer = i + 1;
Console.WriteLine($"{nomer}. ID {salesGendut[i].BusinessEntityID} has bonus USD {salesGendut[i].Bonus:#,##0.00}");
}
}
}
}
Jika kita periksa, perubahan kode tersebut nampak jelas dimulai dari pembuatan options yang mendukung UseRowNumberForPaging dan membuat new object dari AppDbContext dengan parameter option tersebut.
// create new option object
var options = new DbContextOptionsBuilder<AppDbContext>();
options.UseSqlServer(connectionstring, opt => opt.UseCompatibilityLevel(100).UseRowNumberForPaging());
// new db context dengan parameter tambahan option
var dbContext = new AppDbContext(options.Options);
