Search code examples
postgresqllinqwhere-clausetimescaledbkeyset-pagination

LINQ equivalent of keyset based pagination in postgres

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

Solution

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