Relations and lazy loading: modeling real-world data

Real applications rarely have isolated tables. Orders belong to customers. Products belong to brands. Order details reference both an order and a product. In this article we will model these relationships in Trysil using TTLazy<T> and TTLazyList<T>.

The data model

Let's build a classic order management scenario:

Customer  1──N  Order  1──N  OrderDetail  N──1  Product

A Customer has many Orders
An Order has many OrderDetails
An OrderDetail references one Product

Many-to-one: TTLazy<T>

When an entity has a foreign key pointing to another entity, use TTLazy<T>:

unit Order.Model;

{$WARN UNKNOWN_CUSTOM_ATTRIBUTE ERROR}

interface

uses
  System.SysUtils,
  Trysil.Types,
  Trysil.Attributes,
  Trysil.Validation.Attributes,
  Trysil.Lazy,
  Trysil.Generics.Collections;

type
  [TTable('Customers')]
  [TSequence('CustomersID')]
  TTCustomer = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TRequired]
    [TMaxLength(100)]
    [TColumn('CompanyName')]
    FCompanyName: String;

    [TMaxLength(100)]
    [TColumn('City')]
    FCity: String;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;
  public
    function ToString: String; override;

    property ID: TTPrimaryKey read FID;
    property CompanyName: String read FCompanyName write FCompanyName;
    property City: String read FCity write FCity;
  end;

Now the TTOrder entity, which references a TTCustomer:

  [TTable('Orders')]
  [TSequence('OrdersID')]
  [TRelation('OrderDetails', 'OrderID', True)]
  TTOrder = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TRequired]
    [TColumn('OrderDate')]
    FOrderDate: TDateTime;

    [TRequired]
    [TDisplayName('Customer')]
    [TColumn('CustomerID')]
    FCustomer: TTLazy<TTCustomer>;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;

    [TDetailColumn('ID', 'OrderID')]
    FDetails: TTLazyList<TTOrderDetail>;

    function GetCustomer: TTCustomer;
    procedure SetCustomer(const AValue: TTCustomer);
    function GetDetails: TTList<TTOrderDetail>;
  public
    property ID: TTPrimaryKey read FID;
    property OrderDate: TDateTime read FOrderDate write FOrderDate;
    property Customer: TTCustomer read GetCustomer write SetCustomer;
    property Details: TTList<TTOrderDetail> read GetDetails;
  end;

The key points:

FCustomer: TTLazy<TTCustomer> — the field type is TTLazy<T>, not TTCustomer. The [TColumn('CustomerID')] attribute maps it to the foreign key column.
Getter/setter — you expose the related entity through a property backed by methods that access FCustomer.Entity:

function TTOrder.GetCustomer: TTCustomer;
begin
  result := FCustomer.Entity;
end;

procedure TTOrder.SetCustomer(const AValue: TTCustomer);
begin
  FCustomer.Entity := AValue;
end;

When you read Order.Customer for the first time, Trysil executes a SELECT to load the customer. On subsequent accesses, the cached reference is returned. This is lazy loading — the related entity is fetched only when you actually need it.

One-to-many: TTLazyList<T>

To load the child collection (order details), use TTLazyList<T> with [TDetailColumn]:

[TDetailColumn('ID', 'OrderID')]
FDetails: TTLazyList<TTOrderDetail>;

TDetailColumn takes two parameters:

The parent's primary key column ('ID')
The foreign key column in the child table ('OrderID')

The getter exposes the list:

function TTOrder.GetDetails: TTList<TTOrderDetail>;
begin
  result := FDetails.List;
end;

Like TTLazy<T>, the list is loaded on first access.

The TRelation attribute

On the TTOrder class, notice:

[TRelation('OrderDetails', 'OrderID', True)]

This declares that OrderDetails is a child table linked via the OrderID foreign key. The third parameter (True) enables cascade delete — when you delete an order, Trysil automatically deletes its order details first.

If you set it to False, Trysil will check for existing child records before deleting. If any exist, it raises an exception to prevent orphaned data.

