In some cases there may be too much data to load into memory at once. Consider for example a table with a million rows or more. Even if you could load all this data into memory, the process would take a long time.

There are many ways to deal with these scenarios. You could create queries that summarize and cache the data on the server and use web services to deliver data to your Silverlight client. Still you would end up with tables that can be used with C1OlapPage.

In this sample we will use a WCF service to access Northwind data stored in a SQL database. The interesting part of this sample is that not all data will be loaded into memory at one time. The C1OlapPage only requests data for customers that are currently included in the filter.

For this sample we will create a Silverlight project inside an ASP.Net web site. We will also use LINQ to SQL classes to query data from the sample Northwind database. LINQ to SQL is an ORM (object relational mapping) implementation that ships with Visual Studio (2008 and higher). It allows you to model a relational database using .NET classes which you can query against using LINQ.

First, we create a LINQ to SQL representation of the Northwind database. Right-click the web site project associated with your Silverlight project and click “Add New Item…” Select LINQ to SQL Classes and name it NorthwindDataClasses.dbml.

Next, we bring in all of the data fields from the “Invoices” view by dragging items from the Server Explorer.

Then we create a WCF Service that queries this data using LINQ and our LINQ to SQL Classes (NorthwindDataClasses) just created. Right-click the web site project node and click “Add New Item…” Select WCF Service and name it NorthwindDataService.svc.

Replace the code in NorthwindDataService.svc with the following code:

