LINQ equivalent of keyset based pagination in postgres

Question

I have a postgresql query that does pagination using a keyset-based approach.

SELECT *
FROM public.values_with_variable_view
WHERE ( timestamp, id) > ('2024-01-04 01:12:09.267335+00',1996)
ORDER BY ( timestamp, id) asc
LIMIT 100

I have a requirement to write this using LINQ in c#. I tried using the where clause with .Where(x=>x.timestamp > '2024-01-04 01:12:09.267335+00' && x.id > 1996) but it gives a different result.

Here is DBFiddle

Answer 1

You should add || part:

var query = context.values_with_variable_view
    .Where(x => x.timestamp > '2023-11-30 09:32:19.776586' 
        || x.timestamp == '2023-11-30 09:32:19.776586' && x.id > 1)
    .OrderBy(x => x.timestamp)
    .ThenBy(x => x.Id)
    .Take(100);

Answer 2

I would recommend the .Where(x => x.timestamp >= last.timestamp && (x.timestamp > last.timestamp || x.id > last.id)) form for potentially better performance. Having the x.timestamp >= last.timestamp condition up front with no alternative or condition should promote better index usage. The second part of the condition refines the filter after the initial index seek.

Combined with the code from Svyatoslav Danyliv's post, the answer would be:

var query = context.values_with_variable_view
    .Where(x =>
        x.timestamp >= last.timestamp                       // Index seek part
        && (x.timestamp > last.timestamp || x.id > last.id) // Filter part
    )
    .OrderBy(x => x.timestamp)
    .ThenBy(x => x.Id)
    .Take(100);

See this updated fiddle.

Note that I have added an index for (timestamp, id) on the test table.

The real determination as to which is best should be to try both options against live data (presumably substantially larger than 25 rows) and review the execution plans for performance.