Efcore: Query: Bring back support for UseRowNumberForPaging

[smitpatel]

[ErikEJ]

In 7 days: https://blogs.msdn.microsoft.com/sqlreleaseservices/end-of-mainstream-support-for-sql-server-2008-and-sql-server-2008-r2/

[ajcvickers]

Notes from triage:

• Putting this on the backlog for now; we may implement it post 3.0 if there is enough demand. However, need for this may be correlated code that only runs on .NET Framework, which would make it pointless for 3.0 or beyond.
• We will announce this as a breaking change and call it out in the blog post; keeping this in 3.0 until those two things are done.

[John0King]

🤣 microsoft is merciless to microsoft's own product

[fengshi19888]

if don't suppport the "row_num" for paging ,any other way to solve it besides upgrade database.
many company are still use "sqlserver 2008r2".

[ErikEJ]

Please add a vote for the first post here if you really need this

[effun]

Upgrading database may cost lots of money. Just for paging function? My boss would say NO to me. Then I should have these options.

1. Stay with core 2.2 and cry.
2. Use express edition for higher version of SQL Server. I'm not sure that can be used in commercial.
3. Move to other database product and cry louder.
4. Do nothing.

[ErikEJ]

Express can be used commercially, but has some resource constraints

[John0King]

I think this is what cause the .net community is smaller than other language, even .net is better, the support policy (not just support , in fact it just can not use ) of keep dropping user/devs , make company that use .net less than less those year in China .

and for the rownumber paging feature, juse use SELECT [<UserColumns>] FROM ( SELECT [<UserColumns>], RowNumber() Over (Order by [orderby column]) as ____EF_Row_Column FROM (<orginal sql>) WHERE ____EF_Row_Column between <start> and <end> ) as ___EF_internal )
that will be the same like the new paging function , just there is two more ()

and with this pattern , EF can add a new feature .AsSubQuery() , this is a feature I want for a long ago when there some complex query doesn't work. EF should tread it like a Table/View after .AsSubQuery() , and it will make user can do more complex query in a more “manually” way .

[BBGONE]

Very user unfriendly to remove this feature if specially promised keep it in ef 3.0.
Now it was a surprise, after my application refactory to EF 3, that it is not working.

[BBGONE]

i don't think it's related to support of SQL Server 2008. Row number paging is supported in transact sql - now and in the future. New Sql Server releases support it too, but the Net.Core EF 3.0 dropped this support in favor of the newer construct. We don't ask you to support Sql Server 2008, but to allow to use the older paging method.

[smitpatel]

We don't ask you to support Sql Server 2008, but to allow to use the older paging method.

If newer construct is available in all SqlServer versions we support and it consistent across different types of database, then what value it provides to write additional code to use older paging method? Do you have any data points how it is significantly more beneficial to use older paging method on let's say SqlServer 2016?

[BBGONE]

We don't ask you to support Sql Server 2008, but to allow to use the older paging method.

If newer construct is available in all SqlServer versions we support and it consistent across different types of database, then what value it provides to write additional code to use older paging method? Do you have any data points how it is significantly more beneficial to use older paging method on let's say SqlServer 2016?

Yes, it's beneficial, but not on a standalone SqlServer 2016, but if you have, say 10 instances of sql server - 3 Sql Server 2008, 6 Sql Server 2012 and 1 Sql Server 2016, then you would use one common technology to access that data. In terms of query performances it would be the same.

[smitpatel]

@BBGONE - That does not answer my question. What is the benefit of generating row number paging on _supported SqlServer versions_?

[cseils]

This is a show stopper for me on a number of my projects. I have to stick with EF core 2.2 only because of this one issue.

[stooboo]

Hi

Like a lot of other people we're stuck with the SQL Server we're 'given' in production

