Query: Bring back support for UseRowNumberForPaging #16400

smitpatel · 2019-07-02T17:30:19Z

No description provided.

ErikEJ · 2019-07-02T17:43:10Z

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

ajcvickers · 2019-07-05T20:07:47Z

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.

See dotnet/efcore#16400

John0King · 2019-08-15T05:58:50Z

🤣 microsoft is merciless to microsoft's own product

fengshi19888 · 2019-09-16T08:51:15Z

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 · 2019-09-16T09:01:00Z

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

effun · 2019-09-28T13:51:48Z

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

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

ErikEJ · 2019-09-28T19:21:55Z

Express can be used commercially, but has some resource constraints

John0King · 2019-09-29T01:31:08Z

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 .

ghost · 2019-09-30T12:46:17Z

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.

ghost · 2019-10-08T08:53:31Z

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 · 2019-10-08T15:48:05Z

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?

ghost · 2019-10-08T16:25:14Z

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.

SureshYadagiri · 2020-01-29T03:10:56Z

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

ghost · 2020-01-29T05:23:53Z

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 · 2020-03-12T18:12:35Z

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

yyjdelete · 2020-03-13T01:00:19Z

@gate21
#16400 (comment)
Click details in my above comment with modern browsers.

Codename2200 · 2020-04-01T14:17:31Z

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 · 2020-05-13T22:46:25Z

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 · 2020-08-03T16:12:54Z

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.

David-Moreira · 2021-01-08T10:18:25Z

Hello guys. I also just came up with this problem in .NET 5, unfortunately still using SQL Server 2008.
I was trying to use @yyjdelete 's solution, but I can't seem to find the SqlServerQueryTranslationPostprocessor anywhere in Microsoft.EntityFrameworkCore.SqlServer.Query.Internal
Any ideas?

yyjdelete · 2021-01-08T10:48:17Z

@David-Moreira
Not tested, maybe you should use Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor instead

David-Moreira · 2021-01-10T16:31:25Z

@yyjdelete thank you for the quick answer, I'll take a look when possible and let you know if it worked.

lawhorkl · 2021-02-02T13:46:53Z

as with every time I run into an issue like this with .NET its sad to see the .NET dev team's unhalting attitude toward backward compatibility.

Its admirable to want to appear to always push for change and the latest tech however in reality I think it usually turns more into "Allowing backward compatibility is hard, so we arent going to do it." while putting out the folks that can't upgrade for very reasonable and not unusual reasons.

Sorry Microsoft, this is not how you do open source and this is why the .NET ecosystem shrinks every day. 0/10.

gate21 · 2021-02-02T14:57:37Z

I am not sure what the fuss is about, all of us have told our clients / consumers of our API's that we can no longer support a feature.

EF Core team gave us a workaround "SqlServer2008QueryTranslationPostprocessorFactory", I have been using it with EF 3.x in Production for some time now, and it works fine.

Thanks guys for giving us a workaround.

David-Moreira · 2021-02-26T09:31:49Z

@yyjdelete
Once again thank you for your time. Just now I was testing this.
The Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor works as substitute.
What should be in this line, on SqlExpressionFactory? I injected a ISqlExpressionFactory
query = new Offset2RowNumberConvertVisitor(query, SqlExpressionFactory).Visit(query);

I then tried running and I get this:

System.InvalidOperationException: Calling 'ShapedQueryExpression.VisitChildren' is not allowed. Visit the expression manually for the relevant part in the visitor.
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryExpression.VisitChildren(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.VisitExtension(Expression node)
   at Template.Api.Infrastructure.SqlServer2008QueryTranslationPostprocessorFactory.SqlServer2008QueryTranslationPostprocessor.Offset2RowNumberConvertVisitor.VisitExtension(Expression node)

shaunpenfold · 2021-03-10T02:41:49Z

@yyjdelete
Once again thank you for your time. Just now I was testing this.
The Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor works as substitute.
What should be in this line, on SqlExpressionFactory? I injected a ISqlExpressionFactory
query = new Offset2RowNumberConvertVisitor(query, SqlExpressionFactory).Visit(query);

I then tried running and I get this:
System.InvalidOperationException: Calling 'ShapedQueryExpression.VisitChildren' is not allowed. Visit the expression manually for the relevant part in the visitor.
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryExpression.VisitChildren(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.VisitExtension(Expression node)
   at Template.Api.Infrastructure.SqlServer2008QueryTranslationPostprocessorFactory.SqlServer2008QueryTranslationPostprocessor.Offset2RowNumberConvertVisitor.VisitExtension(Expression node)

Stuck at the same issue, tried many different Interfaces, cant seem to figure it out.

smitpatel · 2021-03-10T03:35:55Z

In the Offset2RowNumberConvertVisitor class modify VisitExtension method as following

		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);
                }

