blocks|key|2703433|text|我自己在使用EF7和sql+server2008时遇到了这个问题。幸运的是，在EF+7的最新rc1版本中，您可以通过使用.UseRowNumberForPaging()来解决此问题，如下例所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2703434|services.AddEntityFramework()
++.AddSqlServer()
++.AddDbContext<YourDbContext>(options+=>
+++++options.UseSqlServer(configuration["Data:DefaultConnection:ConnectionString"])
++++++++++++++++++++//+this+is+needed+unless+you+are+on+mssql+2012+or+higher
++++++++++++++++++++.UseRowNumberForPaging()
++++++++++++++++);|code-block|syntax|javascript|2703435|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I encountered this problem myself using EF 7 and sql server 2008. Fortunately in the latest rc1 version of EF 7 you can solve this by using .UseRowNumberForPaging() as shown in this example:

<pre><code>services.AddEntityFramework()
 .AddSqlServer()
 .AddDbContext&lt;YourDbContext&gt;(options =&gt;
 options.UseSqlServer(configuration["Data:DefaultConnection:ConnectionString"])
 // this is needed unless you are on mssql 2012 or higher
 .UseRowNumberForPaging()
 );
</code></pre>

blocks|key|137433|text|如果使用Edmx文件，则必须使用XML编辑器打开edmx文件并更改|type|unstyled|depth|inlineStyleRanges|entityRanges|data|137434|ProviderManifestToken="2012"+==>+ProviderManifestToken="2008"|code-block|syntax|javascript|137435|在第7行。|137436|有关更多信息，请查看这篇博客文章：http://erikej.blogspot.com.tr/2014/12/a-breaking-change-in-entity-framework.html|offset|length|137437|entityMap|0|LINK|mutability|MUTABLE|url|http://erikej.blogspot.com.tr/2014/12/a-breaking-change-in-entity-framework.html^0|0|0|0|H|28|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

If you use Edmx file, you must open the edmx file using XML Editor and change 

<pre><code>ProviderManifestToken="2012" ==&gt; ProviderManifestToken="2008"
</code></pre>

in line 7.

Please take a look at this blog post for more information:
<a href="http://erikej.blogspot.com.tr/2014/12/a-breaking-change-in-entity-framework.html" rel="noreferrer">http://erikej.blogspot.com.tr/2014/12/a-breaking-change-in-entity-framework.html</a>

blocks|key|2703437|text|它在RC1中坏了。必须等待RC2。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2703438|https://github.com/aspnet/EntityFramework/issues/4616|offset|length|2703439|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|1H|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@$D|O|E|P|1|Q]]|A|$]]|$1|F|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|C]]]]

It's broken in RC 1. Gotta wait to get RC 2.

<a href="https://github.com/aspnet/EntityFramework/issues/4616" rel="nofollow">https://github.com/aspnet/EntityFramework/issues/4616</a>

blocks|key|2915969|text|MyDbConnectionString是来自任何源的连接字符串|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2915970|protected+override+void+OnConfiguring(DbContextOptionsBuilder+optionsBuilder)
{
++++optionsBuilder.UseSqlServer(_config["MyDbConnectionString"],+
++++++++++++++++options=>
++++++++++++++++{
++++++++++++++++++++options.UseRowNumberForPaging();
++++++++++++++++});
}|code-block|syntax|javascript|2915971|UseRowNumberForPaging()解决了除edmx文件方案以外的任何情况下的问题。|CODE|2915972|entityMap^0|0|K|0|0|0|N|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|S|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|T|8|@$9|U|A|V|B|M]]|D|@]|E|$]]|$1|N|3|-4|5|6|7|W|8|@]|D|@]|E|$]]]|O|$]]

MyDbConnectionString is Connection string from any source

<pre><code>protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
 optionsBuilder.UseSqlServer(_config["MyDbConnectionString"], 
 options=&gt;
 {
 options.UseRowNumberForPaging();
 });
}
</code></pre>

<code>UseRowNumberForPaging()</code> solved issue in any case except for edmx file scenario.

blocks|key|134525|text|EF核心3.x，UseRowNumberForPaging中的此功能was+removed被标记为已过时。但是，您可以改用EfCore3.SqlServer2008Query包。Nuget中提供了两个软件包，一个用于>=+.NET+5.0，另一个用于>=+.NET+3.1|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|134526|​|134527|📷|atomic|134528|134529|用法：|134530|+services.AddDbContext<MyDbContext>(o+=>+
+++++++o.UseSqlServer(Configuration.GetConnectionString("Default"))
++++++++.ReplaceService<IQueryTranslationPostprocessorFactory,+SqlServer2008QueryTranslationPostprocessorFactory>());|code-block|syntax|javascript|134531|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#userownumberforpaging-has-been-removed|1|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/22d5fc8971089896d653a4f87b3febdb.png|imageAlt^0|8|L|1P|Q|Y|B|0|0|0|0|1|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@$9|17|A|18|B|C]|$9|19|A|1A|B|C]]|D|@$9|1B|A|1C|1|1D]]|E|$]]|$1|F|3|G|5|6|7|1E|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1F|8|@]|D|@$9|1G|A|1H|1|1I]]|E|$]]|$1|K|3|G|5|6|7|1J|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|1K|8|@]|D|@]|E|$]]|$1|N|3|O|5|P|7|1L|8|@]|D|@]|E|$Q|R]]|$1|S|3|-4|5|6|7|1M|8|@]|D|@]|E|$]]]|T|$U|$5|V|W|X|E|$Y|Z]]|10|$5|11|W|12|E|$13|14|15|-4]]]]

