(Hierarchical) Object Relational Mapping

Object Relational Mapping, and especially the hierarchical variety, has continued to be a tricky and code-messy issue with software development. WYSE finally puts a halt to those malpractices and the grievances resulting from them. WYSE was conceptualized and built to promote best coding practices. Easy. Lean. Clean. Maintainable. You are in control.

On this page you can learn about the powerful and versatile ways conversion of results coming back from the database can be handled. Here is what you can expect...

Let's dive in...

Database Results Conversion

This is used for mapping database results into lists of items with simple type properties; no hierarchies. To facilitate this in a strong, type-safe manner WYSE combines its easy and smart way of dealing with aliases with type specific helper conversion methods. These helper methods are DbDataReader extension methods.

Broadly speaking this type of conversion returns either a single object or a list of objects. Given a data reader that is the result of a DbCommand execution, here are some examples of that: 

var customerTable = new CustomerTable();
// Here the code would be where customerTable is used in a Select that is executed
Customer customer = reader.GetItem<Customer>(customerTable.Convert);
// CustomerTable here implements a data reader convert for a Customer object
customer = reader.GetItem<CustomerTable, Customer>();
customer = reader.GetItemOrDefault<Customer>(customerTable.Convert);
customer = await reader.GetItemAsync<Customer>(customerTable.Convert);
IAsyncEnumerable<Customer> customers = reader.GetItemsAsync<Customer>(customerTable.Convert);
customers = reader.GetItemsAsync<CustomerTable, Customer>(cancellationToken);

It is good to note that because command execution is done the regular .NET way (for all the specific database types), working with canceling in async scenarios is completely standard.

And also, because of the uncoupling of building statements on the one hand and working with readers on the other... even though oftentimes Select statements are used to get data from the database, other ways are possible. In the case of SQL Server for instance it is also possible to use Stored Procedures.

Hierarchical Database Results Conversion

The retrieval of non-hierarchical information, as discussed before, is of course a legit use case. In real code-life however objects oftentimes have object properties based on information retrieved from the database as well, who in turn might... It is this were WYSE far outclasses other frameworks. Given a Select statement, ENTIRE (LIST OF) OBJECT HIERARCHIES can be instantiated after a SINGLE database roundtrip, massively improving both EFFICIENCY and application SIMPLICITY. Have a look at this demo example and this.

For cases like these WYSE supports very elegant, lean and powerful hierarchical object relational mapping that is again strong and type-safe. With this, converting the root of an object hierarchy is done as with regular flat cases. But when going deeper WYSE works with mappings.

Here are some examples of DbDataReader extensions working with mappings: 

var customerTable = new CustomerTable();
// Here the code would be where customerTable is used in a Select that is executed
Customer customer = reader.GetItemTree<Customer>(select, customerTable.Convert, mappings);
customer = reader.GetItemTree<Customer>(select, customerTable.Convert, genericMappings);
// CustomerTable here implements a data reader convert for customer
customer = reader.GetItemTree<CustomerTable, Customer>(select, mappings);
customer = await reader.GetItemAsync<Customer>(customerTable.Convert);
IAsyncEnumerable<Customer> customers = reader.GetItemTreesAsync<Customer>(
    select,
    customerTable.Convert,
    mappings);
customers = reader.GetItemTreesAsync<CustomerTable, Customer>(
    select,
    genericMappings,
    cancellationToken);

In the examples above you can see there are three ways in which mappings can be supplied.

These methods (that can be used in conjunction with each other) will be explained in the next paragraphs.

Manual Mapping

Please consider the following code demonstrating how to gather mappings manually: 

var mappings = new List<Mapping>();
var customerTable = new CustomerTable();
var homeAddressTable = new AddressTable();
var billingAddressTable = new AddressTable();
var purchaseTable = new PurchaseTable();
Select select = new Select()
    .Columns(customerTable, homeAddressTable, billingAddressTable)
    .From(customerTable)
    .InnerJoin(homeAddressTable, customerTable.HomeAddressId == homeAddressTable.Id)
        .Map<AddressTable, Customer, Address>(mappings, customer => customer.HomeAddress)
    .LeftJoin(billingAddressTable, customerTable.BillingAddressId == billingAddressTable.Id)
        .Map<Customer, Address>(mappings, billingAddressTable, customer => customer.BillingAddress)
    .InnerJoin(purchaseTable, customerTable.Id == purchaseTable.CustomerId)
        .Map<Customer, Purchase>(mappings, purchaseTable, customer => customer.Purchases);