The child entity

  [TTable('OrderDetails')]
  [TSequence('OrderDetailsID')]
  TTOrderDetail = class
  strict private
    [TPrimaryKey]
    [TColumn('ID')]
    FID: TTPrimaryKey;

    [TColumn('OrderID')]
    FOrderID: TTPrimaryKey;

    [TRequired]
    [TDisplayName('Product')]
    [TColumn('ProductID')]
    FProduct: TTLazy<TTProduct>;

    [TRequired]
    [TMaxLength(100)]
    [TColumn('Description')]
    FDescription: String;

    [TGreater(0.00)]
    [TColumn('Quantity')]
    FQuantity: Double;

    [TMinValue(0.00)]
    [TColumn('Price')]
    FPrice: Double;

    [TColumn('Delivered')]
    FDelivered: TTNullable<TDateTime>;

    [TColumn('VersionID')]
    [TVersionColumn]
    FVersionID: TTVersion;

    function GetProduct: TTProduct;
    procedure SetProduct(const AValue: TTProduct);
  public
    property ID: TTPrimaryKey read FID;
    property OrderID: TTPrimaryKey read FOrderID write FOrderID;
    property Product: TTProduct read GetProduct write SetProduct;
    property Description: String read FDescription write FDescription;
    property Quantity: Double read FQuantity write FQuantity;
    property Price: Double read FPrice write FPrice;
    property Delivered: TTNullable<TDateTime>
      read FDelivered write FDelivered;
  end;

Note the FOrderID field — it stores the foreign key value and is typed as TTPrimaryKey. This is the link back to the parent order.

Using relations in practice

Loading an order with its details

var
  LOrder: TTOrder;
  LDetail: TTOrderDetail;
begin
  LOrder := LContext.Get<TTOrder>(1);

  WriteLn(Format('Order #%d — %s', [
    LOrder.ID,
    LOrder.Customer.CompanyName]));  // triggers lazy load of customer

  for LDetail in LOrder.Details do   // triggers lazy load of details
    WriteLn(Format('  %s x%.0f @ %.2f', [
      LDetail.Description,
      LDetail.Quantity,
      LDetail.Price]));
end;

Creating an order with details

var
  LOrder: TTOrder;
  LDetail: TTOrderDetail;
  LCustomer: TTCustomer;
  LProduct: TTProduct;
begin
  LCustomer := LContext.Get<TTCustomer>(1);
  LProduct := LContext.Get<TTProduct>(5);

  LOrder := LContext.CreateEntity<TTOrder>();
  LOrder.OrderDate := Now;
  LOrder.Customer := LCustomer;
  LContext.Insert<TTOrder>(LOrder);

  LDetail := LContext.CreateEntity<TTOrderDetail>();
  LDetail.OrderID := LOrder.ID;
  LDetail.Product := LProduct;
  LDetail.Description := LProduct.Description;
  LDetail.Quantity := 10;
  LDetail.Price := LProduct.Price;
  LContext.Insert<TTOrderDetail>(LDetail);
end;

Deleting with cascade

// Because TTOrder has [TRelation('OrderDetails', 'OrderID', True)],
// this deletes the order AND all its details:
LContext.Delete<TTOrder>(LOrder);

Important things to remember

The context must stay alive while you access lazy-loaded fields. If you free the context and then access Order.Customer, you will get an access violation.
Beware the N+1 problem. If you load 100 orders and access .Customer on each one, that is 100 additional SELECT queries. For bulk operations, consider loading customers separately and matching them in memory.
Lazy fields use the identity map. If the same customer is referenced by multiple orders, only one instance is loaded (when the identity map is enabled).

What is next

We have modeled a real-world domain with parent-child relationships and lazy loading. In the next article we will look at change tracking and soft delete — how Trysil can automatically record who changed what, and how to delete records without actually removing them.

Trysil is open-source and available on GitHub. If this series is helping you, a star on the repo would be much appreciated!

Relations and lazy loading: modeling real-world data

The data model

Many-to-one: TTLazy<T>

One-to-many: TTLazyList<T>

The TRelation attribute

The child entity

Using relations in practice

Loading an order with its details

Creating an order with details

Deleting with cascade

Important things to remember

What is next

Tags

Author

Stats

Published

You Might Also Like

Filtering, sorting, and pagination with the fluent query builder

Change tracking and soft delete: audit trails without the boilerplate

Declarative validation: keep bad data out of your database