Creating News Views Based on Existing Views

Multiple queries with parameters can often be replaced with a single “big” live view. That can help making code clear and simple and can often make it faster. This technique is based on two features of live views:

A view can be based on other views in the same way as it can be based on base data.
Indexes can be created for a view in the same way as indexes are created for base data.

So, instead of issuing many queries by varying a parameter value, you can create a single live view, create an index for it, and, when you need the list of items corresponding to a particular value of the parameter, you simply get those items from the index for that particular value.

It is illustrated in the LiveLinqIssueTracker demo:

The Assigned Issues form uses multiple views, a separate view for every employee: views depending on a parameter employeeID (we are giving LiveLinq to DataSet versions of the views here, Objects and XML versions are similar):

C#	Copy Code
from i in _dataSet.Issues.AsLive() join p in _dataSet.Products.AsLive() on i.ProductID equals p.ProductID join f in _dataSet.Features.AsLive() on new { i.ProductID, i.FeatureID } equals new { f.ProductID, f.FeatureID } join e in _dataSet.Employees.AsLive() on i.AssignedTo equals e.EmployeeID where i.AssignedTo == employeeID select new Issue { IssueID = i.IssueID, ProductName = p.ProductName, FeatureName = f.FeatureName, Description = i.Description, AssignedTo = e.FullName };

Copy Code

from i in _dataSet.Issues.AsLive()
join p in _dataSet.Products.AsLive()
    on i.ProductID equals p.ProductID
join f in _dataSet.Features.AsLive()
    on new { i.ProductID, i.FeatureID }
        equals new { f.ProductID, f.FeatureID }
join e in _dataSet.Employees.AsLive()
    on i.AssignedTo equals e.EmployeeID
where i.AssignedTo == employeeID
select new Issue
{
    IssueID = i.IssueID,
    ProductName = p.ProductName,
    FeatureName = f.FeatureName,
    Description = i.Description,
    AssignedTo = e.FullName
};

The demo application also contains an alternative implementation of the same functionality, in the form Assigned Issues 2. That form uses a single view containing data for all employees, instead of multiple views depending on a parameter. This single view does not have parameters:

C#	Copy Code
_bigView = from i in _dataSet.Issues.AsLive() join p in _dataSet.Products.AsLive() on i.ProductID equals p.ProductID join f in _dataSet.Features.AsLive() on new { i.ProductID, i.FeatureID } equals new { f.ProductID, f.FeatureID } join e in _dataSet.Employees.AsLive() on i.AssignedTo equals e.EmployeeID select new Issue { IssueID = i.IssueID, ProductName = p.ProductName, FeatureName = f.FeatureName, Description = i.Description, AssignedToID = e.EmployeeID, AssignedToName = e.FullName };

Copy Code

_bigView =
    from i in _dataSet.Issues.AsLive()
    join p in _dataSet.Products.AsLive()
        on i.ProductID equals p.ProductID
    join f in _dataSet.Features.AsLive()
        on new { i.ProductID, i.FeatureID }
            equals new { f.ProductID, f.FeatureID }
    join e in _dataSet.Employees.AsLive()
        on i.AssignedTo equals e.EmployeeID
    select new Issue
    {
        IssueID = i.IssueID,
        ProductName = p.ProductName,
        FeatureName = f.FeatureName,
        Description = i.Description,
        AssignedToID = e.EmployeeID,
        AssignedToName = e.FullName
    };

That view is indexed by the employee id field:

 _bigView.Indexes.Add(x => x.AssignedToID);

so we can retrieve the items for a particular employee id at any time very fast.

Moreover, we can create a live view of that data given an employee id value (which is comboAssignedTo.SelectedIndex in this demo), simply by creating a view over the big view:

from i in _bigView where i.AssignedToID == comboAssignedTo.SelectedIndex select i;