caveats.md

Caveats

NULL

NULL is a very special value in databases. You can't compare against it as it invalidates the whole WHERE clause, resulting in no results. Different databases sort NULLs in different ways too, some treating it as the smallest value while others treating it as the largest value.

This is why having a nullable column as part of the keyset is not supported in this library, as we can't predictably deal with its special handling in databases.

Solutions

There are two solutions for this:

1. Computed column: Reliable, performant, a bit harder to implement
2. Keyset coalescing: Extremely easy to implement, but might suffer in terms of performance

Solution #1 - Computed column

The recommended way of working around this when you naturally have a nullable column that you want as part of your keyset is through using a non-nullable computed column (one for each nullable column in your keyset) and using that in your keyset instead.

You'll have to do this with any kind of nullable column type, but let's look at an example for a DateTime column.

Let's assume we want to create a keyset comprising of two columns: Created + Id. Let's assume that we need to make Created nullable for a business requirement.

First, we'll need to introduce a property in our model that we'll configure as computed along our nullable property:

public class User
{
    public int Id { get; set; }

    public DateTime? Created { get; set; } // Let's assume we need to have this as nullable

    public DateTime CreatedComputed { get; } // We'll be configuring this as a computed column later
}

Next, in the OnModelCreating method of our DbContext, we'll be configuring our computed column. The computation will be a coalescing operation that converts NULLs to a value we can deal with and predictably reason about.

Note that the exact format will be different from a Db to the other, so make sure to use the right format both for the COALESCE sql and for the Date format, or you might get wrong results.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .Property(x => x.CreatedComputed)
        // We're coalescing NULLs into a max date.
        // This results in NULLs effectively being sorted last (if ASC), irrelevant of the database.
        // You're writing sql here, make sure you have the right format for your particular database.
        // This is for sqlite.
        .HasComputedColumnSql("COALESCE(Created, '9999-12-31 00:00:00')");

    // Make sure to properly index columns as per your expected queries.
    modelBuilder.Entity<User>()
        .HasIndex(x => new { x.CreatedComputed, x.Id });
}

Here's another COALESCE example for Sql Server:

.HasComputedColumnSql("COALESCE(Created, CONVERT(datetime2, '9999-12-31', 102))");

In Sql Server, the computed value has to be deterministic to be able to create an index for it, that's why we need to use CONVERT.

As shown in the snippets above, don't forget to properly index as always.

Now that we have configured our computed column, we can go ahead and use that in the keyset the same way we use any other column:

_dbContext.Users.KeysetPaginateQuery(
    b => b.Ascending(x => x.CreatedComputed).Ascending(x => x.Id),
    KeysetPaginationDirection.Forward,
    reference)
    ...

Check this sample page for a working example you can run and play with.

Solution #2 - Keyset coalescing

This is an extremely easy solution to the NULL problem. Let's assume again we have the following keyset: Created + Id, where Created is nullable.

Here's how to solve this:

_dbContext.Users.KeysetPaginateQuery(
    b => b.Ascending(x => x.Created ?? DateTime.MinValue).Ascending(x => x.Id),
    KeysetPaginationDirection.Forward,
    reference)

As you can see, even though Created itself is nullable, the keyset above uses a null coalescing to specify the default value when it's null.

In terms of the end result, this is equivalent to creating a computed column.