Automated Foreign Key Cached Dictionary Generation in SubSonic 3 : Part 2

In part one, we tackled the problem of what Foreign Key (FK) relationships in a database could look like when translated into the object world.
An initial, not terribly successful attempt to provide these methods was undertaken.

At this stage, several design points became obvious:

• there was a need to cache the entire set of objects
• this would best be done using a static list / dictionary
• the least confusing way to provide the FK lookups would be for each cached table to provide lookup dictionaries to itself, indexed by FK column value (rather than one table storing data about another)
• we might want also to look up any indexed column values by index as well as those specifically with a FK
• it would be great to have the option to cache only some tables/objects and not others, and have the object seamlessly detect and deal with this - retrieve cached values if present, or query the database otherwise

Cached Lists

The cached lists were declared in a normal class, which could later be declared as the static member of an application.
This is a sample of the objects for the Order table.

public class DataCache {
    public List<Order> Order_BaseList = null;
    public Dictionary<int, Order> Order_By_OrderID = null;    
    public Dictionary<string, List<Order>> Order_GroupBy_CustomerID = null;
    public Dictionary<int, List<Order>> Order_GroupBy_EmployeeID = null;
    public Dictionary<int, List<Order>> Order_GroupBy_ShipVia = null;

    public DataCache () {
        Order_BaseList = Order.All().ToList();
        foreach (IHasDataCache hdc in Order_BaseList ) { hdc.CachedData = this; } 
        Order_By_OrderID = Order.OrderID_CreateLookupList(Order_BaseList);  
        Order_GroupBy_CustomerID = Order.CustomerID_CreateFkChildList (Order_BaseList);
        Order_GroupBy_EmployeeID = Order.EmployeeID_CreateFkChildList (Order_BaseList);
        Order_GroupBy_ShipVia = Order.ShipVia_CreateFkChildList (Order_BaseList);
    }
}

The 'Base' list could be populated using LINQ from the database.

The 'X_By_XID' dictionary Order_By_OrderID, serves to retreive an Order object given an OrderID.
Any database column having a unique index (clearly including a single column PK) gets a list like this.

The 'X_GroupBy_YID' dictionaries hold lists of Order objects corresponding to a particular FK column value. For example, Order_GroupBy_EmployeeID[employeeId] contains a List<Order> of all the orders associated with that particular employeeID.

You can also see the initialisation code from the constructor. The static methods being called were designed only to be used in the initial creation of the dictionaries from the base list.

Don't worry about the second line, the 'foreach', yet.

It may not be clear yet, but all the functionality we created in Part 1 can easily be derived from these lists. As a bonus, we get this bunch of handy lookup lists to use for any other purpose we please.

Sharing the Cached Data Between Objects

In order to make the cached objects able to access other cached objects, the objects needed to contain a reference to the cache.
This was done by creating an IHasDataCache interface and modifying all data access objects to implement that interface:

public interface IHasDataCache {
    DataCache CachedData { get; set; }
}

public partial class Order: IActiveRecord, IHasDataCache {
    ...

    private DataCache _dataCache = null;

    [SubSonicIgnore]
    public DataCache CachedData {
        get { return _dataCache; }
        set { _dataCache = value; }
    }

    ...
}

public partial class Product: IActiveRecord, IHasDataCache {
    ...
etc

and now we can see the reason for the 'foreach' line back in the DataCache class constructor, remember:

foreach (IHasDataCache hdc in Order_BaseList ) { hdc.CachedData = this; } 

That line loops through each of the just-loaded cached objects and sets a reference to the parent DataCache object. Now the table classes could access the other related classes as they needed to.

Instantiating the Cache

I usually implement a static class 'Gbl' where I put anything that's global to my application, such as cached data ...

public static class Gbl {
    public static DataCache NorthwindDataCache = new DataCache();
    ...
}

The Access Methods

It's time to unveil the actual methods used to access the FK related objects. The naming conventions are a lot less cryptic. Any single FK object is accessed by a method starting with 'FkParent_...', and any list of FK objects is access by a method starting with 'FkList...'.
Here's the methods for the Product class:

List<Order_Detail> orderDetail = p.FkList_Order_Detail_ProductID;
List<Product_Category_Map> pcm = p.FkList_Product_Category_Map_ProductID;
Category category = p.FkParent_CategoryID;
Supplier supplier = FkParent_SupplierID;

Under the Hood

The beauty of this actually resides in the caching system, and the implementation of the above methods gives insight into how this system actually works:

public List<Order_Detail> FkList_Order_Detail_ProductID {
    get {
        if (_FkList_Order_Detail_ProductID == null) {
            if (_dataCache != null && _dataCache.Order_Detail_GroupBy_ProductID!=null) {
                if (!_dataCache.Order_Detail_GroupBy_ProductID.TryGetValue(_ProductID, out _FkList_Order_Detail_ProductID)) {
                    // deal with the case where there are no related records and hence no list
                    _FkList_Order_Detail_ProductID = new List<Order_Detail>();
                }
            } else {
                _FkList_Order_Detail_ProductID = (from items in Order_Detail.All()
                    where items.ProductID == _ProductID
                    select items).ToList();
            }
        }
        return _FkList_Order_Detail_ProductID;
    }
}
 
public Category FkParent_CategoryID {
    get {
       if (_FkParent_CategoryID == null) { 
           if (_dataCache != null && _dataCache.Category_By_CategoryID!=null) {
               _FkParent_CategoryID = _dataCache.Category_By_CategoryID[this.CategoryID]; 
           } else {
               _FkParent_CategoryID = Category.SingleOrDefault(x => x.CategoryID == this.CategoryID);
           }
       }
       return _FkParent_CategoryID;
    }
}

So it goes something like this:

• lazy loading means the FK object or list is only fetched once
• if the object was created in the DataCache class and was populated using the code in the constructor, then it contains a valid reference to the DataCache object
• a valid DataCache reference is used to load the wanted information from the cached data dictionaries, if present
• if no cache is present, the data is loaded from the database via LINQ

This system is pretty flexible. It allows ad-hoc mixing of cached and non cached objects in code, supporting optimally efficient access to all cached objects, but database fetches where that's not desired.

Not only that, but we can use LINQ to query the cached objects without generating a database call.

As mentioned in Part 1, the caching took the run-time of the first program I used this with (where all the tables needed to be cached, but I initially just cached the main ones and LINQ queried the rest) from 3.5 minutes to sub-second - literally finishing before the mouse button had moved back up from the click to start the program.

Here are some snippets:

foreach (Supplier supp in Gbl.NorthwindDataCache.Supplier_BaseList) {
    foreach Product product in supp.FkList_Product_SupplierID) {
        ...
    }
}

if (product.FkParent_SupplierID.Country != "Australia") { overseasSupplier = true; }

tablesAlphaOrder = Gbl.NorthwindDataCache.Product_BaseList
    .Where(x => x.SupplierId > 0 && x.CategoryID != 25)
    .OrderBy(x => x.ProductName).ToList();

The Code

So where can you get your hands on this little beauty ?
The best place is probably in my SubSonic templates branch here.
You only need the T4 Templates.