PagedList.Mvc returns all records from the table

I have following function in my Generic Repository. I have two issues with line var list = query.ToPagedList(pageNumber, pageSizeNumber);

public IEnumerable<T> GetPagedListDbContext(string searchFieldName, string searchText, string searchOperator, string sortFieldName, int? page, int? pageSize)
    {
        int pageSizeNumber = (pageSize ?? 20);
        int pageNumber = (page ?? 1);
        string sql = string.Empty;

        sql = string.Format("Select * From {0} q ", typeof(T).Name);
        switch (searchOperator)
        {
            case "Starts With":
                sql += string.Format("Where q.{0} like '{1}%' ", searchFieldName, searchText);
                break;
            case "Ends With":
                sql += string.Format("Where q.{0} like '%{1}' ", searchFieldName, searchText);
                break;
            case "Contains":
                sql += string.Format("Where q.{0} like '%{1}%' ", searchFieldName, searchText);
                break;
            case "Equal To":
                sql += string.Format("Where q.{0} == '{1}' ", searchFieldName, searchText);
                break;
            case "Not Equal To":
                sql += string.Format("Where q.{0} != '{1}' ", searchFieldName, searchText);
                break;
        }

            if (sortFieldName == null)
            {
                sql += "Order By q.Id ";
            }
            else
            {
                sql += string.Format("Order By q.{0} ", sortFieldName);
            }

            // RepoDbSet is DbSet
            DbSqlQuery<T> query = RepoDbSet.SqlQuery(sql);
            var list = query.ToPagedList(pageNumber, pageSizeNumber);

            return list;
        }

It returns all records from table. (Sql trace below)
It execute same query twice.

Sql Trace Event Class Text Data

RPC:Completed exec sp_reset_connection

SQL:BatchCompleted Select * From Book q Order By q.Id

RPC:Completed exec sp_reset_connection

SQL:BatchCompleted Select * From Book q Order By q.Id

Trace stopped

Questions: Why executing query without Skip and Take (e.g. OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY)?

Why executing twice?

When I checked last line return list it has total count 20 not total rows in table and not hitting database again. how it is querying?

Is it possible to do same process using Linq?

Solution

Behind the scenes, ToPagedList is going to perform two operations:

To get the total number of records (for knowing how many pages there will be):

query.Count()

To get the current page of records:

query.Skip((pageNumber - 1) * pageSize).Take(pageSize)

Those result in the two queries you are seeing.

It's going to run your custom, concatenated SQL, create an in memory collection of objects, and then perform LINQ to objects over the collection.

This is an important point to understand when it comes to LINQ. If the collection supports IQueryable, then the collection is passed an Expression Tree that it can carefully translate into the appropriate SQL.

If it only supports IEnumerable, then the query is operated over an in-memory collection of objects.

Evidently DbSqlQuery does not implement IQueryable.

Instead of concatenating SQL, you should expose the underlying table object (EntityFramework? Linq2Sql? What ORM?) and run your queries on that:

var books = context.Books; //or whatever

var query = books.Where(b => b.Name == "Something");

// You can build up a query by chaining more operataions
// on to it
query = query.Where(b => b.PublishDate < DateTime.Today);

query = query.OrderBy(b => b.Id);

// Your ORM may be able to handle the StartsWith, EndsWith, 
// Contains string methods. Check its documentation.
query = query.Where(b => b.Name.StartsWith("The"))

// Since the query object implements IQueryable, it can translate into
// two appropriate SQL queries.
return query.ToPagedList(2, 50);