实体框架核心:Guid 大于分页 [英] Entity Framework Core: Guid Greater Than for Paging

问题描述

SQL Server 在大型表(> 1000000 行)上使用 Skip/Take 变得非常慢.表键列类型是 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 支持所谓的 数据库标量函数映射.它没有很好的文档记录，通常用于映射一些数据库功能.但是 fluent 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);
        });
    }
}

您只需将以下行添加到您的上下文中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);

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