Nullable fields

[raffaele-cappelli]

I am wondering if nullable fields are already handled. For instance: if the query is ordered by a nullable int field "Num", the value in the last row is NULL and the order is ascending, the expression "Num> NULL" is always FALSE in SQL... I think it should be generated something like "Num IS NOT NULL" (assuming NULL will come before any other value in ascending order). Similarly, in descending order, if the value in the last row is 10, the expression "Num < 10" will not get any row with NULL in "Num", the right expression should be something like "Num < 10 OR Num IS NULL".

[mrahhal]

In principal, my goal is to replicate what a row value expression would give you. I didn't think about the null case you mentioned though. Will need to see how row value deals with that and if it's different from the generated compound conditionals' behavior.

[mrahhal]

Refer to https://github.com/mrahhal/MR.EntityFrameworkCore.KeysetPagination/issues/24 (comment) for an elaborate investigation on this and a conclusion. The suggested solution here won't work. A simple example, if the reference has the value 24 for the nullable column, then a WHERE Column > 24 will never return the records with NULL for this column anyway, even if in theory they were sorted after. NULL is special in SQL. It seems that having nullable columns in the keyset is asking for trouble (elaboration in the linked comment).

[mrahhal]

Further discussion and workaround at https://github.com/mrahhal/MR.EntityFrameworkCore.KeysetPagination/issues/24.