I have about 14,000 rows of data. If use the following EF query, its a long time to load because I suspect it is loading all 14,000 rows and only then is any additional filtering done. This is my Select method in my repository.
Public Function SelectAll() As IEnumerable(Of be_Posts) Implements IPostRepository.SelectAll
Dim posts As IEnumerable(Of be_Posts)
Using db As Ctx = New Ctx
posts = db.be_Posts.OrderByDescending(Function(x) x.DateCreated).ToList
Return posts
End Using
And Controller:
Function Index(page As Integer?) As ActionResult
Dim PageSize = System.Web.Configuration.WebConfigurationManager.AppSettings("PageSize")
Dim pageNumber As Integer = If(page, 1)
Dim posts = _repo.SelectAll()
Return View(posts.ToPagedList(pageNumber, PageSize))
End Function
Helper in View:
@Html.PagedListPager((Model), Function(page) Url.Action("Index", New With { _
.page = page _
}), PagedListRenderOptions.ClassicPlusFirstAndLast)
Now If add in a take
clause, for example .Take(500)
then things are dramatically faster. How can I make this query faster and still work will all the records? I am also using Troy Goode's PagedList extension to get paging functionality. Everything is fine as long as i get just a few hundred records. So what to do? Most if not all examples of paging that I can find that use Troy's library involve all the code directly in the controller.
Calling ToList
executes the query and, as you say, it's getting every record. Paging is done using Skip
and Take
, e.g.
Dim page = list.Skip(pageSize * (pageNumber - 1)).Take(pageSize)
The source list can be the table itself or the result of a Where
or OrderBy
call or whatever. Just make sure that ToList
or, preferably, ToArray
is called last.