Sorting remote data for DataGrid when using Load On Demand.
Environment
Product | DataGrid for Xamarin |
Description
The DataGrid's Load On Demand feature gets smaller sets of items incrementally. When using sorting in this scenario, the DataGrid can only sort the items it has because it is not aware of the rest of the items in the incrementally loaded data source.
When sorting is applied, the DataGrid will automatically sort the new items as they are loaded. For example, if a TextColumn was being sorted, you would see:
ABCDEFGHIJ
When Load On Demand is invoked and the next items are loaded, you would see the following after the SortDescriptor is reapplied:
AABBBCCDEEEFGGHHHIJ
Those items are properly sorted according to all of the items in the DataGrid's ItemsSource. However, what if you wanted the result to load everything presorted in the batches like this:
AAAAAAAAAAAAAAAAAA
You need a way to tell the data source to presort those items, then give you the next set of items. Then, you can add the presorted items to the DataGrid.
Solution
The solution to this is to look at the SortDescriptor
that is currently applied to the DataGrid and then tell the data source to sort the items before giving you the next set. To do this, you can look at the DataGrid.SortDescriptors
collection and use that descriptor to build the LINQ or SQL statement.
Example
As example, assume we have a DataGrid to show a list of Products using the LoadOnDemand event. Visit the Load On Demand documentation to understand how this is setup.
The user has sorted the ProductName column and wants all the "A" products from the database first.
private void DataGrid_OnLoadOnDemand(object sender, LoadOnDemandEventArgs e)
{
IEnumerable<Product> nextProducts = null;
// If there is a SortDescriptor
if (DataGrid.SortDescriptors.Any())
{
// Get the sort descriptor (use foreach DataGrid.SortDescriptors for a more robust version of this)
SortDescriptorBase descriptor = DataGrid.SortDescriptors.First();
// If it is a PropertySortDescriptor, you can check for the property name
if (descriptor is PropertySortDescriptor propertyDescriptor)
{
// you can check for the property name
if (propertyDescriptor.PropertyName == "ProductName")
{
// check if it is ascending or descending
if (propertyDescriptor.SortOrder == SortOrder.Ascending)
{
nextProducts = ProductsDataBase
.OrderBy(p => p.ProductName, (IComparer<string>)propertyDescriptor.Comparer)
.Skip(currentCount)
.Take(lodBatchCount);
}
else
{
nextProducts = ProductsDataBase
.OrderByDescending(p => p.ProductName, (IComparer<string>)propertyDescriptor.Comparer)
.Skip(currentCount)
.Take(lodBatchCount);
}
}
}
// If there is a DelegateSortDescriptor, you can directly pass the KeySelector and Comparer without needing any property names
if (descriptor is DelegateSortDescriptor delegateDescriptor)
{
if (delegateDescriptor.SortOrder == SortOrder.Ascending)
{
nextProducts = ProductsDataBase
.OrderBy(p => delegateDescriptor.KeyLookup.GetKey(p), (IComparer<object>)delegateDescriptor.Comparer)
.Skip(currentCount)
.Take(lodBatchCount);
}
else
{
nextProducts = ProductsDataBase
.OrderByDescending(p => delegateDescriptor.KeyLookup.GetKey(p), (IComparer<object>)delegateDescriptor.Comparer)
.Skip(currentCount)
.Take(lodBatchCount);
}
}
}
else
{
// NORMAL - No filtering applied
nextProducts = ProductsDataBase
.Skip(currentCount)
.Take(lodBatchCount);
}
// Get the items
var result = nextProducts?.ToList();
// Now add the fetched items to the DataGrid
foreach (var product in result)
{
DataGridItems.Add(product);
}
// normal LoD logic
currentCount += lodBatchCount;
e.IsDataLoaded = true;
// If there are no more items, unsubscribe from the event.
if (currentCount >= ProductsDataBase.Count)
{
DataGrid.LoadOnDemand -= DataGrid_OnLoadOnDemand;
}
}
SortDescriptor Changes
The last thing to consider in this scenario is "what happens when the user adds/removes a FilterDescriptor"? If the DataGrid is loading pre-sorted data, then the sort was removed, you cannot incrementally add unsorted data on top. You will eventually get a conflict when the same item is added again.
To handle this, you can subscribe to the SortDescriptors.CollectionChanged event handler. In there, take the following steps:
- Clear all the existing items from the DataGrid's ItemsSource first (you dont want to mix unsorted with sorted data).
- Check if you need filtering or not as was done in the Load On Demand event handler.
- Finally, load the same number of items that was already in the DataGrid.
Here's what that looks like for the example.
private void SortDescriptors_CollectionChanged(object sender, System.Collections.Specialized.NotifyCollectionChangedEventArgs e)
{
// You need to start over so that unsorted items aren't mixed with sorted items
DataGridItems.Clear();
IEnumerable<Product> nextProducts = null;
// If the sort descriptor was removed, get all the items that were previously loaded, but unsorted.
if (DataGrid.SortDescriptors.Count == 0)
{
foreach (var product in ProductsDataBase.Take(currentCount))
{
DataGridItems.Add(product);
}
return;
}
// If a sort descriptor was added, presort but fetch the currentCount without skipping
if (e.NewItems?.Count > 0)
{
SortDescriptorBase descriptor = e.NewItems[0] as SortDescriptorBase;
// PropertySortDescriptor approach
if (descriptor is PropertySortDescriptor propertyDescriptor)
{
if (propertyDescriptor.PropertyName == "ProductName")
{
// check if it is ascending or descending
if (propertyDescriptor.SortOrder == SortOrder.Ascending)
{
//
nextProducts = ProductsDataBase
.OrderBy(p => p.ProductName, (IComparer<string>)propertyDescriptor.Comparer)
.Take(currentCount);
}
else
{
nextProducts = ProductsDataBase
.OrderByDescending(p => p.ProductName, (IComparer<string>)propertyDescriptor.Comparer)
.Take(currentCount);
}
}
}
// DelegateSortDescriptor approach
if (descriptor is DelegateSortDescriptor delegateDescriptor)
{
if (delegateDescriptor.SortOrder == SortOrder.Ascending)
{
nextProducts = ProductsDataBase
.OrderBy(p => delegateDescriptor.KeyLookup.GetKey(p), (IComparer<object>)delegateDescriptor.Comparer)
.Take(currentCount);
}
else
{
nextProducts = ProductsDataBase
.OrderByDescending(p => delegateDescriptor.KeyLookup.GetKey(p), (IComparer<object>)delegateDescriptor.Comparer)
.Take(currentCount);
}
}
}
var result = nextProducts?.ToList();
foreach (var product in result)
{
DataGridItems.Add(product);
}
}
private void DataGrid_OnLoadOnDemand(object sender, LoadOnDemandEventArgs e)
{
if (currentCount == allProducts.Count) return null;
var result = FetchData(allProducts, DataGrid.SortDescriptors, currentCount, batchCount);
currentCount += batchCount;
foreach (var product in result)
{
DataGridItems.Add(product);
}
}
private void MySortDescriptorsCollectionChanged(object sender, NotifyCollectionChangedEventArgs e)
{
DataGridItems?.Clear();
var result = FetchData(allProducts, DataGrid.SortDescriptors, currentCount);
foreach (var product in result)
{
DataGridItems?.Add(product);
}
}
// Reusable method that leverages Queryable to combine multiple expressions before invoking the query.
private static IEnumerable<Product> FetchData(ObservableCollection<Product> products, SortDescriptorCollection descriptors, int currentItemCount, int? batchItemCount = null)
{
IQueryable<Product> query = products.AsQueryable();
if (descriptors == null || descriptors.Count == 0)
{
query.Take(currentItemCount);
}
else
{
foreach (var descriptor in descriptors)
{
if (descriptor is PropertySortDescriptor propertyDescriptor)
{
// check the property name and construct the LINQ query accordingly
if (propertyDescriptor.PropertyName == "ProductName")
{
if (propertyDescriptor.SortOrder == SortOrder.Ascending)
{
query.OrderBy(p => p.ProductName, (IComparer<string>)propertyDescriptor.Comparer);
}
else
{
query.OrderByDescending(p => p.ProductName, (IComparer<string>)propertyDescriptor.Comparer);
}
}
// Alternate - If you use System.Linq.Dynamic, you can pass a SQL-like expression in a string for the KeySelector
// (propertyDescriptor.SortOrder == SortOrder.Ascending)
//
// query.OrderBy(propertyDescriptor.PropertyName);
//
//se
//
// query.OrderByDescending(propertyDescriptor.PropertyName);
//
}
else if (descriptor is DelegateSortDescriptor delegateDescriptor)
{
if (delegateDescriptor.SortOrder == SortOrder.Ascending)
{
query.OrderBy(p => delegateDescriptor.KeyLookup.GetKey(p), (IComparer<object>) delegateDescriptor.Comparer);
}
else
{
query.OrderByDescending(p => delegateDescriptor.KeyLookup.GetKey(p), (IComparer<object>) delegateDescriptor.Comparer);
}
}
}
}
if (batchItemCount == null)
{
query.Take(currentItemCount);
}
else
{
query.Skip(currentItemCount).Take((int)batchItemCount);
}
// Invoke the query
var result = query.ToList();
return result;
}
This approach can also be used for FilterDescriptors. Use the same concept with the
DataGrid.FilterDescriptors
collection and write the custom logic accordingly.