CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworkasp.net-mvc-4
Ganesh UP
Ganesh UP

Reputation: 107

Paging on large amounts of data in MVC using custom logic

I want to implement Paging, Searching, Sorting on large amount of data (DB has 2 - 3 million records) in MVC. While using default paging option in MVC (using PagedList.Mvc, @Html.PagedListPager) all records are fetched for DB & only 100 records are displayed on page. That's time consuming & used more memory.

How can I implement custom logic in MVC that only retrieves exact 100 records per page? Currently I am trying both approaches (using EF and SP).

I have already developed sample applications using EF & SP and implement default paging. But both approaches retrieves all records from DB & then apply paging on client side. That's too time consuming.

public ActionResult Index(int? page) { 
      return View(db.TestUploadData2.ToList().ToPagedList(page ?? 1, 100));
 }

Any suggestions?

Upvotes: 2

Views: 6100

Answers (2)

Sampath
Sampath

Reputation: 65958

Here is the correct way to use PagedList.Mvc. 

public ViewResult Index(int? page)
{
   int pageSize = 100;
   int pageNumber = (page ?? 1);
   return View(db.TestUploadData2.ToPagedList(pageNumber, pageSize));
}

You can read more about this on Paging with the Entity Framework article.

Upvotes: 3

krillgar
krillgar

Reputation: 12815

The problem that you're running into is that you're pulling everything from the database into C# memory and then getting only what you want to show.

db.TestUploadData2.ToList() The .ToList() is what fetches from the database, so you want to put your qualifications before that.

Something like this should be what you need to do:

public IEnumerable<TestUploadData2> GetPagedData(string prop1SearchValue,
                                                 string prop2SearchValue,
                                                 int pageNum = 1,
                                                 int pageSize = 20)
{
    var data = db.TestUploadData2;

    // No idea what your properties are, so I'll just wing with strings.
    if (!string.IsNullOrWhitespace(prop1SearchValue))
    {
        data = data.Where(d => d.Prop1 == prop1SearchValue);
    }

    // Did it like this so you can see how to conditionally filter the query
    if (!string.IsNullOrWhitespace(prop2SearchValue))
    {    
        data = data.Where(d => d.Prop2 == prop2SearchValue);
    }

               // If it is the first page, then 0 * pageSize,
               // second page, skip 1 * pageSize, etc.
    return data.Skip((pageNum - 1) * pageSize)
               // Take only what is being asked for.
               .Take(pageSize)
               // NOW pull it from the database.
               .ToList();
}

Upvotes: 4

Related Questions