New post
Question
· Jan 23, 2018

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?

Discussion (11)1
Log in or sign up to continue
Chris Sprague · Jan 23, 2018

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. 

0 0
Alexander Koblov · Jan 23, 2018

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=...

0 0
Chris Sprague · Jan 25, 2018

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" 

0 0