Relations

• Defining relations
  • Many-To-One
  • One-To-Many
  • One-To-One
• Reading relations
• Prefetching relations
• Reverse relations

Defining relations

Many-To-One

To define a many-to-one relation, create a field or property of the related class and either mark it with the [Relation] attribute or have the related class implement the IEntity interface:

public class Order
{
   //...
   public int CustomerID;

   [Relation] // optional if Customer implements IEntity
   public Customer Customer {get;set;}
}

The default naming convention is for the relation key to be the same name as the primary key of the related class. This can be overridden with properties of the [Relation] attribute. For example, [Relation(LocalKey="CustID")]. LocalKey refers to the field name in the class itself, not the related class.

One-To-Many

To define a one-to-many relation, create a collection (enumerable) field or property of the related class and either mark it with the [Relation] attribute or make the field of type IDataSet<T>:

public class Customer
{
   //....
   [Relation]
   public IEnumerable<Order> Orders {get;set;}
}

// or

public class Customer
{
   //....

   // no attribute required
   public IDataSet<Order> Orders {get;set;}
}

The default naming convention is for the relation key in the related class to be the same name as the primary key of the declaring class. This can be overriden with properties of the [Relation] attribute. For example, [Relation(ForeignKey="CustID")]. ForeignKey refers to the field name in related class.

#####One-To-One

A One-to-One relation is the same as a Many-to-One relation, with the only difference being that the reverse relation's object is automatically set when a one-to-one relation is loaded. A One-to-One relation should be tagged in the class using the attribute [Relation.OneToOne].

Reading relations

Many-To-One and One-To-One relations are never read automatically (except when prefetching. One-To-Many relations are lazily loaded when the collection type is IDataSet<T> or IEnumerable<T>.

There are several ways to load relations:

• context.LoadRelations(relation1, relation2, ...)
• context.Read(key, relation1, relation2, ...);
• dataSet.WithRelations(relation1, relation2, ...)

relation1, etc are lambda expressions referencing the relations to be loaded

For example, reading a record of type Order with the related Customer record:

var order = dbContext.Read<Order>(orderId, o => o.Customer);

// or

var order = dbContext.Read<Order>(orderId);

dbContext.LoadRelations(() => order.Customer);

For one-to-many relations it can be a lot easier if you declare the relation using either IDataSet<T> or IEnumerable<T>. In that case you don't need to load the relation explicitly because the relation will be loaded the first time you try to enumerate the collection:

public class Customer
{
   //....

   // no attribute required
   public IDataSet<Order> Orders {get;set;}
}

// ...

var customer = dbContext.Read(customerId);

// enumerating over customer.Orders will load
// the related records
foreach (var order in customer.Orders)
    /* do something with order */;

In addition, you can also filter the related records:

// retrieve all orders from customer within the last month
foreach (var order in customer.Orders.Where(o => o.OrderDate >= DateTime.Today.AddMonths(-1)))
   /* do something with order */

// note that this query will be translated to proper SQL if the 
// database is a SQL database. The generated SQL will be something like this:
//
//  select * from Order where CustomerID=@custID and OrderDate>=@date

If you don't declare the one-to-many relation as IEnumerable<T> or IDataSet<T>, you will need to manually load the relations when you need them:

public class Customer
{
   //....

   [Relation]
   public Order[] Orders {get;set;}
}

// ...

var customer = dbContext.Read(customerId, c => c.Orders);

foreach (var order in customer.Orders)
    /* do something with order */;

####Prefetching relations

When reading collections of objects, it's possible to load many-to-one relations in one single query. This will improve performance a lot.

For example, loading a list of orders:

foreach (var order in dbContext.Orders)
{
    dbContext.LoadRelations(order, order => order.Customer);

    Console.WriteLine("Order #{0}, Customer {1}", order.OrderID, order.Customer.Name);
}

If you have 100 orders, a total of 101 queries will be done on the database: one to read the order records and one for each order's customer record.

The solution is to specify one or more Many-to-One relations to read with the collection:

foreach (var order in dbContext.Orders.WithRelations(order => order.Customer)
{
    Console.WriteLine("Order #{0}, Customer {1}", order.OrderID, order.Customer.Name);
}

In the example above, only one query will be run on the database. All related customer records are loaded in one database query.

####Reverse relations

When a reverse relation is detected, the corresponding object will automatically be set to the parent object (for One-To-Many and One-To-One relations).

For example, given the following relations:

public class Order
{
    public int OrderID;

    [Relation] public IDataSet<OrderItem> OrderItems;
}

public class OrderItem
{
    public int OrderItemID;
    public int OrderID;

    [Relation] public Order Order;
}

When the One-To-Many relation OrderItems is read, the Order objects in the OrderItem objects is set to the parent Order object. It will reference the same object in memory:

var order = db.Read<Order>(orderId);

foreach (var orderItem in order.OrderItems)
{
    // orderItem.Order == order
}