Paging with OFFSET Command in MS SQL not working properly

Hello,

When examining the SearchProductsAsync method in the ProductService I noticed that the paged results are queried directly in MS SQL Command via the line

OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY

from here: https://github.com/nopSolutions/nopCommerce/blob/2c130c60018be960808382496e1aeeeb0a9acc6e/src/Libraries/Nop.Services/Catalog/ProductService.cs#L1111

Unfortunatley this OFFSET command causes a timeout in MS SQL Server and an Exception is risen. When I remove it the query is executed.

I don't now why it is so, but first of all I'd like to ask if I have overseen some MS SQL specific configuration?

Found this one here: https://www.nopcommerce.com/en/boards/topic/73980/sqlexception-incorrect-syntax-near-offset

But unfortunately there is no "UseRowNumberForPaging" option in appsettings.json
I am using nop 4.60

What version of MS SQL Server are you using?

15.0.2101.7, Windows 64Bit locally
12.0.2008.8 on Azure remotely

Must also note, that I have quite a large number of products in the product table (many ten thousands).

Hi,

Is there any news on this?

Thanks, Stephan

Did you check the compatibility on your database?
https://learn.microsoft.com/en-us/sql/relational-databases/databases/view-or-change-the-compatibility-level-of-a-database?redirectedfrom=MSDN&view=sql-server-ver16

No, can you tell me in Short where I can find Info about compatibiliy? Thanks a lot

As per the link above, exec this (with your DB name)
SELECT compatibility_level  
FROM sys.databases WHERE name = 'yourNopCommerceDB';

From what I've read elsewhere, "OFFSET was added in SQL Server 2012, so if your database is in 2008 compatibility mode, this keyword is not available"

If you need to alter your DB compatibility_level , see this
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver16

OFFSET was introduced in SQL Server 2012 so 2008 produce error.
There is extremally easy solution.
Find

Libraries\Nop.Data\DataProviders\MsSqlDataProvider.cs

and  replace

protected override IDataProvider LinqToDbDataProvider => SqlServerTools.GetDataProvider(SqlServerVersion.v2012, SqlServerProvider.MicrosoftDataSqlClient);

with

protected override IDataProvider LinqToDbDataProvider => SqlServerTools.GetDataProvider(SqlServerVersion.v2008, SqlServerProvider.MicrosoftDataSqlClient);

I hope it helps.

Alex