0

I want To query 1 million rows with entity framework and then paging them. I use pagedlist.mvc library and it is not problem for first page because I used .ToPagedList(pageNumber, pageSize) in My code and it is not necessary to load all of my data.

 var allrows = db.works.Where(x => x.Status == 100).OrderByDescending(x => x.ID_Work).ToPagedList(pageNumber, pageSize);

but when I add filtering and paging action, First I should load All rows and then filter them and after that use .ToPagedList(pageNumber, pageSize).

var allrows = db.works.Where(x => x.Status == 100);
     if(!String.IsNullOrEmpty(Code_Work))
      {
       allrows= allrows.Where(x => x.Code_Work.Contains(Code_Work));
       ViewBag.CurrentCode_Work = Code_Work;
      }
var pagedrows = allrows.OrderByDescending(x => x.ID_Work).ToPagedList(pageNumber, pageSize);

How Can I handle this challenge. I think it could be problem and reduce performance. I don't want to load all of my rows.

Ali SH elcid
  • 57
  • 8

1 Answers1

0

You should read difference between IEnumerable and IQueryable

In your case you need to first make the filter query using IQueryable

IQueryable<works> worksDetails = db.works.Where(x => x.Status == 100)
              .OrderByDescending(x => x.ID_Work); // just creates the sql query to filter records

Finally hit the database to get records using PagedList

var pagedrows = worksDetails.ToPagedList(pageNumber, pageSize); 
                                // hits the database and executes the sql query to filter records

Hope this helps.

Shaiju T
  • 6,201
  • 20
  • 104
  • 196
  • 1
    ok and then when we use IQueryable it doesn't load data ? it's good. – Ali SH elcid May 20 '19 at 11:07
  • @AliSHelcid , yes correct, its's good because it first creates the query and then runs it in the database. So only filtered records are retrieved from database. But in the case of `IEnumerable` all 1 million rows is retrieved from database and then it filters the data, So there is performance loss. – Shaiju T May 20 '19 at 11:48