[Question] How to disable pagination for child entities?

[AndriiLesiuk]

Hi

OData 8.2.0

My project uses server-side pagination through the EnableQuery attribute and the PageSize parameter:

        [HttpGet, EnableQuery(PageSize = 1000)]
        [ProducesResponseType(typeof(ParentTableName), 200)]
        public IActionResult Get()
        {
            var result = _service.Get();
            return Ok(result);
        }

Everything works fine, the query executes quickly and it generates @odata.nextLink as I expected. OData query: https://localhost:7233/odata/ParentTableName And generate this SQL:

SELECT o.key, o.column_name1, o.column_name2, o.column_name3
      FROM ParentTableName AS o
      ORDER BY o.key
      LIMIT 1000

BUT

When I try to add $expand, the OData library starts building a complex query with nested subqueries that just kills performance.

OData query: https://localhost:7233/odata/ParentTableName?$expand=ChildTableName And generate this SQL:

SELECT t.key, t.column_name1, t.column_name2, t.column_name3, t0.key, t0.column_name1, t0.column_name1
      FROM (
          SELECT t.key, t.column_name1, t.column_name2, t.column_name3
          FROM ParentTableName AS o
          ORDER BY o.key
          LIMIT 1000
      ) AS t
      LEFT JOIN (
          SELECT t1.key, t1.column_name1, t1.column_name2
          FROM (
              SELECT o0.key, o0.column_name1, o0.column_name2, ROW_NUMBER() OVER(PARTITION BY o0.key ORDER BY o0.key) AS row
              FROM ChildTableName AS o0
          ) AS t1
          WHERE t1.row <= 1000
      ) AS t0 ON t.key = t0.key
      ORDER BY t.key, t0.key

When I remove (PageSize = 1000) and try to make a query like this: OData query: https://localhost:7233/odata/ParentTableName?$expand=ChildTableName&$top=100 , OData generates a lighter query:

SELECT t.key, t.column_name1, t.column_name2, t.column_name3, o0.key, o0.column_name1, o0.column_name2
      FROM (
          SELECT o.key, o.column_name1, o.column_name2, o.column_name3
          FROM ParentTableName AS o
          ORDER BY o.key
          LIMIT 100
      ) AS t
      LEFT JOIN ChildTableName AS o0 ON t.key = o0.key
      ORDER BY t.key, o0.key

As far as I understand, in this case it also does pagination for ChildTableName. I don't see any other reason why it generates such a complex query. I've created indexes, tried splitting the table into even smaller ones, but it still doesn't help.

Is it possible somehow to save server-side pagination and at the same time disable pagination for child tables?

Thanks.

[ramiro-alegre]

Same Question