EF 6.0.0+C# ASP.NET MVC4中Linq生成OFFSET FETCH SQL的方法咨询

Great question! The issue you're seeing is because Entity Framework 6 defaults to using legacy pagination logic (TOP + WHERE clause with a key comparison) when it doesn't recognize that your SQL Server environment supports the newer OFFSET/FETCH syntax (introduced in SQL Server 2012). Here's how you can adjust your setup and code to get the desired SQL output:

Step 1: Verify SQL Server Compatibility Level #

First, make sure your database is set to a compatibility level that supports OFFSET/FETCH (minimum 110 for SQL Server 2012):

1. Run this query in SQL Server Management Studio to check your database's compatibility level:

   SELECT name, compatibility_level 
   FROM sys.databases 
   WHERE name = 'YourDatabaseName';
   

2. If the level is below 110, update it with this command:

   ALTER DATABASE YourDatabaseName 
   SET COMPATIBILITY_LEVEL = 110; -- Or higher (120 for 2014, 130 for 2016, etc.)
   

Step 2: Configure EF 6 to Use SQL Server 2012+ Provider #

EF 6 needs to know it's talking to a modern SQL Server instance. You can configure this via a custom DbConfiguration or directly in your DbContext:

Option A: Custom DbConfiguration Class #

Create a configuration class to register the correct SQL Server provider:

public class MyDbConfig : DbConfiguration
{
    public MyDbConfig()
    {
        // Register SQL Server provider services and factory
        SetProviderServices("System.Data.SqlClient", SqlProviderServices.Instance);
        SetProviderFactory("System.Data.SqlClient", SqlClientFactory.Instance);
    }
}

Then apply this configuration to your DbContext using an attribute:

[DbConfigurationType(typeof(MyDbConfig))]
public class YourDbContext : DbContext
{
    public YourDbContext() : base("YourConnectionString")
    {
    }

    // DbSet properties here...
}

Option B: Configure Directly in DbContext Constructor #

If you prefer not to use a separate config class, add this to your DbContext constructor:

public class YourDbContext : DbContext
{
    public YourDbContext() : base("YourConnectionString")
    {
        // Ensure EF uses the SQL Server 2012+ provider
        DbConfiguration.Loaded += (sender, args) =>
        {
            args.ReplaceService<DbProviderServices>((s, k) => SqlProviderServices.Instance);
            args.ReplaceService<DbProviderFactory>((s, k) => SqlClientFactory.Instance);
        };
    }
}

Step 3: Write Correct Linq Query #

Finally, make sure your Linq query follows this critical order: OrderBy → Skip → Take. The OrderBy is mandatory—EF can't generate OFFSET/FETCH without an explicit sort order (since pagination requires a consistent result set):

var paginatedResults = dbContext.YourEntities
    .OrderBy(entity => entity.Id) // Replace with your actual sort field
    .Skip(30)
    .Take(10)
    .ToList();

Why This Works #

Once EF confirms your SQL Server supports OFFSET/FETCH, and your query includes a proper OrderBy, it will generate the clean pagination syntax you want:

SELECT [Extent1].[Id] AS [Id], -- other columns...
FROM [dbo].[YourEntities] AS [Extent1]
ORDER BY [Extent1].[Id] ASC
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

Additional Notes #

• Ensure your project references the correct version of EntityFramework.SqlServer.dll (matching your EF 6.0.0 installation) to avoid version conflicts.
• If using Code First, you can set the compatibility level automatically during database initialization by adding this to your custom initializer:

  protected override void Seed(YourDbContext context)
  {
      context.Database.ExecuteSqlCommand(
          "ALTER DATABASE {0} SET COMPATIBILITY_LEVEL = 110", 
          context.Database.Connection.Database
      );
      // Seed data here...
  }
  

内容的提问来源于stack exchange，提问作者Nands