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=18.104.22.168, 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?