Entity Framework provider seems to generate incorrect SQL when implementing paging

SQL, Caché

I've mapped multiple tables (UNION on mapped SQL classes) into a view, using CREATE VIEW.

Through ODBC, in Entity Framework, I am querying against that view and offering paging. The paging is implemented using IQueryable.Skip and IQueryable.Take. 

Skip seems to have unexpected results, I believe due to incorrect SQL generated by the Entity Framework provider, though perhaps I've done something incorrectly. The generated SQL looks similar to this (with some bits replaced or altered for security reasons), the basic structure is unaltered.

SELECT TOP (2) * -- "2" is the number of records per page
FROM ( SELECT TOP ALL  *
    FROM ( SELECT *
        FROM ( SELECT *    FROM "SQLUser"."XSOMEVIEW" AS "Extent1"
            WHERE (("Extent1"."SomeColumn" = 1) AND (("Extent1"."SomeOtherColumn" = '12345') OR (("Extent1"."SomeOtherColumn" IS NULL) AND ('12345' IS NULL)))) AND (("Extent1"."SomeThirdColumn" = '1234567') OR (("Extent1"."SomeThirdColumn" IS NULL) AND ('1234567' IS NULL)))
        )  AS "Project1"
    )  AS "Project1"
    WHERE %VID > 2 -- "2" is the amount to skip, implying page number
    ORDER BY "Project1"."SortableColumn" DESC
)  AS "top"

/* provider: InterSystems.Data.Entity6, Version=4.5.0.0, Culture=neutral, PublicKeyToken=ad350a26c4a4447c*/

 

When adding in %VID to the query at the same level as the TOP ALL, I can see the values represent the overall position in the view, not the position in the filtered, sorted list.

The C# code looks similar to:

            var query = filters.Aggregate(_repository.TargetTable, (a, x) => a.Where(x))
                .OrderByDescending(x => x.SortableColumn).Skip(skip).Take(take);
            return await query.ToListAsync();

Where "filters" is a list of filter expressions (such as x=>x.SomeColumn = 1).

Can anybody confirm that this is a bug in the Entity Framework provider, or identify where I might have made a mistake?

  • 0
  • 0
  • 354
  • 9
  • 3

Answers

Ok I think I can confirm this is a bug in Intersystems's query provider for Entity Framework.

The order clause is being generated at the same level as the where containing the %VID. Instead, the ORDER BY should be moved inward to a query lower, along with the TOP ALL which it requires. I have an example of the broken and fixed queries below.

How can I submit this as a bug fix? I can't find a project page or anything, is it internal, or on GitHub?

-- This is the InterSystems query (with identifying values and some excess removed for clarity, but structurally intact):

SELECT TOP (10) *
FROM ( SELECT TOP ALL *
    FROM ( SELECT *
        FROM ( SELECT *
            FROM --Some view or table or sub query 
            AS "Extent1"
            WHERE -- redacted client specific fields
        )  AS "Project1"
    )  AS "Project1"
    WHERE %VID > 1
    ORDER BY "Project1". -- Some Sortable Field 
    DESC
)  AS "top" 

-- This is the correct structure for the query (or something similar, the generator may add extra embedded queries...

SELECT TOP (10) *
FROM ( SELECT *
    FROM ( SELECT TOP ALL *
        FROM ( SELECT *
            FROM --Some view or table or sub query 
            AS "Extent1"
            WHERE -- redacted client specific fields
        )  AS "Project1"
        ORDER BY "Project1". -- Some Sortable Field
    )  AS "Project1"
    WHERE %VID > 1     
    DESC
)  AS "top" 

I'm holding off on answering this in hopes somebody who can direct me to the correct place to report the bug will stop by.

If you don't already have an online support account  (a.k.a. WRC Direct) then I suggest you email your bug report to support@intersystems.com

More info abut InterSystems Support is here.

Thanks John,
I don't think this will get fixed soon enough to be of any use to me, but I've emailed a link to this article to the support address you provided.

Thank you for your comment.

Hi Chris.

As I understand %VID implies not the page number, but amount of rows to skip

So if you have pages with 10 records on each page and you need to show 3rd page then you should use

SELECT TOP (10) ...
 ...
WHERE %VID > 20 -- that is page number 2 multiplied by 10 

More info about %VID:
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

HI Alexander, thank you, I'm aware of this. I am achieving page number in code (note shown) such as follows:

            var page = int.Parse(Request.Query["page"].FirstOrDefault() ?? "1"); // page from query
            var skip = Math.Max(0, count * (page - 1)); // <-- number of records to skip

This doesn't address the original issue, unfortunately. 

Further, regarding Info on %VID, I'm not creating this SQL, it's being created by an Intersystems DLL (for Entity Framework).

Comments

Changing this query to work against a mapped SQL class (backed by a legacy global) without any UNION or VIEW still yields the same issue, so I believe this may have to do with the positioning of the filter clauses and the %VID.

It's been a while and this problem still exists. I reported it to intersystems and we had an email back and forth for a while before it became clear we were going nowhere.

If anybody can offer any further insights on the issue please do. It becomes more and more of an inconvenience as time goes on.