Version 8.4.5 loads the entire table into memory

[7702244]

Describe the bug
After upgrade to the new version, when using await query.ToPagedListAsync(pageNumber, pageSize) all table data loads into memory! When used together with Entity Framework, 2 queries are actually executed. One loads the entire table into memory, the second loads data only for the selected page.

To Reproduce
Steps to reproduce the behavior:

1. Create some query in EF:
   IQueryable<Payment> query = db.Payments.AsNoTracking();
2. Try to load page: await query.ToPagedListAsync(1, 10);
3. Put breakpoint at row with .ToPagedListAsync and start profiling real SQL queries.
4. Put breakpoint on next row and stop profiling.
5. You will see the problem.

Expected behavior
1 query for counting all rows and 1 query with data only for selected page.

[ernado-x]

Should be fixed in https://www.nuget.org/packages/X.PagedList/8.4.6

[tyfarquhar]

I still have the issue as described above. I'm paging through customer records, but they are all being loaded into memory so it's extremely slow. Happens with 8.4.5 and 8.4.6 also. Reverting to 8.4.3 fixes the issue.

[ernado-x]

@tyfarquhar did you pass IQueryable collection?

[tyfarquhar]

Yes, I'll try to get some details, but essentially I'm doing the same as above. I have a huge list of customers that I build an IQueryable query to filter. I then call query.ToPagedListAsync(pagenumber, pagesize); With v8.4.6 it still takes 30+ seconds to complete and I can see it's loading every record into memory. With 8.4.3 and before it's less than a second to complete.

[tyfarquhar]

Looking at Application Insights I can see the difference in the queries generated. Looks like it's not doing a SELECT COUNT in v8.4.6 for me. It's loading every entity and doing the count in memory. In v8.4.3 I have this query:

SELECT COUNT(*)
FROM [CustRecords] AS [c]
WHERE [c].[LocationID] IN (1, 2, 3, 4, 5)

In v8.4.6 it just queries all the records instead of a count.

[tyfarquhar]

Debugging it's this line here

X.PagedList/src/X.PagedList/PagedListExtensions.cs

Line 314 in 329e7e8

totalCount = await superset.CountAsync(cancellationToken).ConfigureAwait(false);

[ernado-x]

@tyfarquhar please try install https://www.nuget.org/packages/X.PagedList.EF and use extension method from X.PagedList.EF namespace like it implemented here: https://github.com/dncuug/X.PagedList/blob/fix-ef-paging/examples/Example.Website/Controllers/EFController.cs

[tyfarquhar]

Thank you sir. I'll give it a try.

[tyfarquhar]

Everything is working as expected now. Thanks.

[Zaitungtung]

I am experiencing this same problem with version 9.1.2. If I pass the IEnumerable directly from EF Core to ToPagedList it only retrieves 3 records, however, if I do ToList first the problem is solved.

[ernado-x]

@Zaitungtung please provide code example

[Zaitungtung]

var students = _readContext.Students?
.Include(e => e.States)?
.Include(e => e.Subjects)

Here if I debug "students" it has all the properties I have included (lists, objects, etc.).

var viewModel = students.ToPagedList(page, pageSize)

At this point I only have the basic type properties (string, int, datetime, etc.) but the lists and custom objects are missing.