实体框架核心：指导性强于分页 [英] Entity Framework Core: Guid Greater Than for Paging

问题描述

在大型表（> 1000000行）上执行跳过/执行时，SQL Server变得非常慢。表的关键列类型为Guid，我知道最后读取的行。我尝试加载下一页，例如

SQL Server becomes very slow with Skip/Take on large tables (> 1000000 rows). The tables key column type is Guid and I know the last read row. I try to load next page like

var keyGuid = Guid.NewGuid(); // Key Guid of the last read row
// var result1 = DbContext.Entity.Where(x => x.Id > keyGuid).Take(10).ToList();
var result2 = DbContext.Entity.Where(x => x.Id.CompareTo(keyGuid) > 0).Take(10).ToList();

虽然第一种方法未编译，但是第二种方法在客户端上查询查询（QueryClientEvaluationWarning），也不有用。

While the first approach doesn't compile, the second one evaluates the query on client (QueryClientEvaluationWarning) and isn't useful too.

不幸的是，我无法以任何方式修改数据库。

Unfortunately, I cannot modify the database in any way.

有没有没有自定义SQL的本机 EF Core解决方案？如果可以拦截SQL代码生成并手动解析表达式（但是如何呢？）

Is there any 'native' EF Core solution without custom SQL? It might be ok if it's possible to intercept SQL code generation and resolve the expression manually (but how?)

推荐答案

EF Core 2.x ：

从v2.0开始，EF Core支持所谓的数据库标量函数映射。它的文档记录不是很好，通常用于映射某些数据库功能。但是流利的API还允许您通过 HasTranslation 方法：

Starting with v2.0, EF Core supports the so called Database scalar function mapping. It's not very well documented and usually is used to map some database function. But fluent API also allows you to provide a custom translation via HasTranslation method:

设置一个回调，该回调将被调用以执行自定义翻译此功能。回调采用与传递给函数调用的参数相对应的表达式集合。回调应返回一个表示所需翻译的表达式。

Sets a callback that will be invoked to perform custom translation of this function. The callback takes a collection of expressions corresponding to the parameters passed to the function call. The callback should return an expression representing the desired translation.

以下类通过定义几种自定义扩展方法来比较 Guid 值并为其注册一个自定义转换，该转换将方法调用表达式转换为二进制比较表达式，基本上模拟了丢失的> ，> = ，< 和< = Guid运算符，可以将它们正确转换为SQL一旦数据库支持它们（SqlServer就会执行）。

The following class utilizes that by defining several custom extension methods for comparing Guid values and registers a custom translation for them, which converts the method call expressions to binary comparison expressions, basically simulating the missing >, >=, < and <= Guid operators, which allows translating them to SQL and properly execute server side, as soon as the database supports them (SqlServer does).

这里是实现：

public static class GuidFunctions
{
    public static bool IsGreaterThan(this Guid left, Guid right) => left.CompareTo(right) > 0;
    public static bool IsGreaterThanOrEqual(this Guid left, Guid right) => left.CompareTo(right) >= 0;
    public static bool IsLessThan(this Guid left, Guid right) => left.CompareTo(right) < 0;
    public static bool IsLessThanOrEqual(this Guid left, Guid right) => left.CompareTo(right) <= 0;
    public static void Register(ModelBuilder modelBuilder)
    {
        RegisterFunction(modelBuilder, nameof(IsGreaterThan), ExpressionType.GreaterThan);
        RegisterFunction(modelBuilder, nameof(IsGreaterThanOrEqual), ExpressionType.GreaterThanOrEqual);
        RegisterFunction(modelBuilder, nameof(IsLessThan), ExpressionType.LessThan);
        RegisterFunction(modelBuilder, nameof(IsLessThanOrEqual), ExpressionType.LessThanOrEqual);
    }
    static void RegisterFunction(ModelBuilder modelBuilder, string name, ExpressionType type)
    {
        var method = typeof(GuidFunctions).GetMethod(name, new[] { typeof(Guid), typeof(Guid) });
        modelBuilder.HasDbFunction(method).HasTranslation(parameters =>
        {
            var left = parameters.ElementAt(0);
            var right = parameters.ElementAt(1);
            return Expression.MakeBinary(type, left, right, false, method);
        });
    }
}

所有您需要的是在您的行中添加以下行context OnModelCreating 覆盖：

All you need is to add the following line to your context OnModelCreating override:

GuidFunctions.Register(modelBuilder);

然后只需在查询中使用它们即可：

and then simply use them in your queries:

var result = DbContext.Entity
    .Where(x => x.Id.IsGreaterThan(keyGuid))
    .Take(10).ToList();

EF Core 3.0 ：

HasTranslation 现在接收并返回 SqlExpression 实例，因此

HasTranslation now receives and returns SqlExpression instances, so

return Expression.MakeBinary(type, left, right, false, method);

应替换为

return new SqlBinaryExpression(type, left, right, typeof(bool), null);

这篇关于实体框架核心：指导性强于分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！