Relational Modeling and CRUD

I’m experimenting with support for relational modeling for #liveDB based on Generics. Here’s a peek at how this is progressing.

Entities

Entities of type E are stored in an EntitySet<E>, which wraps a SortedSet<E>, a new collection class in .NET 4 implemented as a balanced binary tree with O(log N) read and write operations. Entities need to be orderable within the sorted set, which can be achieved using a key property implementing IComparable or having the entity itself implement IComparable<E>. Here are some example entities with keys defined in three different ways:

 1:  [Serializable]
 2:  public class Category
 3:  {
 4:      public int Id { get; set; }
 5:      public string Name { get; set; }
 6:  }
 7:
 8:  [Serializable]
 9:  public class User
10:  {
11:      [Key]
12:      public string Email { get; set; }
13:  }
14:
15:  [Serializable]
16:  public class Task : IComparable<Task>
17:  {
18:      public Guid Id { get; set; }
19:      public string Name { get; set; }
20:      public DateTime DueBy{ get; set; }
21:      public int CategoryId { get; set; }
22:
23:      public int CompareTo(Task other)
24:      {
25:          return Id.CompareTo(other.Id);
26:      }
27:  }

The Category entity has a property named Id of type IComparable<int> which by naming convention will be used as a key. The User entity has a property marked with KeyAttribute. Task implements IComparable<Task> so Task objects will themselves be used as key and the Id property will be ignored. Task has a foreign key property, CategoryId,  but notice that none of the entities have any outbound object references. An entity can be an aggregation but must be an isolated from other entities meaning no inbound or outbound object references. For example, an Order entity can have a collection of OrderLine objects.

This isolation is important for queries. Imagine an N-to-N relationship between Category and Task above, where Category has a  collection of Tasks and Task has a collection of Categories. A query for a single task could possibly return every Task and Category in the database! This is a common beginners' mistake. Normally, we deal with this issue by returning specific view objects stripped from references. We could for instance have a TaskView class with the categories represented as a list of string.

The Model

Next, create a model and derive from RelationalModel, defining entity sets in the constructor:

 1:  [Serializable]
 2:  public class MyRelationalModel : RelationalModel
 3:  {
 4:      public MyRelationalModel()
 5:      {
 6:          AddSetOf<Category>();
 7:          AddSetOf<User>();
 8:          AddSetOf<Task>();
 9:      }
10:  }

Not much going on here, really.

CRUD Commands and Querying

Now start your engine and you’re all set up for basic CRUD using commands of type AddEntityCommand<E>, RemoveEntityCommand<E>, and ReplaceEntityCommand<E>. Of course, nothing is stopping you from also creating custom commands. EntitySet<E> implements IEnumerable<E> so query as usual using lambdas or Query<M,R> instances.

Here is the AddEntityCommand<E> in action:

 1:  [TestMethod()]
 2:  public void can_add_entity()
 3:  {
 4:      var engine = Engine.LoadOrCreate<MyRelationalModel>();
 5:
 6:      //Create the entity you want to insert
 7:      var category = new Category{ Name = "Beverages"};
 8:
 9:      //Create a generic command
10:      var addCommand = new AddEntityCommand(category);
11:
12:      //A copy is returned with the Id assigned
13:      category = engine.Execute(addCommand);
14:      int numEntities = engine.Execute( db => db.SetOf<Category>().Count());
15:      Assert.IsTrue(numEntities == 1);
16:      engine.Close();
17:  }
18:
19:
20:  [TestMethod()]
21:  public void added_entity_was_assigned_a_key()
22:  {
23:      var engine = Engine.LoadOrCreate<MyRelationalModel>();
24:      var category = new Category { Name = "Beverages" };
25:      var addCommand = new AddEntityCommand(category);
26:      category = engine.Execute(addCommand);
27:      Assert.IsTrue(category.Id > 0);
28:      engine.Close();
29:  }

Conclusion

OK, so this isn’t really relational but it does enable basic CRUD, provided you do your modeling correctly. With CRUD, you don’t have to write specific task-oriented commands and you can return entities from queries without having to map to reference-stripped view objects.