Question 314566 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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

Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search