c# - Entity Framework Core: Guid Greater Than for Paging

Question

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();

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.

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?)

Answer 1

EF Core 2.x:

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.

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).

Here is the implementation:

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);
        });
    }
}

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 now receives and returns SqlExpression instances, so

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

should be replaced with

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