none
Paging Performance Issues RRS feed

  • Question

  • Hello,

    I am using the lasted version of EF and Pocos.

    I need to get a page of products where "page" given two parameters:

      PageIndex and PageSize.

    When I display the pager in my data table I would also need the following parameters:

      TotalItems, TotalPages, etc.

    I see that many people use PageList and ToPageList.

    However, this might have performance problems for thousands of products and/or binary data exists.

    I think the query should be executed on the database.

    Can I get the products page and the products count in one query?

    Thank You,

    Miguel

    Monday, September 19, 2011 11:36 AM

Answers

  • Hi Miguel,

    Thanks for your post.

    I think you could use ESQL in your code, and write a SP to return the products count and products page.

    This is the Entity SQL paging example:

    using (AdventureWorksEntities context =
        new AdventureWorksEntities())
    {
        // Create a query that takes two parameters.
        string queryString =
            @"SELECT VALUE product FROM 
              AdventureWorksEntities.Products AS product 
              order by product.ListPrice SKIP @skip LIMIT @limit";
    
        ObjectQuery<Product> productQuery =
            new ObjectQuery<Product>(queryString, context);
    
        // Add parameters to the collection.
        productQuery.Parameters.Add(new ObjectParameter("skip", 3));
        productQuery.Parameters.Add(new ObjectParameter("limit", 5));
    
        // Iterate through the collection of Contact items.
        foreach (Product result in productQuery)
            Console.WriteLine("ID: {0}; Name: {1}",
            result.ProductID, result.Name);
    }
    
    

    However, you use pager and needn't perform your code like the example to do the paging. You just need to know how to use Entity SQL in your code, and then you could do your paging using PageList and ToPageList:

    http://code-inside.de/blog-in/2008/04/08/aspnet-mvc-pagination-view-user-control/

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 21, 2011 6:57 AM
    Moderator