C1Olap can consume any collection as a data source. It is not restricted to DataTable objects. In particular, it can be used with LINQ.

LINQ provides an easy-to-use, efficient, flexible model for querying data. It makes it easy for developers to create sophisticated queries on client applications without requiring modifications to the databases such as the creation of new stored procedures. These queries can in turn be used as data sources for C1Olap so end users also have the ability to create their own views of the data.

To illustrate this, create a new project and add a C1OlapPage control to the form. Instead of setting the DataSource property in the designer and using a stored procedure like we did before, this time we will load the data using a LINQ query. To do this, add the following code to the form constructor:

public Form1()
{
  // designer
  InitializeComponent();
 
  // load all interesting tables into a DataSet
  var ds = new DataSet();
  foreach (string table in
    "Products,Categories,Employees," +
    "Customers,Orders,Order Details".Split(','))
  {
    string sql = string.Format("select * from [{0}]", table);
    var da = new OleDbDataAdapter(sql, GetConnectionString());
    da.Fill(ds, table);
  }
 
  // build LINQ query and use it as a data source
  // for the C1OlapPage control
  // …
}
// get standard c1nwind.mdb connection string
static string GetConnectionString()
{
  string path = 
    Environment.GetFolderPath(Environment.SpecialFolder.Personal) + 
    @"\ComponentOne Samples\Common";
  string conn = @"provider=microsoft.jet.oledb.4.0;" +
    @"data source={0}\c1nwind.mdb;";
  return string.Format(conn, path);
}

The code loads several tables from the NorthWind database. It assumes the NorthWind database is available in the "ComponentOne Samples" folder, which is where the C1Olap setup places it. If you have the database in a different location, you will have to adjust the GetConnectionString method as appropriate.

Next, let’s add the actual LINQ query. This is a long but simple statement:

// build LINQ query
var q =
  from detail in ds.Tables["Order Details"].AsEnumerable()
  join product in ds.Tables["Products"].AsEnumerable()
    on detail.Field<int>("ProductID")
      equals product.Field<int>("ProductID")
  join category in ds.Tables["Categories"].AsEnumerable()
    on product.Field<int>("CategoryID")
      equals category.Field<int>("CategoryID")
  join order in ds.Tables["Orders"].AsEnumerable()
    on detail.Field<int>("OrderID")
      equals order.Field<int>("OrderID")
  join customer in ds.Tables["Customers"].AsEnumerable()
    on order.Field<string>("CustomerID") 
      equals customer.Field<string>("CustomerID")
  join employee in ds.Tables["Employees"].AsEnumerable()
    on order.Field<int>("EmployeeID")
      equals employee.Field<int>("EmployeeID")
  select new
  {
    Sales = (detail.Field<short>("Quantity") *
      (double)detail.Field<decimal>("UnitPrice")) * 
      (1 - (double)detail.Field<float>("Discount")),
    OrderDate = order.Field<DateTime>("OrderDate"),
    Product = product.Field<string>("ProductName"),
    Customer = customer.Field<string>("CompanyName"),
    Country = customer.Field<string>("Country"),
    Employee = employee.Field<string>("FirstName") + " " +
      employee.Field<string>("LastName"),
    Category = category.Field<string>("CategoryName")
  };
 
// use LINQ query as DataSource for the C1OlapPage control
c1OlapPage1.DataSource = q.ToList();

The LINQ query is divided into two parts. The first part uses several join statements to connect the tables we loaded from the database. Each table is connected to the query by joining its primary key to a field that is already available on the query. We start with the "Order Details" table, and then join "Products" using the "ProductID" field, and then "Categories" using the "CategoryID" field, and so on.

Once all the tables are joined, a select new statement is used to build a new anonymous class containing the fields we are interested in. Notice that the fields may map directly to fields in the tables, or they may be calculated. The "Sales" field for example is calculated based on quantity, unit price, and discount.

Once the LINQ query is ready, it is converted to a list using LINQ’s ToList method, and the result is assigned to the DataSource property. The ToList method is required because it causes the query to be executed. If you simply assign the query to any control’s DataSource property, you will get a syntax error.

If you run the project now, you will see that it looks and behaves just like before, when we used a stored procedure as a data source. The advantage of using LINQ is that the query is built into the application. You can change it easily without having to ask the database administrator for help.