We NEED to be able to support 2008 :-(

Please re-add support for this

cheers
Stu

[zyofeng]

+1 from me, we use a combination of Azure WebApp/Azure SQL as well as on-prem SQL, our on-prem sqls are on 2008 and hopefully this can be added back in EF Core 3.0

[Neme12]

Why isn't the method UseRowNumberForPaging() at least marked as obsolete with an error? It still exists and just doesn't do anything. I spent a bunch of time updating a project only to find out at runtime that I can't actually update after all. If i saw immediately that this method is no longer supported it would have saved me some time.

[ErikEJ]

It was a bug that it was not marked obsolete, and it will be fixed in 3.1

[NuNn-DaDdY]

It was a bug that it was not marked obsolete, and it will be fixed in 3.1

Not being marked obsolete will be fixed in 3.1 or the UseRowNumberForPaging() support will be added back in 3.1?

[bobbydowling]

pretty sure it is obsolete

https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#urn

See link above about "End of Mainstream support for SQL Server 2008 and SQL Server 2008 R2"

[NuNn-DaDdY]

Right, what happened in my case was that I overlooked the breaking change regarding UseRowNumberForPaging, but my code reported no Errors/Warnings due to the method not being flagged obsolete (ErikEJ indicated as bug).

https://github.com/aspnet/EntityFrameworkCore/issues/15403

Will have to revert back to Core 2.2 for now due to a legacy application restricting my ability to upgrade the database. I'll look forward to see if this gets reintroduced in the future.

[bobbydowling]

eww

[ajcvickers]

Note from triage: we discussed this again as part of 5.0 planning and based on current feedback and usage numbers we are not going to bring this back for 5.0.

[yyjdelete]

As one possible workaround, not full tested(verified no invalid sql with tests in efcore3.1, but didn't verify the result manually or run in really world now) and I will not do update for it, so use it at your own risk.
NOTE: use skip/take with negative value is not supported and will lead to wrong value.

Use it as optionsBuilder.ReplaceService<IQueryTranslationPostprocessorFactory, SqlServer2008QueryTranslationPostprocessorFactory>();, optionsBuilder is the same var used to call UseSqlServer().
Inspired by #19305 and https://github.com/dotnet/efcore/blob/4eeed38/src/EFCore.Relational/Query/SqlExpressions/SelectExpression.cs#L1313-L1337 (the only one usage for RowNumberExpression in efcore)

using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.SqlServer.Query.Internal;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;

namespace Test001
{

    //Use it as `optionsBuilder.ReplaceService<IQueryTranslationPostprocessorFactory, SqlServer2008QueryTranslationPostprocessorFactory>();`
    //`optionsBuilder` is the same var used to call `UseSqlServer()`
#pragma warning disable EF1001 // Internal EF Core API usage.
    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,
                queryCompilationContext);

        public class SqlServer2008QueryTranslationPostprocessor : SqlServerQueryTranslationPostprocessor
        {
            public SqlServer2008QueryTranslationPostprocessor(QueryTranslationPostprocessorDependencies dependencies, RelationalQueryTranslationPostprocessorDependencies relationalDependencies, QueryCompilationContext queryCompilationContext)
                : base(dependencies, relationalDependencies, queryCompilationContext)
            {
            }

            public override Expression Process(Expression query)
            {
                query = base.Process(query);
                query = new Offset2RowNumberConvertVisitor(query, SqlExpressionFactory).Visit(query);
                return query;
            }

            private class Offset2RowNumberConvertVisitor : ExpressionVisitor
            {
                private static readonly Func<SelectExpression, SqlExpression, string, ColumnExpression> GenerateOuterColumnAccessor;

                static Offset2RowNumberConvertVisitor()
                {
                    var method = typeof(SelectExpression).GetMethod("GenerateOuterColumn", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, null, new Type[] { typeof(SqlExpression), typeof(string) }, null);
                    if (method?.ReturnType != typeof(ColumnExpression))
                        throw new InvalidOperationException("SelectExpression.GenerateOuterColumn() is not found");
                    GenerateOuterColumnAccessor = (Func<SelectExpression, SqlExpression, string, ColumnExpression>)method.CreateDelegate(typeof(Func<SelectExpression, SqlExpression, string, ColumnExpression>));
                }

                private readonly Expression root;
                private readonly ISqlExpressionFactory sqlExpressionFactory;

                public Offset2RowNumberConvertVisitor(Expression root, ISqlExpressionFactory sqlExpressionFactory)
                {
                    this.root = root;
                    this.sqlExpressionFactory = sqlExpressionFactory;
                }

                protected override Expression VisitExtension(Expression node)
                {
                    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;
                    //order by in subQuery without TOP N is invalid.
                    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,
                                                               selectExpression.IsDistinct,
                                                               selectExpression.Alias);
                    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);
                    var left = GenerateOuterColumnAccessor(subQuery, projection, "row");
                    selectExpression.ApplyPredicate(sqlExpressionFactory.GreaterThan(left, oldOffset));
                    if (oldLimit != null)
                    {
                        if (oldOrderings.Count == 0)
                        {
                            selectExpression.ApplyPredicate(sqlExpressionFactory.LessThanOrEqual(left, sqlExpressionFactory.Add(oldOffset, oldLimit)));
                        }
                        else
                        {
                            //the above one not working when used as subQuery with orderBy
                            selectExpression.ApplyLimit(oldLimit);
                        }
                    }
                    return selectExpression;
                }
            }
        }
    }