Then it will work as before.

Rwing · 2021-04-08T09:31:02Z

Hi guys,
I created a library to bring back the UseRowNubmerForPaging method based @yyjdelete 's code.
https://www.nuget.org/packages/EntityFrameworkCore.UseRowNumberForPaging/
thank you!

jitendrajadav · 2021-04-10T11:19:06Z

Hi guys,
I created a library to bring back the UseRowNubmerForPaging method based @yyjdelete 's code.
https://www.nuget.org/packages/EntityFrameworkCore.UseRowNumberForPaging/
thank you!

This is really help me a lot it save my day I was struggling and find this is really really awesome!

Thank you very much

David-Moreira · 2021-04-10T14:33:33Z

That's great!
When I had this problem, I did find that there's two already existing nuget packages which do something similar, like this one:
https://www.nuget.org/packages/EfCore.SqlServer2008Query/

However I don't know if yours or that one does something more. Thanks @Rwing

Rwing · 2021-04-12T01:00:16Z

@David-Moreira Sorry I didn't see this library before.
I think they have the same core code, and maybe my library's api is more friendly :)

David-Moreira · 2021-04-13T18:58:12Z

Yea, and a nice friendly MIT license on it!
Thanks! :)

ssg · 2021-10-06T03:53:49Z

The NuGet packages target only EF Core 5.0. In order to use it on EF Core 3.1, use the hack code by @yyjdelete in this thread.

sfsharapov · 2021-11-16T08:39:43Z

Are there any workaround for EF Core 6.0?

yltsa · 2021-12-09T14:17:37Z

Are there any workaround for EF Core 6.0?

I use EF Core 6.0 and the UseRowNumberForPaging nuget package. It comes the following error:

An unhandled exception occurred while processing the request.
InvalidOperationException: SelectExpression.GenerateOuterColumn() is not found
EntityFrameworkCore.UseRowNumberForPaging.SqlServer2008QueryTranslationPostprocessorFactory+SqlServer2008QueryTranslationPostprocessor+Offset2RowNumberConvertVisitor..cctor()

TypeInitializationException: The type initializer for 'Offset2RowNumberConvertVisitor' threw an exception.
EntityFrameworkCore.UseRowNumberForPaging.SqlServer2008QueryTranslationPostprocessorFactory+SqlServer2008QueryTranslationPostprocessor+Offset2RowNumberConvertVisitor..ctor(Expression root, ISqlExpressionFactory sqlExpressionFactory)

ssg · 2021-12-09T17:57:29Z

Someone needs to adapt the workaround code to EF Core 6.0. It's not portable between versions as it relies on internal implementation details. That's why you're getting the error.

I wish EF Core team had never dropped the legacy support. I think maintaining it in EF Core codebase would be way easier than maintaining third-party workarounds.

floydhamandi · 2021-12-30T20:21:08Z

Any news where we stand on this?

yltsa · 2022-01-04T20:25:07Z

Hi, I wrote before about the error. Now have noticed, that the sw works OK at home office, where is the newest SQL server. But at work the paging is not OK with an old Microsoft SQL server.

benyaminl · 2022-03-12T14:49:48Z

Hi, I wrote before about the error. Now have noticed, that the sw works OK at home office, where is the newest SQL server. But at work the paging is not OK with an old Microsoft SQL server.

The package of https://www.nuget.org/packages/EntityFrameworkCore.UseRowNumberForPaging/ works with SQL2008R2

