我要评论前言
net程序员是很幸福的,ms在上个月发布了net9.0rtm,带来了不少的新特性,但是呢,还有很多同学软硬件都还没跟上时代的步伐,比如,自己的电脑还在跑win7,公司服务器还在跑mssql2005-2008的!
这不就引入了我们本文要探索的问题,因为ms早在efcore3.1后就不再内置支持row_number()了,以至于需要兼容分页的代码都需要自行处理,当然同学们如果对efcore没有依赖度也可以使用其他的orm选型,当然如果不想折腾efcore也能使用万能的rawsql拼接执行也是可以的 😃
最近自己发的nuget包有个国外的程序员朋友提了一个issue,以至于我马上行动起来
在efcore9中, 以前兼容的好好的row_number()代码,升级尝鲜后发现跑不起来了,这主要是因为新版本的efcore9做了很多破坏性更新,以至于我们不得不研究新的底层代码!
兼容实现
之前发布过一个nuget包,代码主要是基于以前道友兼容efcore7适配到efcore8的兼容,代码也不多变化也不大,不过呢,升级到efcore9后发现底层的api全变了,不得不重新再实现一遍!
以下是兼容efcore9的代码部分:
#if net9_0_or_greater
#pragma warning disable ef1001 // internal ef core api usage.
namespace biwen.efcore.userownumberforpaging;
using microsoft.entityframeworkcore.query;
using system.collections.generic;
public class sqlserver2008querytranslationpostprocessorfactory(
querytranslationpostprocessordependencies dependencies,
relationalquerytranslationpostprocessordependencies relationaldependencies) : iquerytranslationpostprocessorfactory
{
private readonly querytranslationpostprocessordependencies _dependencies = dependencies;
private readonly relationalquerytranslationpostprocessordependencies _relationaldependencies = relationaldependencies;
public virtual querytranslationpostprocessor create(querycompilationcontext querycompilationcontext)
=> new sqlserver2008querytranslationpostprocessor(
_dependencies,
_relationaldependencies,
querycompilationcontext);
public class sqlserver2008querytranslationpostprocessor(querytranslationpostprocessordependencies dependencies, relationalquerytranslationpostprocessordependencies relationaldependencies, querycompilationcontext querycompilationcontext) :
relationalquerytranslationpostprocessor(dependencies, relationaldependencies, (relationalquerycompilationcontext)querycompilationcontext)
{
public override expression process(expression query)
{
query = base.process(query);
query = new offset2rownumberconvertvisitor(query, relationaldependencies.sqlexpressionfactory).visit(query);
return query;
}
internal class offset2rownumberconvertvisitor(
expression root,
isqlexpressionfactory sqlexpressionfactory) : expressionvisitor
{
private readonly expression root = root;
private readonly isqlexpressionfactory sqlexpressionfactory = sqlexpressionfactory;
private const string subtablename = "subtbl";
private const string rowcolumnname = "_row_";//下标避免数据表存在字段
protected override expression visitextension(expression node) => node switch
{
shapedqueryexpression shapedqueryexpression => shapedqueryexpression.update(visit(shapedqueryexpression.queryexpression), shapedqueryexpression.shaperexpression),
selectexpression se => visitselect(se),
_ => base.visitextension(node)
};
private selectexpression visitselect(selectexpression selectexpression)
{
var oldoffset = selectexpression.offset;
if (oldoffset == null)
return selectexpression;
var oldlimit = selectexpression.limit;
var oldorderings = selectexpression.orderings;
var neworderings = oldorderings switch
{
{ count: > 0 } when oldlimit != null || selectexpression == root => oldorderings.tolist(),
_ => []
};
var roworderings = oldorderings.any() switch
{
true => oldorderings,
false => [new orderingexpression(new sqlfragmentexpression("(select 1)"), true)]
};
var oldselect = selectexpression;
var rownumberexpression = new rownumberexpression([], roworderings, oldoffset.typemapping);
// 创建子查询
ilist<projectionexpression> projections = [new projectionexpression(rownumberexpression, rowcolumnname),];
var subquery = new selectexpression(
subtablename,
oldselect.tables,
oldselect.predicate,
oldselect.groupby,
oldselect.having,
[.. oldselect.projection, .. projections],
oldselect.isdistinct,
[],//排序已经在rownumber中了
null,
null,
null,
null);
//构造新的条件:
var and1 = sqlexpressionfactory.greaterthan(
new columnexpression(rowcolumnname, subtablename, typeof(int), null, true),
oldoffset);
var and2 = sqlexpressionfactory.lessthanorequal(
new columnexpression(rowcolumnname, subtablename, typeof(int), null, true),
sqlexpressionfactory.add(oldoffset, oldlimit));
var newpredicate = sqlexpressionfactory.andalso(and1, and2);
//新的projection:
var newprojections = oldselect.projection.select(e =>
{
if (e is { expression: columnexpression col })
{
var newcol = new columnexpression(col.name, subtablename, col.type, col.typemapping, col.isnullable);
return new projectionexpression(newcol, e.alias);
}
return e;
});
// 创建新的 selectexpression，将子查询作为来源
var newselect = new selectexpression(
oldselect.alias,
[subquery],
newpredicate,
oldselect.groupby,
oldselect.having,
[.. newprojections],
oldselect.isdistinct,
[],
null,
null,
null,
null);
// projectionmapping replace
var pm = new projectionmember();
var projectionmapping = new dictionary<projectionmember, expression>
{
{
pm,
oldselect.getprojection(new projectionbindingexpression(null,pm,null))
}
};
newselect.replaceprojection(projectionmapping);
return newselect;
}
}
}
}
#pragma warning restore ef1001 // internal ef core api usage.
#endif最后
实现上逻辑还是一致的,反正都是将offset转换为row_number()子查询中,取行号数据
只是代码实现区别有一些,以前的efcore底层代码很多已经不在可用比如直接使用pushdownintosubquery()会报错,generateoutercolumn()内部的扩展方法发生了破坏性更新导致不能再使用等!
如果你的程序需要升级到net9并还在使用早期数据库的话,可以引用我实现的代码部分,或者直接引用我发布的nuget包
<packagereference include="biwen.efcore.userownumberforpaging" version="2.1.1" />
代码我放在了github,任何问题欢迎issue https://github.com/vipwan/biwen.efcore.userownumberforpaging
到此这篇关于.net9 efcore支持早期mssql数据库 row_number()分页的文章就介绍到这了,更多相关.net9 efcore mssql数据库 row_number()分页内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论