CodexMicroORM - Sample Data Model and Advanced Mapping



“My goal is to show how differences in these models (data and object) can be handled by CEF in very simple ways.”

Welcome to the first article I’ve created for CodexMicroORM (aka Codex Entity Framework or CEF)! Instead of writing long, boring reference documentation, you’ll find articles that cover relevant topics in a way that offers practical examples and more “why” than you’d find in most reference docs. The fact the main articles page supports full-text searching through all articles is an effective way to find what you’re after. At the same time, there’s a lot of existing detail found on the project’s GitHub page that will stand as background, with articles building on specific narrow topics. For this first article, I’ll spend some time going over the sample data and object models that the project includes for both testing and demonstration. My goal is to show how differences in these models can be handled by CEF in very simple ways.

The Data Model

Let’s start by looking at the database storage model used when CEF was initially released. It was very simple: just 3 tables!

The most interesting element here is a self-reference on Person, to indicate a person’s parent. This isn’t always handled well (or simply) by all ORM frameworks, so for initial development I felt this would uncover some importance nuances – and it did. CEF understands how to support inserts, updates and deletes performed in the proper order, no matter how deep you nest the object graph.

Starting in 0.2.4, new tables were introduced to the sample database, in the WTest schema:

These new tables offer more scenarios to address when mapping to any chosen object model. For example, this is the object model I used to “map” to these tables:

Of note:

-          Not all names are “exact matches” to the database model. For example, “CurrentStatus” is a property name on the Widget class, where this maps to a database column called “CurrentStatusID” on the Widget table. We can strive to keep all names in sync, but sometimes it’s clearer to use freedom in your object model – and there are plenty of other reasons we could see some level of divergence in naming. To support this example, one line of set-up code is used in our demo/test app:


DBService.RegisterStorageFieldName<Widget>(nameof(Widget.CurrentStatus), "CurrentStatusID");

                Similarly there can be a difference between table name and class name. For example, the WidgetGroupItem table is represented by the GroupItem class, which we can inform CEF about using one line of code on initialization:


-          We can support enumerations for lookup values. WidgetStatusValues is an enumeration sourced from table WidgetStatus, which when we generate it from the contents of the lookup table, lets us get the benefit of using a descriptive value in .NET code, plus descriptive labels when doing SQL queries.


-          CEF supports compound keys. WidgetReview for example has both SKU (a string) and Username (another string) as its primary key. We can support this using a registration statement:

KeyService.RegisterKey<WidgetReview>("SKU", nameof(WidgetReview.Username));

Why did we use “nameof” for Username, not SKU? The mapped class named WidgetReview exposes Username as a string, but the SKU is called “RatingFor” and is a WidgetType reference. In this case, WidgetType’s primary key is “SKU” and it appears on WidgetReview as a foreign key. CEF models foreign keys using registration statements such as this one:


                As you can see, we can identify the relationship and the property name mapped to, which in this case is in the object model, not the database (RatingFor).

-          In the object model, we’re using the Address class as a group of related properties that can be reused over multiple classes. For example, Receipt has a “FromAddress” which can be a reference to a specific Address instance. In the database, this maps to “FromAddressLine” and “FromCity”. We can tell CEF exactly how this works using:

DBService.RegisterPropertyGroup<Receipt, Address>(nameof(Receipt.FromAddress), "From");

                This uses “From” as the prefix for “AddressLine” and “City” which are the properties of the Address class, when mapped to the Receipt table using the “FromAddress” reference.

-          The WidgetList as a base class supports a “GroupNumber” and a list called “Widgets” containing zero, one or many Widgets. Receipt and Shipment both inherit from WidgetList and implement some customizations. What this means in a practical sense when trying to save the object model is we need to save to two database tables, not one for a Receipt or Shipment. Also, WidgetGroupItem is a join table that includes TrackingNumber as “extra payload.” To save from one class to two tables, we instruct CEF using:


                This will save to WidgetGroup first, then Receipt, mapping properties to fields per normal rules for each table. The mapping for the items collection uses a basic foreign key:



With this release we’ve introduced plenty of tools to let your object model diverge from your data model – and we’ll provide a few more related features in the future. Everything covered here (and more) can be seen exercised in the test project, in this method:


        public void NewWidgetNewReceiptNewShipment()


What’s Next

There’s a lot of ground to catch up on: look for articles covering serialization, caching, creating your own services (really explaining the service architecture), use cases around connection and service scopes, and more. As for the next code release, expect to see 0.5 in March sometime, where we’ll have added support for a new extension: an object-oriented database that can let you not only not use SQL Server, but not use any relational database! What we’re delivering is a HybridSQL (as opposed to NoSQL) solution that when combined with CEF lets you use SQL for some data sources (e.g. reference data) and the object-oriented database for others (e.g. transactional data). It’s a lofty goal that may get rolled out over a few releases. I promise, it’ll be interesting, so sign up for blog updates!

- @codexguy

Did you like this article? Please rate it!

Back to Article List