Tested with https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks2008r2/adventure-works-2008r2-oltp.bak

ajcvickers added this to the Backlog milestone Jul 5, 2019

ajcvickers added the breaking-change label Jul 5, 2019

ajcvickers assigned ajcvickers and divega Jul 5, 2019

ajcvickers modified the milestones: Backlog, 3.0.0 Jul 5, 2019

ajcvickers changed the title ~~Query: RowNumberPaging support~~ Query: Bring back support for UseRowNumberForPaging Jul 10, 2019

ajcvickers added a commit to dotnet/EntityFramework.Docs that referenced this issue Jul 11, 2019

Add note about UseRowNumberForPaging

75d05b2

See dotnet/efcore#16400

ajcvickers mentioned this issue Jul 11, 2019

Add note about UseRowNumberForPaging dotnet/EntityFramework.Docs#1571

Merged

ajcvickers modified the milestones: 3.0.0, Backlog Jul 11, 2019

ajcvickers added type-enhancement and removed breaking-change labels Jul 11, 2019

ajcvickers added a commit to dotnet/EntityFramework.Docs that referenced this issue Jul 11, 2019

Add note about UseRowNumberForPaging (#1571)

7411586

See dotnet/efcore#16400

smitpatel mentioned this issue Aug 19, 2019

Throw better exception message for client eval #17247

Merged

smitpatel mentioned this issue Sep 18, 2019

Query: runtime error for queries with row number paging projecting a constant #12570

Closed

divega unassigned divega and ajcvickers Sep 18, 2019

ajcvickers reopened this Oct 16, 2022

ajcvickers closed this as not planned Won't fix, can't repro, duplicate, stale Oct 16, 2022

drmcclelland mentioned this issue May 1, 2024

StartsWith/EndsWith/Contains with parameter fails on Azure Synapse because ESCAPE isn't supported there #33555

Open

Query: Bring back support for UseRowNumberForPaging #16400

Query: Bring back support for UseRowNumberForPaging #16400

Comments

smitpatel commented Jul 2, 2019

ErikEJ commented Jul 2, 2019

ajcvickers commented Jul 5, 2019

John0King commented Aug 15, 2019

fengshi19888 commented Sep 16, 2019

ErikEJ commented Sep 16, 2019

effun commented Sep 28, 2019

ErikEJ commented Sep 28, 2019

John0King commented Sep 29, 2019

ghost commented Sep 30, 2019

ghost commented Oct 8, 2019

smitpatel commented Oct 8, 2019

ghost commented Oct 8, 2019 • edited by ghost Loading

SureshYadagiri commented Jan 29, 2020

ghost commented Jan 29, 2020

gate21 commented Mar 12, 2020

yyjdelete commented Mar 13, 2020

Codename2200 commented Apr 1, 2020 • edited Loading

Dunge commented May 13, 2020 • edited Loading

dlmotter commented Aug 3, 2020

David-Moreira commented Jan 8, 2021

yyjdelete commented Jan 8, 2021

David-Moreira commented Jan 10, 2021

lawhorkl commented Feb 2, 2021

gate21 commented Feb 2, 2021

David-Moreira commented Feb 26, 2021

shaunpenfold commented Mar 10, 2021

smitpatel commented Mar 10, 2021 • edited Loading

Rwing commented Apr 8, 2021

jitendrajadav commented Apr 10, 2021

David-Moreira commented Apr 10, 2021

Rwing commented Apr 12, 2021

David-Moreira commented Apr 13, 2021

ssg commented Oct 6, 2021

sfsharapov commented Nov 16, 2021 • edited Loading

yltsa commented Dec 9, 2021

ssg commented Dec 9, 2021

floydhamandi commented Dec 30, 2021

yltsa commented Jan 4, 2022

benyaminl commented Mar 12, 2022

ghost commented Oct 8, 2019 •

edited by ghost

Loading

Codename2200 commented Apr 1, 2020 •

edited

Loading

Dunge commented May 13, 2020 •

edited

Loading

smitpatel commented Mar 10, 2021 •

edited

Loading

sfsharapov commented Nov 16, 2021 •

edited

Loading