The benefit of the approach shown above is that you keep the joins and the mappings together. It is not a case where you configure a mapping in some far away Json file. This way the code stays readable and is less error prone. Also lean, because you only have the mappings you need and no more. Also, it is not just with tables or views that you can use mappings. Pairing a mapping with a join based on a Select statement is possible as well.

After you have built your select statement and gathered your mappings it is time to use them. That can be done like in this demo example and in this demo example and in this demo example.

Lastly, here is a subselect example.

Mapping-Savvy Joins

The 'savvy joins' approach eliminates the need to gather mappings manually. Another advantage is that it has a cleaner look. Please take a look at the following code to get the general idea: 

var customerTable = new CustomerTable();
var homeAddressTable = new AddressTable();
var billingAddressTable = new AddressTable();
var purchaseTable = new PurchaseTable();
Select select = new Select()
    .Columns(customerTable, homeAddressTable, billingAddressTable)
    .From(customerTable)
    .InnerJoin<Customer, Address>(
        homeAddressTable,
        customerTable.HomeAddressId == homeAddressTable.Id,
        customer => customer.HomeAddress)
    .LeftJoin<Customer, Purchase>(
        purchaseTable,
        customerTable.Id == purchaseTable.CustomerId,
        customer => customer.Purchases);

In this case WYSE is able to harvest the mappings from the Select statement, as demonstrated in this demo example and in this demo example and in this demo example.

Generic Mapping

The non-generic mapping methods have the advantage of keeping the Join and the Mapping pertaining to it neatly together, making the code easy to follow. The disadvantage is that when you have a lot of queries you have to repeat that. In such situations it can be better to work with generic mappings. Generic mappings can be coded centrally, in a repository for example, solving the 'repeat' problem.

With Generic Mappings the mapping information is stored separate from a Join. Only later, right before conversion of the result coming back from the database, are the actual mappings determined. WYSE does this by matching the Select's top level joins with the generic mappings, creating a specific mapping like that with non-generic mappings.

As for how to use generic mappings in the bigger picture. Please check out this demo example.

To get an idea of how manual mappings and generic mappings could work together you can have a look at this mixed demo example.

Building on generic mappings and combining it with inspecting its strongly typed Select statement enables WYSE to do advanced things, unmatched by other frameworks. Not only is it possible to easily have an entire object hierarchy instantiated in one go. It is even possible to have WYSE GENERATE THE EFFICIENT SELECT STATEMENT needed for that as well. And as always; no magic. You keep total control. Here is a demonstration, and here.

And again, like with manual mappings, it works not just with tables and views, but with joining (sub)Select statements too.

Object Instantiation Using Reflection

Reflection-based object instantiation assumes aliases of columns in selects to have corresponding domain object properties with regard to name and data type. This way of mapping has the advantage that there is no extra code needed.

The drawback is that you get less compiler, parser and other Integrated Development Environment support. That makes it more error-prone and, although powerful, should be used with care.

As for when this reflection-based approach can be used; all GetItem(s), GetItemTree(s) methods have their reflection-counterpart.

Working With Scripts

It is possible in WYSE to write entire SQL strongly typed scripts. Using WYSE data reader extension methods the result sets coming back from the database can be investigated; reader results can be recognized and located based on the Select statements used in the script. This way, even when you there was branching used in the script, and not all Select statements have results, you can easily find what you need.

Here is an example of working with scripts.

PS The Statement that is typically a Select statement. However, in the case of SqlServer for example the same applies to the result of a Stored Procedure.

Single Values

For the specific cases where you know in advance that a select statement will yield a single value WYSE has two dedicated data reader extension methods:

Leveraging Inheritance In Database Tables

Inheritance is usually associated with programming languages, whereas SQL databases are relational in nature. There are ways of implementing inheritance in database tables however. WYSE offers support for this database strategy, making it easy and clear.

Since the best way to explain something is usually to show it: This is an example of how to generally do it in WYSE and here is a more concise, inheritance-specialized version of it.

Property Entanglement

Consider the following case: A Purchase table and a Delivery table associated with it. A Purchase object would have a Delivery property. But it is also possible for the Delivery object to have a reference back to the original purchase in the form of a Purchase property. The two properties in those objects are what is in WYSE called 'entangled'.

This is how you do it: With mappings you set not just the primary object property but also a second one that references back, entangling them.

Here you can see entanglement in demo-action.