Object Relations with Lex.DB

The next big unanswered question regarding Lex.DB is “How does Lex.DB deal with object relations?”. The short answer – it doesn’t. However, it doesn’t mean showstopper.

Object relations are different. There are many-to-many, one-to-many, one-to-one, nested (on delete cascade) etc. The idea of Lex.DB is to be simple as a nail and fast as a hammer. Implementing extended relation support will bloat the library, increase complexity of code, configuration and understanding.

While some alternative local databases support nested collections of any objects, they serialize all data to a blob (Xml, Binary, Json etc.). When data structures evolve, the guarantee that already stored data is still readable is shallow.

Lex.DB uses table with columns structures internally, so it cannot map arbitrary nested/related objects into those columns. But you as developer can. The developer definitely knows, what kind of relations his objects have, when to load related objects, when to delete. Lex.DB does not cache any data. It provides you the means to serialize/deserialize your data fast, query by indices, load by keys.

If you care about performance, you should load all data your application needs only once. Entity Framework makes me laugh, when I see how it preloads *-to-many relation from database. Same objects with all their content are transferred over the network for every second-end of the *-to-many reference.

Lex.DB simply does not allow this scenario. It just gives you the full control.

All your data objects have primary key, right? If not, consider introducing it. An integer, long or Guid field/property is fine. Lex.DB supports auto generated keys for these types of PK. However, the key is auto generated on save. This is important.

A relation that Lex.DB can serialize/deserialize is just a foreign key or a list/array of foreign keys. This is more than enough to load related objects when you need them. This is enough to pull your foreign keys through your custom cache to avoid loading same objects several times and one by one. The developer knows lifetime of his data objects, knows which objects have to be cached, knows which can be loaded every time. Full freedom!

Here is an example of simple one-to-mane and one-to-one object relations. First the main entity classes without partials:

  public class Customer
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
  }

  public class Product
  {
    public int Id { get; set; }
    public string Name { get; set; }
  }

  public partial class Order
  {
    public int Id { get; set; }
    public int? CustomerFK { get; set; }
    public int[] ProductsFK { get; set; }
  }

The Order class will have reference to Customer and list of references to Products.

Please welcome, partial classes for object relations:

  public partial class Order
  {
    [XmlIgnore] // to avoid DB Serialization
    public Customer Customer
    {
      get
      {
        return CustomerFK == null ? null : _customer ?? (_customer = Database.Customers.LoadByKey(CustomerFK.Value));
      }
      set
      {
        _customer = value;
        SyncCustomerFK();
      }
    }
    Customer _customer;

    void SyncCustomerFK()
    {
      CustomerFK = _customer != null ? (int?)_customer.Id : null;
    }

    [XmlIgnore]  // to avoid DB Serialization
    public Collection<Product> Products { get { return _products ?? (_products = CreateProductsRelation()); } }
    Collection<Product> _products;

    void SyncProductsFK()
    {
      ProductsFK = (from i in _products select i.Id).ToArray();
    }

    Collection<Product> CreateProductsRelation()
    {
      var products = ProductsFK == null ? Enumerable.Empty<Product>() : Database.Products.LoadByKeys(ProductsFK);
      var result = new ObservableCollection<Product>(products);
      result.CollectionChanged += (s, e) => SyncProductsFK();
      return result;
    }
  }

As you may see, now we added loaded on demand live Customer instance and Collection of Products. There properties are invisible to Lex.DB due to [XmlIgnore] attribute.
In Customer property setter, we call SyncCustomerFK to update CustomerFK foreign key reference.
The Products collection is baked by ObservableCollection instance, where we sync Product foreign keys on collection changes.

Here is the Database static class used for loading:

  public static class Database
  {
    public static DbInstance Instance { get { return _db ?? (_db = CreateDb()); } }
    static DbInstance _db;

    public static DbTable<Product> Products { get { return _products ?? (_products = Instance.Table<Product>()); } }
    static DbTable<Product> _products;

    public static DbTable<Order> Orders { get { return _orders ?? (_orders = Instance.Table<Order>()); } }
    static DbTable<Order> _orders;

    public static DbTable<Customer> Customers { get { return _customers ?? (_customers = Instance.Table<Customer>()); } }
    static DbTable<Customer> _customers;

    static DbInstance CreateDb()
    {
      var result = new DbInstance("Relational");
      result.Map<Product>().Automap(i => i.Id, true);
      result.Map<Order>().Automap(i => i.Id, true);
      result.Map<Customer>().Automap(i => i.Id, true);

      result.Initialize();
      return result;
    }

It exposes typed table classes a-la DataContext/ObjectContext.

Here is a source code for this example (SL & WinRT versions).

So we can get/set/data-bind our Order.Customer property like a normal one, CustomerFK will be in sync as long as we don’t assign it directly.

The same valid for Order.Products collection, OnCollectionChanged handler will keep ProductsFK array in sync. And Lex.DB will persist it.