Using IFindRelated to implement navigation properties

A common requirement when accessing data through an ORM is when you find a record, you need to access related records automatically. For example, let's say you find an OrderItem record, you will likely want easy access to its related OrderHeader record and perhaps a related Item record. Perhaps there are child records of OrderItem that you want queried automatically. To accomplish this, implement IFindRelated on your model class. This has one method FindRelated.

Let's build out this OrderItem example. In a nutshell, you add the IFindRelated interface. Since it requires a TKey argument, you use the type that corresponds to your foreign key types. In this case, I'm using int.

public class OrderItem : IFindRelated<int>
{
    // foreign key properties
    public int OrderHeaderId { get; set; }
    public int ItemId { get; set; }

    public decimal Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public int Id { get; set; }

    // navigation properties
    public OrderHeader OrderHeader { get; set; }
    public Item Item { get; set; }
    public IEnumerable<StatusInfo> Statuses { get; set; }

    public void FindRelated(IDbConnection connection, CommandProvider<int> commandProvider)
    {
        OrderHeader = commandProvider.Find<OrderHeader>(connection, OrderHeaderId);
        Item = commandProvider.Find<Item>(connection, ItemId);
        Statuses = new StatusInfoQuery() { OrderItemId = Id }.Execute(connection);
    }
}

Now, whenever I perform a Find, FindWhere, or its async versions, my OrderItem record will have OrderHeader, Item and StatusInfo related records available in scope through their respective properties. Note that the StatusInfo example is based on a hypothetical query object not shown here, but this is the pattern I'd use with the Query object. Please see this wiki topic on using the Postulate Query type.

One thing to note is that foreign keys in the database are created with the [References] attribute. This attribute doesn't enable any navigation property behavior, so that's why my example above doesn't use this.

One thing to be careful of when implementing IFindRelated is that you don't introduce circular Find operations. If you have parent and child record types that attempt to find each other via FindRelated, this will cause an infinite loop.