This feature <a href="https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.x/breaking-changes#userownumberforpaging-has-been-removed" rel="nofollow noreferrer">was removed</a> in EF Core 3.x, <code>UseRowNumberForPaging</code> is marked as obsolete. However, you can use <code>EfCore3.SqlServer2008Query</code> package instead. There are 2 packages available in Nuget, one for &gt;= .NET 5.0, other one is for &gt;= .NET 3.1
<a href="https://i.stack.imgur.com/YDlEL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YDlEL.png" alt="enter image description here" /></a>
Usage:
<pre><code> services.AddDbContext&lt;MyDbContext&gt;(o =&gt; 
 o.UseSqlServer(Configuration.GetConnectionString(&quot;Default&quot;))
 .ReplaceService&lt;IQueryTranslationPostprocessorFactory, SqlServer2008QueryTranslationPostprocessorFactory&gt;());
</code></pre>

blocks|key|136829|text|你需要使用类似这样的东西：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|136830|var+MinPageRank+=+(pageIndex+-+1)+*+pageSize+%2B+1;
var+MaxPageRank+=+(pageIndex+*+pageSize);

var+person+=+_context.Person.FromSql($"SELECT+*+FROM+(SELECT+[RANK]+=+ROW_NUMBER()+OVER+(ORDER+BY+Surname),*+FROM+Person)+A+WHERE+A.[RANK]+BETWEEN+{MinPageRank}+AND+{MaxPageRank}").ToList();

IQueryable<Person>+PersonIQ+=+from+s+in+person.AsQueryable()+select+s;
Person+=+await+PaginatedList<Person>.CreateAsync(PersonIQ+.AsNoTracking(),+pageIndex+??+1,+pageSize,+sourceFull);|code-block|syntax|javascript|136831|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You need to use something like this : 

<pre><code>var MinPageRank = (pageIndex - 1) * pageSize + 1;
var MaxPageRank = (pageIndex * pageSize);

var person = _context.Person.FromSql($"SELECT * FROM (SELECT [RANK] = ROW_NUMBER() OVER (ORDER BY Surname),* FROM Person) A WHERE A.[RANK] BETWEEN {MinPageRank} AND {MaxPageRank}").ToList();

IQueryable&lt;Person&gt; PersonIQ = from s in person.AsQueryable() select s;
Person = await PaginatedList&lt;Person&gt;.CreateAsync(PersonIQ .AsNoTracking(), pageIndex ?? 1, pageSize, sourceFull);
</code></pre>

blocks|key|135473|text|在这里，只需在ConfigureServices中设置UseRowNumberForPaging()|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|135474|services.AddDbContext<CallcContext>(options+=>+
options.UseSqlServer(Configuration.GetConnectionString("Connectionstring"),opt=>+{+opt.UseRowNumberForPaging();+}));|code-block|syntax|javascript|135475|entityMap^0|7|H|R|N|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Here, just set <code>UseRowNumberForPaging()</code> in <code>ConfigureServices</code>

<pre><code>services.AddDbContext&lt;CallcContext&gt;(options =&gt; 
options.UseSqlServer(Configuration.GetConnectionString("Connectionstring"),opt=&gt; { opt.UseRowNumberForPaging(); }));
</code></pre>

I'm trying to use paging (that is <code>.Skip(...).Take(...)</code> in Entity Framework 7. It works OK with Microsoft SQL Server 2012 and 2014, but fails with the following error on SQL Server 2008:

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

I've figured out that it is a breaking change in EF version 6.1.2 (<a href="http://erikej.blogspot.com/2014/12/a-breaking-change-in-entity-framework.html">http://erikej.blogspot.com/2014/12/a-breaking-change-in-entity-framework.html</a>). But the fix is to modify EDMX file setting ProviderManifestToken attribute to "2008".

The problem is that EF7 currently only supports code-first scenario, thus there is no any EDMX out there. The question is: how to configure ASP.NET 5 website with Entity Framework 7 to use fallback pagination approach for SQL Server older than 2012?

Paging with Entity Framework 7 and SQL Server 2008

SQL Server

我正在尝试使用分页(即Entity Framework7中的.Skip(...).Take(...) )。它在Microsoft SQL Server2012和2014上运行正常，但在SQL Server2008上失败，出现以下错误： System.Data.SqlClient.SqlException (0x80131904)：'OFFSET‘附近的语法不正确。FETCH语句中的NEXT选项用法

问使用Entity Framework 7和SQL Server 2008进行分页
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Entity Framework 7和SQL Server 2008进行分页EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用Entity Framework 7和SQL Server 2008进行分页
EN