#pragma warning restore EF1001 // Internal EF Core API usage.
}

[BBGONE]

As one possible workaround, not full tested(verified no invalid sql with tests in efcore3.1, but didn't verify the result manually or run in really world now) and I will not do update for it, so use it at your own risk. .....
```
Thanks. But technically there's no need in EF 3 to work with Sql 2008. You can still use EF 2 with Net.Core 3.1 and ASP. NET Core 3.1. You can encapsulate it in its own library and use EF2 library consumed by ASP.Net Core 3.1 application.
I tried to use EF 2 in ASP.Net Core 3.1. It works.
https://github.com/BBGONE/JRIApp.Core/tree/net-core-3.1-using-EF2.0

[BBGONE]

And by the way there's an option to use Entity Framework 6.4 in Net.Core applications too.
So one project can consume different ORM for different purposes.
My old project on ASP.NET MVC 5 uses different ORM (Linq for SQL, EF 6, and EF 4) in different services.
So this approach can be used in NET.Core as well

[SureshYadagiri]

Lot of projects still using SQL Server 2008. It's nice to have pagination support.

[BBGONE]

Lot of projects still using SQL Server 2008. It's nice to have pagination support.

By the way, if you did not noticed - the issue is closed. Try to find other solutions, and there are many

[gate21]

In the comment by yyjdelete on 1/11/2020
optionsBuilder.ReplaceService();
Where do I locate SqlServer2008QueryTranslationPostprocessorFactory?

[yyjdelete]

@gate21
https://github.com/dotnet/efcore/issues/16400#issuecomment-573325540
Click details in my above comment with modern browsers.

[Codename2200]

I still get this error for 3.1.2
'SqlServerDbContextOptionsBuilder.UseRowNumberForPaging(bool)' is obsolete: 'Row-number paging is no longer supported. See https://aka.ms/AA6h122 fore more information'.

When I run the project it gives me this error:

Core Microsoft SqlClient Data Provider | Incorrect syntax near 'OFFSET'.\r\nInvalid usage of the option NEXT in the FETCH statement.

Is there any workaround to this?

[Dunge]

Which LINQ query is impacted by this? Is it only Skip()?

I plan to move ahead with EF3.1 on a SQL2008 server (upgrading later), I only have two queries using skip and can be rewritten differently. Anything else I should be looking for?

Take() will still works right? (it converts to TOP in SQL)

[dlmotter]

Azure Synapse Analytics Server (Azure Data Warehouse) databases also do not support OFFSET, so it would be really nice to bring back RowNumberPaging for that. Unlike SQL2008, ASAS is not deprecated and needs support for this as well.