how to target sql server 2008 with EF Core

Question

Building a razor pages application usign ef core 2.1.1. Development DB server localDB, target SQL server SQL 2008 r2.

I get the following error on the target server:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'OFFSET'. Invalid usage of the option NEXT in the FETCH statement.

I suppose the generated SQL code is targeted to SQL server 2012 and up.

How do I force ef core to generate code for SQL server 2008?

Thanks!

Answer 1

EF Core, or more specifically the underlying driver used when interacting with SQL Server Microsoft.EntityFrameworkCore.SqlServer only supports SQL Server 2012 and onwards. Reference

One alternative to make your Skip and Take queries work is to use the below when configuring your DbContext

services.AddDbContext<MyDbContext>(options => 
    options.UseSqlServer(connection, opt => opt.UseRowNumberForPaging()));

Update:

• Row-number paging is no longer supported. See: https://aka.ms/AA6h122
• There is a third-party package to add it back: https://github.com/Rwing/EntityFrameworkCore.UseRowNumberForPaging