Imports System.Linq
Imports System.Runtime.Serialization
Imports System.ServiceModel
Imports System.ServiceModel.Activation
Imports System.Collections.Generic
Imports System.Text
Namespace SqlFilter.Web
    <ServiceContract([Namespace] := "")> _
    <AspNetCompatibilityRequirements(RequirementsMode := AspNetCompatibilityRequirementsMode.Allowed)> _
    Public Class NorthwindDataService
        ''' <summary>/// Get all invoices. /// </summary>        [OperationContract]
        Public Function GetInvoices() As List(Of Invoice)
            Dim ctx = New NorthwindDataClassesDataContext()
            Dim invoices = From inv In ctx.Invoicesinv
            Return invoices.ToList()
        End Function
        ''' <summary>/// Get all customers. /// </summary>        [OperationContract]
        Public Function GetCustomers() As List(Of String)
            Dim ctx = New NorthwindDataClassesDataContext()
            Dim customers = (From inv In ctx.Invoicesinv.CustomerName).Distinct()
            Return customers.ToList()
        End Function
        ''' <summary>/// Get all invoices for a specific set of customers. /// </summary>        [OperationContract]
        Public Function GetCustomerInvoices(ParamArray customers As String()) As List(Of Invoice)
            ' build hashset            var hash = new HashSet<string>();
            For Each c As String In customers
                hash.Add(c)
            Next
            Dim customerList As String() = hash.ToArray()
            ' get invoices for customers in the list            var ctx = new NorthwindDataClassesDataContext();
            Dim invoices = From inv In ctx.Invoices Where customerList.Contains(inv.CustomerName)inv
            Return invoices.ToList()
        End Function
    End Class
End Namespace

using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Collections.Generic;
using System.Text;
namespace SqlFilter.Web
{
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class NorthwindDataService
    {
        /// <summary>/// Get all invoices. /// </summary>        [OperationContract]
        public List<Invoice> GetInvoices()
        {
            var ctx = new NorthwindDataClassesDataContext();
            var invoices =
                from inv in ctx.Invoices
                select inv;
            return invoices.ToList();
        }
        /// <summary>/// Get all customers. /// </summary>        [OperationContract]
        public List<string> GetCustomers()
        {
            var ctx = new NorthwindDataClassesDataContext();
            var customers =
                (from inv in ctx.Invoices
                    select inv.CustomerName).Distinct();
            return customers.ToList();
        }
        /// <summary>/// Get all invoices for a specific set of customers. /// </summary>        [OperationContract]
        public List<Invoice> GetCustomerInvoices(params string[] customers)
        {
            // build hashset            var hash = new HashSet<string>();
            foreach (string c in customers)
            {
                hash.Add(c);
            }
            string[] customerList = hash.ToArray();
            // get invoices for customers in the list            var ctx = new NorthwindDataClassesDataContext();
            var invoices =
                from inv in ctx.Invoices
                where customerList.Contains(inv.CustomerName)
                select inv;
            return invoices.ToList();
        }
    }
}

Notice here we have defined 3 methods for our web service. The first two are simple Get methods which return a list of items using LINQ and our LINQ to SQL classes created earlier. The GetCustomerInvoices method is special in that it accepts an array of customers as parameter. This is our filter that will be defined on the client in our Silverlight C1OlapGrid project.

Before moving to the Silverlight project we must build the web site project, and add a reference to our web service. To add the reference, right-click the Silverlight project node in the Solution Explorer and click “Add Service Reference.” Then click “Discover” and select the NorthwindDataService.svc. Rename it “NorthwindDataServiceReference” and click OK.

Now that the data source is ready, we need to connect it to C1OlapPage to ensure that:

1. The user can see all the customers in the filter (not just the ones that are currently loaded) and
2. When the user modifies the filter, new data is loaded to show any new customers requested.

Before we accomplish these tasks we should set up our UI. In MainPage.XAML, add a C1OlapPage control and a couple of TextBlocks which will be used as status strips:

<Grid x:Name="LayoutRoot">
<Grid.RowDefinitions>
              <RowDefinition />
              <RowDefinition Height="Auto"/>
</Grid.RowDefinitions>
<olap:C1OlapPage x:Name="_c1OlapPage"/>
<TextBlock x:Name="_lblLoading"FontSize="24"Opacity=".5"Text="Loading data..."HorizontalAlignment="Center"VerticalAlignment="Center"/>
<TextBlock x:Name="_lblStatus"Text="Ready"HorizontalAlignment="Right"Grid.Row="1"/>
</Grid>

Then add the following code to the form:

Dim _allCustomers As ObservableCollection(Of String)
Dim _invoices As ObservableCollection(Of NorthwindDataServiceReference.Invoice)
Dim _customerFilter As C1OlapFilter

ObservableCollection<string> _allCustomers;
ObservableCollection<NorthwindDataServiceReference.Invoice> _invoices;
C1OlapFilter _customerFilter;

These fields will contain a complete list of all the customers in the database, a list of the customers currently selected by the user, and the maximum number of customers that can be selected at any time.

We need to assign the complete list of customers to the C1OlapField.Values property. This property contains a list of the values that are displayed in the filter. By default, C1OlapPage populates this list with values found in the raw data. In this case, the raw data will only contain a partial list, so we need to provide the complete version instead. The _allCustomers ObservableCollection will hold our entire collection of possible customers for the user to select among. The C1OlapPage will actually work with the _invoices collection, which will be the dataset filtered by the selected customers.

Replace the following code in MainPage():

Public Sub New()
    InitializeComponent()
    ' initialize OlapPage data source    _invoices = new ObservableCollection<SqlFilter.NorthwindDataServiceReference.Invoice>();
    _c1OlapPage.DataSource = _invoices
    ' initialize OlapPage view    var olap = _c1OlapPage.OlapEngine;
    olap.BeginUpdate()
    olap.ColumnFields.Add("OrderDate")
    olap.RowFields.Add("CustomerName")
    olap.ValueFields.Add("ExtendedPrice")
    olap.RowFields(0).Width = 200
    olap.Fields("OrderDate").Format = "yyyy"
    olap.Fields("CustomerName").Filter.ShowValues = selectedCustomers.ToArray()
    olap.EndUpdate()
    ' get list of all customers in the database    var sc = new SqlFilter.NorthwindDataServiceReference.NorthwindDataServiceClient();
    sc.GetCustomersCompleted += sc_GetCustomersCompleted
        ' show status    _lblStatus.Text = "Retrieving customer list...";
    sc.GetCustomersAsync()
End Sub

public MainPage()
{
    InitializeComponent();
     // initialize OlapPage data source    _invoices = new ObservableCollection<SqlFilter.NorthwindDataServiceReference.Invoice>();
    _c1OlapPage.DataSource = _invoices;
    // initialize OlapPage view    var olap = _c1OlapPage.OlapEngine;
    olap.BeginUpdate();
    olap.ColumnFields.Add("OrderDate");
    olap.RowFields.Add("CustomerName");
    olap.ValueFields.Add("ExtendedPrice");
    olap.RowFields[0].Width = 200;
    olap.Fields["OrderDate"].Format = "yyyy";
    olap.Fields["CustomerName"].Filter.ShowValues = selectedCustomers.ToArray();
    olap.EndUpdate();
 
    // get list of all customers in the database    var sc = new SqlFilter.NorthwindDataServiceReference.NorthwindDataServiceClient();
    sc.GetCustomersCompleted += sc_GetCustomersCompleted;
    sc.GetCustomersAsync();
    // show status    _lblStatus.Text = "Retrieving customer list...";
}

Here we initialize our C1OlapPage data source, we create a default view and we get a list of all customers in the database. We need to get a complete list of all the customers in the database so the user can select the ones he wants to look at. Note that this is a long list but compact list. It contains only the customer name, not any of the associated details such as orders, order details, etc.

Since our data is coming from a web service, it is being retrieved asynchronously and the sc_GetCustomersCompleted event is fired when the data has finished loading.

Private Sub sc_GetCustomersCompleted(sender As Object, e As SqlFilter.NorthwindDataServiceReference.GetCustomersCompletedEventArgs)
    ' hide 'loading' message    _lblLoading.Visibility = Visibility.Collapsed;
    ' monitor CustomerName filter    _customerFilter = _c1OlapPage.OlapEngine.Fields["CustomerName"].Filter;
    _customerFilter.PropertyChanged += filter_PropertyChanged
    ' monitor view definition to ensure CustomerName field is always active    _c1OlapPage.ViewDefinitionChanged += _c1OlapPage_ViewDefinitionChanged;
    ' show available customers in the "CustomerName" field filter    _allCustomers = e.Result;
    _customerFilter.Values = _allCustomers
    ' go get the data    GetData();
End Sub

void sc_GetCustomersCompleted(object sender, SqlFilter.NorthwindDataServiceReference.GetCustomersCompletedEventArgs e)
{
    // hide 'loading' message    _lblLoading.Visibility = Visibility.Collapsed;
    // monitor CustomerName filter    _customerFilter = _c1OlapPage.OlapEngine.Fields["CustomerName"].Filter;
    _customerFilter.PropertyChanged += filter_PropertyChanged;
    // monitor view definition to ensure CustomerName field is always active    _c1OlapPage.ViewDefinitionChanged += _c1OlapPage_ViewDefinitionChanged;
    // show available customers in the "CustomerName" field filter    _allCustomers = e.Result;
    _customerFilter.Values = _allCustomers;
    // go get the data    GetData();
}

This event gets the complete list of customers in the database. We store this to show in the filter. We need to listen to the C1OlapField.PropertyChanged event, which fires when the user modifies any field properties including the filter. When this happens, we retrieve the list of customers selected by the user and pass that list to the data source.

And here is the event handler that updates the data source when the filter changes:

' CustomerName field filter has changed: get new datavoid filter_PropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
If True Then
    GetData()
End If

// CustomerName field filter has changed: get new datavoid filter_PropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
{
    GetData();
}

 The field’s Filter property is only taken into account by the C1OlapEngine if the field in “active” in the view. “Active” means the field is a member of the RowFields, ColumnFields, ValueFields, or FilterFields collections. In this case, the “CustomerName” field has a special filter and should always be active. To ensure this, we must handle the engine’s ViewDefinitionChanged event and make sure the “Customers” field is always active.

Here is the code that ensures the “CustomerName” field is always active:

' make sure Customer field is always in the viewvoid _c1OlapPage_ViewDefinitionChanged(object sender, EventArgs e)
If True Then
    Dim olap = _c1OlapPage.OlapEngine
    Dim field = olap.Fields("CustomerName")
    If Not field.IsActive Then
        olap.FilterFields.Add(field)
    End If
End If

// make sure Customer field is always in the viewvoid _c1OlapPage_ViewDefinitionChanged(object sender, EventArgs e)
{
    var olap = _c1OlapPage.OlapEngine;
    var field = olap.Fields["CustomerName"];
    if (!field.IsActive)
    {
        olap.FilterFields.Add(field);
    }
}

The GetData method is called to get the invoice data for the selected customers in the filter.

' go get invoice data for the selected customersvoid GetData()
If True Then
    ' re-create active customer list based on the current filter settings    var selectedCustomers = new ObservableCollection<string>();
    For Each customer As String In _allCustomers
        If _customerFilter.Apply(customer) Then
            selectedCustomers.Add(customer)
        End If
    Next
    _customerFilter.ShowValues = selectedCustomers.ToArray()
    ' go get invoices for the selected customers    var sc = new SqlFilter.NorthwindDataServiceReference.NorthwindDataServiceClient();
    sc.GetCustomerInvoicesCompleted += sc_GetCustomerInvoicesCompleted
    sc.GetCustomerInvoicesAsync(selectedCustomers)
    ' show status    _lblStatus.Text = string.Format("Retrieving invoices for {0} customers...", selectedCustomers.Count);
End If

// go get invoice data for the selected customersvoid GetData()
{
    // re-create active customer list based on the current filter settings    var selectedCustomers = new ObservableCollection<string>();
    foreach (string customer in _allCustomers)
    {
        if (_customerFilter.Apply(customer))
        {
            selectedCustomers.Add(customer);
        }
    }
    _customerFilter.ShowValues = selectedCustomers.ToArray();
    // go get invoices for the selected customers    var sc = new SqlFilter.NorthwindDataServiceReference.NorthwindDataServiceClient();
    sc.GetCustomerInvoicesCompleted += sc_GetCustomerInvoicesCompleted;
    sc.GetCustomerInvoicesAsync(selectedCustomers);
    // show status    _lblStatus.Text = string.Format("Retrieving invoices for {0} customers...", selectedCustomers.Count);
}

Here we use the C1OlapFilter (_customFilter) and call its Apply method to build a list of customers selected by the user. We make another asynchronous call to our web service which returns the filtered invoice data in the following event:

' got new data: show it on C1OlapPagevoid sc_GetCustomerInvoicesCompleted(object sender, SqlFilter.NorthwindDataServiceReference.GetCustomerInvoicesCompletedEventArgs e)
If True Then
    If e.Cancelled OrElse e.[Error] IsNot Nothing Then
        _lblStatus.Text = String.Format("** Error: {0}", If(e.[Error] IsNot Nothing, e.[Error].Message, "Canceled"))
    Else
        _lblStatus.Text = String.Format("Received {0} invoices ({1} customers).", e.Result.Count, _customerFilter.ShowValues.Length)
        ' begin update        var olap = _c1OlapPage.OlapEngine;
        olap.BeginUpdate()
        ' update data source        _invoices.Clear();
        For Each invoice As var In e.Result
            _invoices.Add(invoice)
            ' finish update        olap.EndUpdate();
        Next
    End If
End If

// got new data: show it on C1OlapPagevoid sc_GetCustomerInvoicesCompleted(object sender, SqlFilter.NorthwindDataServiceReference.GetCustomerInvoicesCompletedEventArgs e)
{
    if (e.Cancelled || e.Error != null)
    {
        _lblStatus.Text = string.Format("** Error: {0}", e.Error != null ? e.Error.Message : "Canceled");
    }
    else
    {
        _lblStatus.Text = string.Format("Received {0} invoices ({1} customers).",
            e.Result.Count,
            _customerFilter.ShowValues.Length);
        // begin update        var olap = _c1OlapPage.OlapEngine;
        olap.BeginUpdate();
        // update data source        _invoices.Clear();
        foreach (var invoice in e.Result)
        {
            _invoices.Add(invoice);
        }
        // finish update        olap.EndUpdate();
    }
}

If you run the application now, you will notice that only the customers included in the “CustomerName” setting are included in the view:

To see other customers, double-click the “CustomerName” field and select “Field Settings” to open its Filter settings.

Then edit the filter by selecting specific customers or by defining a condition. To define a custom filter condition, click “Text Filter” on the bottom of the Field Settings Filter tab, select a condition type (i.e. Equals or Begins with…), then enter your criteria as shown below:

When you click OK, the application will detect the change and will request the additional data from the GetData method. Once the new data has been loaded, C1OlapPage will detect the change and update the OLAP table automatically:

See the included sample “SqlFilter” for the full implementation described in the previous sections. We can extend this sample to also store the OLAP view with filters in local storage. See Persisting OLAP Views in Local Storage.