CODEWITHSUNDEEP
c#mysqlasp.net-mvcentity-frameworkpagedlist
Michael B
Michael B

Reputation: 129

Understanding PagedList mvc and Entity Framework (Slow Queries)

I have a table in a MySQL database with a few thousand records. On my webpage, I display 10 records at a time using PagedList. I generally call the data like this.

using (var ctx = new mydbEntities())
{
    var customers = ctx.customers.OrderBy(o => o.ID).ToPagedList(1,10);
}

If I was to look at the SQL that this generates, am I correct in saying that ToPagedList will only select 10 rows from the Database rather than return everything before taking ten from the result?

On some occasions, I use raw SQL as the query is quite complex and it is built up as a string depending on certain conditions. A simplified example.

using (var ctx = new mydbEntities())
{
    List<MySqlParameter> parameters = new List<MySqlParameter>();
    string strQry = "select * from visitor;";                
    var customers = ctx.Database.SqlQuery<customer>(strQry, parameters.ToArray()).OrderByDescending(o => o.ID).ToPagedList(1,10);
}

I guess this will return all records before applying paging?

Is there an efficient way to apply paging using PagedList to the latter example?

Thanks guys.

Upvotes: 2

Views: 1911

Answers (2)

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

var customers = ctx.customers.OrderBy(o => o.ID).ToPagedList(1,10);

With this query; the Linq to entities translate the query as something like select * from customers order by ID OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY and this is which you desired.

var customers = ctx.Database.SqlQuery<customer>(strQry, parameters.ToArray()).OrderByDescending(o => o.ID).ToPagedList(1,10);

With this query; the strQry query is already fetched by SQL Server and records are started to retrieve. So, desired OrderBy and Pagination actions are performed in memory.

There are two options here;

  1. Use the Linq To Entity queries and translate ToPagedList in IQueryable form

  2. Or modify the strQry query to perform order by and pagination by using something like that

    select * from table order by cl1 OFFSET x ROWS FETCH NEXT y ROWS ONLY

Upvotes: 0

Nick Polyderopoulos
Nick Polyderopoulos

Reputation: 909

I think to better help you with your question it would be nice to take a look at the generated sql for both queries.

From what i have seen on the Repo of the PagedList:

It will do 2 calls on the database:

  1. will take the count of the query provided
  2. will take the actual query with the correct take and skip.

Now your second query if you want to get better performance it may be more effective to write the code as a raw sql query to make the paging function as pointed by @Ivan Stoev.

Either way both queries will be executed twice when you are using the PagedList library.

Be alerted though when using .Database.SqlQuery<customer> the results you get are not cached inside Entity Framework and they will not be tracked even if they are valid Entity Object. For more info about that check below:

  1. Database.SqlQuery Method

  2. DbRawSqlQuery Class

Upvotes: 2

Related Questions