Silverlight, DataPager, RIA Services, and smart paging

Question

I'm still trying to get my feet on the ground with Silverlight and RIA Services, and of course starting with some of the more "fun" stuff like grids and intelligent paging. I can connect to RIA Services (using a home-grown ORM, not L2S or EF), get data on the grid, and connect to a DataPager. The domain service is working well with the home-grown ORM, at least for queries. (Still working on full CRUD.) However, there are still problems:

1. To support the user application, I need user-controlled sorting and filtering, in addition to smart paging (only run the query for the rows needed to display) and grouping.

2. So far, I've seen nothing in the DataGrid or DataPager to externalize these capabilities so that filtering, sorting, and paging parameters can be passed to the server to build the appropriate query.

3. The datasets are potentially quite large; my table I've chosen for prototyping work can have up to 35,000 entries at some customers, and I'm sure there are other tables far larger that I will have to deal with at some point. So the "smart paging" aspect is essential.

Ideas, suggestions, guidance, and nerf bricks are all welcome.

Answer 1

OK, I've spent a few days in the weeds with this one, and I think I've got a handle on it.

First, an important piece of magic. For paging to work properly, the pager has to know the total item count, no matter how many items were returned by the current query. If the query returns everything, the item count is obviously the number of items returned. For smart paging, the item count is still the total of available items, although the query returns only what gets displayed. With filtering, even the total of available items changes every time the filter changes.

The Silverlight Datapager control has a property called ItemCount. It is readonly and cannot be databound in XAML, or set directly in code. However, if the user control containing the pager has a DataContext that implements IPagedCollectionView, then the data context object must implement an ItemCount property with PropertyChanged notification, and the DataPager seems to pick this up automagically.

Second, I highly recommend Brad Abrams' excellent series of blog posts on RIA Services, especially this one on ViewModel. It contains most of what you need to make paging and filtering work, although it's missing the critical piece on managing the item count. His downloadable sample also contains a very good basic framework for implementing ModelViewViewModel (MVVM). Thank you, Brad!

So here's how to make the item count work. (This code refers to a custom ORM, while Brad's code uses Entity Framework; between the two you can figure you what you need in your environment.)

First, your ORM needs to support getting record counts, with and without your filter. Here's my domain service code that makes the counts available to RIA Services:

[Invoke]
public int GetExamCount()
{
    return Context.Exams.Count();
}

[Invoke]
public int GetFilteredExamCount(string descriptionFilter)
{
    return Context.Exams.GetFilteredCount(descriptionFilter);
}

Note the [Invoke] attribute. You need this for any DomainService method that doesn't return an Entity or an Entity collection.

Now for the ViewModel code. You need an ItemCount, of course. (This is from Brad's example.)

    int itemCount;
    public int ItemCount
    {
        get { return itemCount; }
        set
        {
            if (itemCount != value)
            {
                itemCount = value;
                RaisePropertyChanged(ItemCountChangedEventArgs);
            }
        }
    }

Your LoadData method will run the query to get the current set of rows for display in the DataGrid. (This doesn't implement custom sorting yet, but that's an easy addition.)

    EntityQuery<ExamEntity> query = 
        DomainContext.GetPagedExamsQuery(PageSize * PageIndex, PageSize, DescriptionFilterText);
    DomainContext.Load(query, OnExamsLoaded, null);

The callback method then runs the query to get the counts. If no filter is being used, we get the count for all rows; if there's a filter, then we get the count for filtered rows.

private void OnExamsLoaded(LoadOperation<ExamEntity> loadOperation)
{
    if (loadOperation.Error != null)
    {
        //raise an event... 
        ErrorRaising(this, new ErrorEventArgs(loadOperation.Error));
    }
    else
    {
        Exams.MoveCurrentToFirst();
        if (string.IsNullOrEmpty(DescriptionFilterText))
        {
            DomainContext.GetExamCount(OnCountCompleted, null);
        }
        else
        {
            DomainContext.GetFilteredExamCount(DescriptionFilterText, OnCountCompleted, null);
        }
        IsLoading = false;
    }
}

There's also a callback method for counts:

void OnCountCompleted(InvokeOperation<int> op)
{
    ItemCount = op.Value;
    TotalItemCount = op.Value;
}

With the ItemCount set, the Datapager control picks it up, and we have paging with filtering and a smart query that returns only the records to be displayed!

LINQ makes the query easy with .Skip() and .Take(). Doing this with raw ADO.NET is harder. I learned how to do this by taking apart a LINQ-generated query.

SELECT * FROM 
    (select ROW_NUMBER() OVER (ORDER BY Description) as rownum, * 
     FROM Exams as T0  WHERE T0.Description LIKE @description ) as T1 
WHERE T1.rownum between @first AND @last ORDER BY rownum

The clause "select ROW_NUMBER() OVER (ORDER BY Description) as rownum" is the interesting part, because not many people use "OVER" yet. This clause sorts the table on Description before assigning row numbers, and the filter is also applied before row numbers are assigned. This allows the outer SELECT to filter on row numbers, after sorting and filtering.

So there it is, smart paging with filtering, in RIA Services and Silverlight!

Answer 2

Here's the quick and dirty solution (that I went for):

Just move your DomainDataSource to your ViewModel! Done!

May not exactly be great for testability and probably some other limitations I haven't discovered yet, but personally I don't care about that until something better comes along.

Inside your ViewModel just instantiate the data source :

// Feedback DataSource
_dsFeedback = new DomainDataSource();
_dsFeedback.DomainContext = _razorSiteDomainContext;
_dsFeedback.QueryName = "GetOrderedFeedbacks";
_dsFeedback.PageSize = 10;
_dsFeedback.Load();

and provide a bindable property :

private DomainDataSource _dsFeedback { get; set; }
public DomainDataSource Feedback 
{
    get 
    {
        return _dsFeedback;
    }
}

And add your DataPager to your XAML:

  <data:DataPager Grid.Row="1"
                  HorizontalAlignment="Stretch" 
                  Source="{Binding Feedback.Data}" 
                  Margin="0,0,0,5" />

  <data:DataGrid ItemsSource="{Binding Feedback.Data}">

PS. Thanks to 'Francois' from the above linked page. I didn't even realize I could take the DomainDataSource out of the XAML until I saw your comment!