0
774
Question Chris Sprague · Jan 23, 2018

Entity Framework provider seems to generate incorrect SQL when implementing paging

#Caché #SQL

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

Comments

Chris Sprague · Jan 23, 2018

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.

0
Chris Sprague  Jan 23, 2018 to Alexander Koblov

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
Chris Sprague  Jan 23, 2018 to Alexander Koblov

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

0
Chris Sprague  Jan 25, 2018 to Chris Sprague

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.

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
John Murray  Jan 26, 2018 to Chris Sprague

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.

0
Chris Sprague  Jan 29, 2018 to John Murray

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.

0
Chris Sprague · Jan 15, 2019

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.

0