WAQS Pagination

7 reasons to use WAQS

WAQS documentation

 

Lazy Pagination

With LINQ To Entities, you can use two methods for pagination: Take and Skip.

Pagination on lists / grids is an enough frequent use case to develop classes that does the job for us.

WAQS has a class for it: PaginatedQuery.

WAQS (for WPF only) also adds a NavigationPanel control with First, Previous, Next and Last buttons, a TextBox to specify the page and a TextBlock to show the number of pages.

In order to have a reusable code, each button is bound using an attached property defined outside control:

<Button Content=“&lt;&lt;”
             local:PaginatedActions.GoFirst=”{Binding Source} />
<Button Content=“&lt;”
             local:PaginatedActions.GoPrevious=”{Binding Source} />
<TextBox Text=”{Binding Source.PageIndex, Mode=TwoWay}>
    <interactivity:Interaction.Behaviors>
        <local:ValidateBindingOnEnterBehavior />
    </interactivity:Interaction.Behaviors>
</TextBox>
<TextBlock Text=”{Binding Source.MaxPage} />
<Button Content=“&gt;”
             local:PaginatedActions.GoNext=”{Binding Source} />
<Button Content=“&gt;&gt;”
             local:PaginatedActions.GoLast=”{Binding Source} />



Property Source is a dependency property of NavigationPanel class which will be bound on a PaginatedQuery.



 


PaginatedQuery


PaginatedQuery uses Take and Skip methods to only load the expected page.


Note that Skip method needs the usage of OrderBy(Descending) method in T-SQL.


Like the ByStepQuery, it’s very easy to the PaginatedQuery using the ObservableCollection but you not have to.


To load customers by page of 20, you can use the following code:


private PaginatedQuery<Customer> _customers;
public PaginatedQuery<Customer> Customers
{
    get { return _customers ?? (_customers = _context.Customers.AsAsyncQueryable().OrderBy(c => c.CompanyName).ToPaginatedQuery(20).LoadPage()); }
}









You can use the following xaml to show it:


<Grid>
    <Grid.RowDefinitions>
        <RowDefinition />
        <RowDefinition Height=“Auto” />
    </Grid.RowDefinitions>
    <ListBox ItemsSource=”{Binding Customers.Items} />
    <controls:NavigationPanel Grid.Row=“1” 
                                           Source
=”{Binding Customers} />
</Grid>









 


Load the page containing an entity


Imagine that you are working with a product and you want to update its supplier. In order to do it, we could show the list of suppliers and we can select one.


Now, imagine that this list is paginated. It would be better to load the page containing the current one.


How to do it?

With LINQ, there is a Select method with index. It would be very helpful but, sadly, Entity Framework does not support it and, contrary to DateTime calculation, we can’t only use an EdmFunction to help us.


In order to achieve it, we will run two queries on the server: one to get the page index and another one to load this page.


 

How to get the page index?

To get the page index, we just have to count all rows before the current one.


In order to do it, we have to specify the values of properties used in OrderBy.


For example, if suppliers are ordered by SupplierId, we can use this query:


context.Suppliers.Select(s => s.Id < currentSupplierId ? 1 : 0).Sum()









Now, if the OrderBy is on CompanyName and then on SupplierId, we can use this query:


context.Suppliers.Select(s => s.CompanyName < currentCompanyName ? 1 : s.CompanyName > currentCompanyName ? 0 : s.Id < currentSupplierId ? 1 : 0).Sum()









But here, we have a problem: string comparison works in SQL but not in .NET!


When we build a LINQ expression using strings comparison, there is a test that throws an exception if we use strings before calling an internal method. Note that if we call the internal method (using Reflection) to avoid the test, it works fine but, anyway, this is not a good practice.


So we have to find a new way to do it and CSDL Functions will be very helpful.


 

What is a CSDL Function?

To query with Entity Framework, you can use 3 different ways.


The first (most used) one is LINQ To Entities.


A second one if Entity SQL (ESQL).


The last one is query builder.


In the edmx, the entity description is named CSDL (Conceptual schema definition language).


In this CSDL, you can define some functions written in ESQL.


When you run WCFAsyncQueryableServicesServer PowerShell command in NuGet package Manager Console, WAQS generates a copy of your edmx. So WAQS uses another edmx than the one you created.


And WAQS adds in the new one these CSDL functions:


<Function Name=LessThanString ReturnType=Boolean>
  <Parameter Name=s1 Type=String />
  <Parameter Name=s2 Type=String />
  <DefiningExpression>s1 &lt; s2</DefiningExpression>
</Function>









<Function Name=GreaterThanString ReturnType=Boolean>
  <Parameter Name=s1 Type=String />
  <Parameter Name=s2 Type=String />
  <DefiningExpression>s1 &gt; s2</DefiningExpression>
</Function>









Then, WAQS generates two methods for them with EdmFunction attribute in order to be able to use them in LINQ queries.


So, using this way, we will be able to calculate the page index and to load this page.


 


PaginatedQuery

Using the following PaginatedQuery,


private PaginatedQuery<Supplier> _suppliers;
public PaginatedQuery<Supplier> Suppliers
{
    get { return _suppliers ?? (_suppliers = _context.Suppliers.AsAsyncQueryable().OrderBy(s => s.CompanyName).ToPaginatedQuery(10)); }
}









we, you can load the page containing property SelectedSupplier with this way:


Suppliers.LoadPage(
    new LoadPageParameter { PropertyName = “CompanyName”, Value = SelectedSupplier.CompanyName },
    new LoadPageParameter { PropertyName = “Id”, Value = SelectedSupplier.Id });









Note that, if you don’t like using string to set property name, you can also use expression:


Suppliers.LoadPage(
    new LoadPageParameter {PropertyName = PropertyName.GetPropertyName((Supplier s) => s.CompanyName), Value = SelectedSupplier.CompanyName},
    new LoadPageParameter {PropertyName = PropertyName.GetPropertyName((Supplier s) => s.Id), Value = SelectedSupplier.Id});









Note that LoadPageParameter class also has a bool property Descending.


 


So, for example, at the end, we can use this code:


private Product _selectedProduct;
public Product SelectedProduct
{
    get { return _selectedProduct; }
    set
    {
        _selectedProduct = value;
        NotifyPropertyChanged.RaisePropertyChanged(() => SelectedProduct);
        NotifyPropertyChanged.RaisePropertyChanged(() => SelectedSupplier);
        if (Suppliers != null)
            Suppliers.LoadPage(
                new LoadPageParameter { PropertyName = PropertyName.GetPropertyName((Supplier s) => s.CompanyName), Value = SelectedSupplier.CompanyName },
                new LoadPageParameter {PropertyName = PropertyName.GetPropertyName((Supplier s) => s.Id), Value = SelectedSupplier.Id});
    }
}

private PaginatedQuery<Supplier> _suppliers;
public PaginatedQuery<Supplier> Suppliers
{
    get { return _suppliers ?? (_suppliers = _context.Suppliers.AsAsyncQueryable().OrderBy(s => s.CompanyName).ToPaginatedQuery(10,
callBack: () => NotifyPropertyChanged.RaisePropertyChanged(() => SelectedSupplier))); }
}

public Supplier SelectedSupplier
{
    get { return SelectedProduct == null ? null : SelectedProduct.Supplier; }
    set { }
}







											
This entry was posted in 16868. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>