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!