虽然是之前遇到的问题，但是还是想记录下来
环境
EF Core 3SQL Server 2008
问题
分页时使用.Skip().Take()会出现异常：
SqlException: ‘OFFSET’ 附近有语法错误。在 FETCH 语句中选项 NEXT 的用法无效
原因
分页方式
ROW_NUMBER() OVER() - SQL Server 2008 以上支持OFFSET FETCH NEXT - SQL Server 2012 以上支持
EF Core 3默认是使用 OFFSET FETCH NEXT 的方式进行分页。
因为使用EF Core 3又是SQL Server 2008 所以导致OFFSET FETCH NEXT关键字不生效产生异常。
解决方案
失败的方案
使用 UseRowNumberForPaging
网上说在配置DbContextOptions时使用UseRowNumberForPaging就可以解决
1
2
3
4
5
6
| protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UserSqlServer("ConnectionString", options => {
options.UseRowNumberForPaging();
});
}
|
但是问题并没有解决因为 UseRowNumberForPaging 已删除(过时)
失败的原因 UseRowNumberForPaging 已删除(过时)
旧行为
在 EF Core 3.0 之前，UseRowNumberForPaging 可用于生成与 SQL Server 2008 兼容的分页 SQL。
新行为
从 EF Core 3.0 开始，EF 将仅生成仅与更高的 SQL Server 版本兼容的分页 SQL。
为什么
因为 SQL Server 2008 不再是受支持的产品，并且更新此功能以使用在 EF Core 3.0 中做出的查询更改是一项重要工作。
成功的方案
生成一个新的查询Factory
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
| public class SqlServer2008QueryTranslationPostprocessorFactory : IQueryTranslationPostprocessorFactory
{
private readonly QueryTranslationPostprocessorDependencies _dependencies;
private readonly RelationalQueryTranslationPostprocessorDependencies _relationalDependencies;
public SqlServer2008QueryTranslationPostprocessorFactory(QueryTranslationPostprocessorDependencies dependencies, RelationalQueryTranslationPostprocessorDependencies relationalDependencies)
{
_dependencies = dependencies;
_relationalDependencies = relationalDependencies;
}
public virtual QueryTranslationPostprocessor Create(QueryCompilationContext queryCompilationContext)
=> new SqlServer2008QueryTranslationPostprocessor(
_dependencies,
_relationalDependencies,
queryCompilationContext);
public class SqlServer2008QueryTranslationPostprocessor : SqlServerQueryTranslationPostprocessor
{
public SqlServer2008QueryTranslationPostprocessor(QueryTranslationPostprocessorDependencies dependencies, RelationalQueryTranslationPostprocessorDependencies relationalDependencies, QueryCompilationContext queryCompilationContext)
: base(dependencies, relationalDependencies, queryCompilationContext)
{
}
public override Expression Process(Expression query)
{
query = base.Process(query);
query = new Offset2RowNumberConvertVisitor(query, SqlExpressionFactory).Visit(query);
return query;
}
private class Offset2RowNumberConvertVisitor : ExpressionVisitor
{
private static readonly Func<SelectExpression, SqlExpression, string, ColumnExpression> GenerateOuterColumnAccessor;
static Offset2RowNumberConvertVisitor()
{
var method = typeof(SelectExpression).GetMethod("GenerateOuterColumn", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance, null, new Type[] { typeof(SqlExpression), typeof(string) }, null);
if (method?.ReturnType != typeof(ColumnExpression))
throw new InvalidOperationException("SelectExpression.GenerateOuterColumn() is not found");
GenerateOuterColumnAccessor = (Func<SelectExpression, SqlExpression, string, ColumnExpression>)method.CreateDelegate(typeof(Func<SelectExpression, SqlExpression, string, ColumnExpression>));
}
private readonly Expression root;
private readonly ISqlExpressionFactory sqlExpressionFactory;
public Offset2RowNumberConvertVisitor(Expression root, ISqlExpressionFactory sqlExpressionFactory)
{
this.root = root;
this.sqlExpressionFactory = sqlExpressionFactory;
}
protected override Expression VisitExtension(Expression node)
{
if (node is SelectExpression se)
node = VisitSelect(se);
return base.VisitExtension(node);
}
private Expression VisitSelect(SelectExpression selectExpression)
{
var oldOffset = selectExpression.Offset;
if (oldOffset == null)
return selectExpression;
var oldLimit = selectExpression.Limit;
var oldOrderings = selectExpression.Orderings;
var newOrderings = oldOrderings.Count > 0 && (oldLimit != null || selectExpression == root)
? oldOrderings.ToList()
: new List<OrderingExpression>();
selectExpression = selectExpression.Update(selectExpression.Projection.ToList(),
selectExpression.Tables.ToList(),
selectExpression.Predicate,
selectExpression.GroupBy.ToList(),
selectExpression.Having,
orderings: newOrderings,
limit: null,
offset: null,
selectExpression.IsDistinct,
selectExpression.Alias);
var rowOrderings = oldOrderings.Count != 0 ? oldOrderings
: new[] { new OrderingExpression(new SqlFragmentExpression("(SELECT 1)"), true) };
_ = selectExpression.PushdownIntoSubquery();
var subQuery = (SelectExpression)selectExpression.Tables[0];
var projection = new RowNumberExpression(Array.Empty<SqlExpression>(), rowOrderings, oldOffset.TypeMapping);
var left = GenerateOuterColumnAccessor(subQuery, projection, "row");
selectExpression.ApplyPredicate(sqlExpressionFactory.GreaterThan(left, oldOffset));
if (oldLimit != null)
{
if (oldOrderings.Count == 0)
{
selectExpression.ApplyPredicate(sqlExpressionFactory.LessThanOrEqual(left, sqlExpressionFactory.Add(oldOffset, oldLimit)));
}
else
{
selectExpression.ApplyLimit(oldLimit);
}
}
return selectExpression;
}
}
}
}
|
在配置时使用ReplaceService替换Factory
1
| optionsBuilder.ReplaceService<IQueryTranslationPostprocessorFactory, SqlServer2008QueryTranslationPostprocessorFactory>();
|
不过这个只能作为可能的解决方案，未经过全面的测试，因此使用它的风险由自己承担。
以上代码来自Github
总结
很好奇后续EFCore是否会有优化😂。
如果有更好的解决方案记得联系我！！！👍
