Using paging (Skip, Take) over multiple DbSets

I have an Entity Framework DbContext with two different DbSets.

In my view I am combining these two sets into the same view model and listing them in the same table.

I want to support table paging to be able to query only a page of records at a time sorted by a particular column. I can't see how to do this without reading all of the records from the database and then paging from memory.

For example, I want to be able to sort by date ascending since both tables have a date column. I could simply take the page size from both tables and then sort in memory but the problem comes into play when I am skipping records. I do not know how many to skip in each table since it depends on how many records are found in the other table.

Is there a way to manipulate Entity Framework to do this?

Answers


It is possible.

  • JOin them in the database (can be done in EF).
  • Project that (select new {}) into the final object
  • Order by, skip, take on that projection.

It will be crap performance wise but there is no way around that given you have a broken database model. It basically has to get a tempoary view of all rows for the SQL to find the first ones - that will be slow.


Your best bet is going to be to combine them with a stored procedure or view, and then map that sp/view into Entity Framework. Combing them on the client is going to kill performance - let the server do it for you; it is clearly a server side task.


Need Your Help

How do I properly test an API Controller that returns json for a non null response?

c# json moq asp.net-web-api2 mstest

I have this test method for testing an API controller, that returns a JSON string, for a non null response.

How to test website type project using XUnit?

asp.net .net automated-tests xunit xunit.net

I am new to XUnit. I have bit knowledge to test a WebApplication using XUnit(Created Test class and open XUnit.gui->Open Assembly->TestApplication DLL and RUN). My question is how to test a website...