LiveLinq and Declarative Programming

The live views provided by LiveLinq are not restricted to data binding scenarios.

Live views can be used to combine data from multiple tables, group and aggregate this data according to business rules, and make it available to the application at all times. The views are always synchronized with the data, so there’s no need to call methods to update the views when you need the data. This greatly simplifies application logic and improves efficiency.

To illustrate this point, imagine a NorthWind application that exposes the following services:

ProcessOrder: This service bills the customers, ships the products, and updates the information in the database. It is used by the sales force and by the company web store.

SalesInformation: This service returns summaries of sales per product and product category. It is used by management and marketing.

You could implement the application by having the ProcessOrder service write orders directly into the database and having the SalesInformation service run a stored procedure that would return the latest sales summaries. This would work, but the SalesInformation service would be relatively expensive since it would go to the database every time and would have to scan all the orders in the database.

Another approach would be to load the data into live views, where the sales summaries would be kept constantly up to date as orders are processed. Calls to the ProcessOrder method would automatically update the summaries provided by SalesInformation. Calls to SalesInformation would be processed in zero time, without touching the database at all.

To illustrate this, let us create another simple WinForms application using the NorthWind data once again. The application will have three grids. The first corresponds to the ProcessOrder service, allowing users to edit orders. The others correspond to the SalesInformation service, showing sales summaries that are always synchronized with the orders.

Here are the steps:

Create a new WinForms application
Use the Data | Add New DataSource menu and add a reference to the NORTHWND.MDF database. Accept all the default options offered by the wizard, and pick all the tables in the database.
Add a reference to the C1.LiveLinq.dll assembly to the project.
Add three DataGridView controls to the form, with labels above each one as shown in the image below.

Now, right-click the form and enter the following code:

