How to query with entity and fitlering and pagedlist with one milion rows

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.

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.