DEV Community

David Lastrucci
David Lastrucci

Posted on

Relations and lazy loading: modeling real-world data

#delphi #orm #database #opensource

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
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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>;
Enter fullscreen mode Exit fullscreen mode

TDetailColumn takes two parameters:

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

The getter exposes the list:

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

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

The TRelation attribute

On the TTOrder class, notice:

[TRelation('OrderDetails', 'OrderID', True)]
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Deleting with cascade 

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

Important things to remember

  1. 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.

  2. 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.

  3. 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!

Top comments (0)