C#	Copy Code
using C1.LiveLinq; using C1.LiveLinq.AdoNet; using C1.LiveLinq.LiveViews; public Form1() { InitializeComponent(); // get the data NORTHWNDDataSet ds = GetData(); // create a live view to update order details this.dataGridView1.DataSource = GetOrderDetails(ds); // create live views that provide up-to-date order information this.dataGridView2.DataSource = GetSalesByCategory(ds); this.dataGridView3.DataSource = GetSalesByProduct(ds); }

Copy Code

using C1.LiveLinq;
using C1.LiveLinq.AdoNet;
using C1.LiveLinq.LiveViews;
 
public Form1()
{
  InitializeComponent();
 
  // get the data
  NORTHWNDDataSet ds = GetData();
 
  // create a live view to update order details
  this.dataGridView1.DataSource = GetOrderDetails(ds);
 
  // create live views that provide up-to-date order information
  this.dataGridView2.DataSource = GetSalesByCategory(ds);
  this.dataGridView3.DataSource = GetSalesByProduct(ds);
}

As before, the first step is loading the relevant data from the database:

C#	Copy Code
NORTHWNDDataSet GetData() { var ds = new NORTHWNDDataSet(); new NORTHWNDDataSetTableAdapters.ProductsTableAdapter() .Fill(ds.Products); new NORTHWNDDataSetTableAdapters.Order_DetailsTableAdapter() .Fill(ds.Order_Details); new NORTHWNDDataSetTableAdapters.CategoriesTableAdapter() .Fill(ds.Categories); return ds; }

Copy Code

NORTHWNDDataSet GetData()
{
  var ds = new NORTHWNDDataSet();
  new NORTHWNDDataSetTableAdapters.ProductsTableAdapter()
    .Fill(ds.Products);
  new NORTHWNDDataSetTableAdapters.Order_DetailsTableAdapter()
    .Fill(ds.Order_Details);
  new NORTHWNDDataSetTableAdapters.CategoriesTableAdapter()
    .Fill(ds.Categories);
  return ds;
}

Next, we use LiveLinq to implement the live view that will be exposed through the SalesInformation service. This is a standard LINQ query, only slightly more sophisticated than the ones we used in earlier samples, with a couple of AsLive clauses that turn the standard query into a live view:

C#	Copy Code
object GetSalesByCategory(NORTHWNDDataSet ds) { var products = ds.Products; var details = ds.Order_Details; var categories = ds.Categories; var salesByCategory = from p in products.AsLive() join c in categories.AsLive() on p.CategoryID equals c.CategoryID join d in details.AsLive() on p.ProductID equals d.ProductID let detail = new { CategoryName = c.CategoryName, SaleAmount = d.UnitPrice * d.Quantity * (decimal)(1f - d.Discount) } group detail by detail.CategoryName into categorySales let total = categorySales.Sum(x => x.SaleAmount) orderby total descending select new { CategoryName = categorySales.Key, TotalSales = total }; return salesByCategory; }

Copy Code

object GetSalesByCategory(NORTHWNDDataSet ds)
{
  var products = ds.Products;
  var details = ds.Order_Details;
  var categories = ds.Categories;
 
  var salesByCategory =
    from p in products.AsLive()
    join c in categories.AsLive()
      on p.CategoryID equals c.CategoryID
    join d in details.AsLive()
      on p.ProductID equals d.ProductID
 
    let detail = new
    {
      CategoryName = c.CategoryName,
      SaleAmount = d.UnitPrice * d.Quantity
        * (decimal)(1f - d.Discount)
    }
 
    group detail
      by detail.CategoryName into categorySales
 
    let total = categorySales.Sum(x => x.SaleAmount)
    orderby total descending
    select new
    {
      CategoryName = categorySales.Key,
      TotalSales = total
    };
 
  return salesByCategory;
}

The query starts by joining the three tables that contain the information on products, categories, and orders. It then creates a temporary detail variable that holds the product category and total amount for each order detail. Finally, the details are ordered by sales totals and grouped by category name.

The result is a live view that is automatically updated when the underlying data changes. You will see this a little later, when we run the app.

The next live view is similar, except it provides sales information by product instead of by category.

C#	Copy Code
object GetSalesByProduct(NORTHWNDDataSet ds) { var products = ds.Products; var details = ds.Order_Details; var categories = ds.Categories; var salesByProduct = from p in products.AsLive() join c in categories.AsLive() on p.CategoryID equals c.CategoryID join d in details.AsLive() on p.ProductID equals d.ProductID into sales let productSales = new { ProductName = p.ProductName, CategoryName = c.CategoryName, TotalSales = sales.Sum( d => d.UnitPrice * d.Quantity * (decimal)(1f - d.Discount)) } orderby productSales.TotalSales descending select productSales; return salesByProduct; }

Copy Code

object GetSalesByProduct(NORTHWNDDataSet ds)
{
  var products = ds.Products;
  var details = ds.Order_Details;
  var categories = ds.Categories;
 
  var salesByProduct =
    from p in products.AsLive()
    join c in categories.AsLive()
      on p.CategoryID equals c.CategoryID
    join d in details.AsLive()
      on p.ProductID equals d.ProductID
    into sales
    let productSales = new
    {
      ProductName = p.ProductName,
      CategoryName = c.CategoryName,
      TotalSales = sales.Sum(
         d => d.UnitPrice * d.Quantity *
              (decimal)(1f - d.Discount))
    }
    orderby productSales.TotalSales descending
    select productSales;
 
  return salesByProduct;
}

The last view shows the order details. We will bind this view to an editable grid so we can simulate orders being created or modified, and how the changes affect the previous views:

C#	Copy Code
object GetOrderDetails(NORTHWNDDataSet ds) { var products = ds.Products; var details = ds.Order_Details; var categories = ds.Categories; var orderDetails = from d in details.AsLive().AsUpdatable() join p in products.AsLive() on d.ProductID equals p.ProductID join c in categories.AsLive() on p.CategoryID equals c.CategoryID select new { c.CategoryName, p.ProductName, d.UnitPrice, d.Quantity, d.Discount }; return orderDetails; }

Copy Code

object GetOrderDetails(NORTHWNDDataSet ds)
{
  var products = ds.Products;
  var details = ds.Order_Details;
  var categories = ds.Categories;
 
  var orderDetails =
    from d in details.AsLive().AsUpdatable()
    join p in products.AsLive()
      on d.ProductID equals p.ProductID
    join c in categories.AsLive()
      on p.CategoryID equals c.CategoryID
    select new
    {
      c.CategoryName,
      p.ProductName,
      d.UnitPrice,
      d.Quantity,
      d.Discount
    };
 
  return orderDetails;
}

If you run the application now, you should see a window like this one:

The application shows the total sales by category and by product, sorted by amount. The best-selling category is “Beverages”, and the best-selling product is “Cote de Blaye”.

Now click the “ProductName” column header on the top grid and scroll down to find the entries for “Cote de Blaye”. Once you’ve found them, try making a few changes to the orders and see how the summary data is immediately updated. For example, if you change the quantities to zero for a few “Cote de Blaye” orders, you will see that “Beverages” quickly falls behind the “Diary Products” category:

This simple example illustrates the power of LINQ-based live views. They bridge the gap between data and logic and can make data-centric applications much simpler and more efficient.