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 AWEntities As New AdventureWorksEntities
    'LINQ to Entities only supports Skip on ordered collections.
    Dim products As IOrderedQueryable(Of Product) = _
            AWEntities.Product.OrderBy(Function(p) p.ListPrice)

    Dim allButFirst3Products As IQueryable(Of Product) = products.Skip(3)

    Console.WriteLine("All but first 3 products:")
    For Each product As Product In allButFirst3Products
        Console.WriteLine("Name: {0} \t ID: {1}", _
                product.Name, _
                product.ProductID)
    Next
End Using

This is the Entity SQL example.

Using advWorksContext As New AdventureWorksEntities()
    ' Define the parameters used to define the "page" of returned data.
    Try
        ' Create a query that takes two parameters.
        Dim esqlQuery As String = "SELECT VALUE product FROM " & _
                  " AdventureWorksEntities.Product AS product " & _
                  " order by product.ListPrice SKIP @skip LIMIT @limit"

        Dim productQuery As New ObjectQuery(Of Product)(esqlQuery, advWorksContext)
        ' Add parameters to the collection.
        productQuery.Parameters.Add(New ObjectParameter("skip", 3))
        productQuery.Parameters.Add(New ObjectParameter("limit", 5))


        ' Iterate through the page of Product items.
        For Each result As Product In productQuery
            Console.WriteLine("ID: {0} Name: {1}", _
            result.ProductID, result.Name)
        Next
    Catch ex As EntityException
        Console.WriteLine(ex.ToString())
    Catch ex As InvalidOperationException
        Console.WriteLine(ex.ToString())
    End Try
End Using


This is the query builder method example.

Using advWorksContext As New AdventureWorksEntities()
    ' Define the parameters used to define the "page" of returned data.
    Dim skipValue = 3
    Dim 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.
        Dim query As ObjectQuery(Of Product) = advWorksContext.Product _
            .Skip("it.ListPrice", "@skip", _
                New ObjectParameter("skip", skipValue)) _
            .Top("@limit", New ObjectParameter("limit", limitValue))

        ' Iterate through the page of Product items.
        For Each result As Product In query
            Console.WriteLine("ID:{0} Name: {1}", _
            result.ProductID, result.Name)
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    End Try
End Using

See Also

Community Additions

Show:
© 2014 Microsoft