Export (0) Print
Expand All

How to: Page Through Query Results (Entity Framework)

This topic shows how to page the results of a query. The example gets five Product objects after skipping the first three in the query result, which is sorted by Product.ListPrice. The same example is shown using each of the following Entity Framework query technologies:

The example in this topic is based on the Adventure Works Sales Model. To run the code in this example, you must have already added the AdventureWorks Sales Model to your project and configured your project to use the Entity Framework. To do this, complete the procedures in How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework). You can also use the Entity Data Model Wizard to define the AdventureWorks Sales Model. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework).

Example

This is the LINQ to Entities example.

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    // LINQ to Entities only supports Skip on ordered collections.
    IOrderedQueryable<Product> products = AWEntities.Product
            .OrderBy(p => p.ListPrice);

    IQueryable<Product> allButFirst3Products = products.Skip(3);

    Console.WriteLine("All but first 3 products:");
    foreach (Product product in allButFirst3Products)
    {
        Console.WriteLine("Name: {0} \t ID: {1}",
            product.Name,
            product.ProductID);
    }
}

This is the Entity SQL example.

using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    try
    {
        // Create a query that takes two parameters.
        string queryString =
            @"SELECT VALUE product FROM 
              AdventureWorksEntities.Product AS product 
              order by product.ListPrice SKIP @skip LIMIT @limit";

        ObjectQuery<Product> productQuery =
            new ObjectQuery<Product>(queryString, advWorksContext);

        // 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);
    }
    catch (EntityException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

This is the query builder method example.

using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    // Define the parameters used to define the "page" of returned data.
    int skipValue = 3;
    int limitValue = 5;

    try
    {
        // Define a query that returns a "page" or the full 
        // Product data using the Skip and Top methods. 
        // When Top() follows Skip(), it acts like the LIMIT statement.
        ObjectQuery<Product> query = advWorksContext.Product
            .Skip("it.ListPrice","@skip", 
                    new ObjectParameter("skip", skipValue))
            .Top("@limit", new ObjectParameter("limit", limitValue));

        // Iterate through the page of Product items.
        foreach (Product result in query)
            Console.WriteLine("ID: {0}; Name: {1}",
            result.ProductID, result.Name);
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

See Also

Community Additions

Show:
© 2014 Microsoft