One of the databases I use on a regular bases has a rather interesting column naming scheme; all columns have a prefix, based on the table name. For example, the table containing people would have the prefix PEO_, so you would have this:
I believe the idea was so that when querying, you would not have any column name clashes. This of course breaks down if you have to join on the same table twice.
This structure presents a problem when it comes to reading the tables into objects in code, as it removes the ability to use an orm - I have yet to see one which allows you to specify a prefix to be used on all columns in a table.
The existing entities are all manually read, and follow the same pattern:
Note how columns are read in order, which means two things: you cannot use select * as your query, and you cannot change column order etc.
To help split this so we can start using an ORM to do the mapping for us, we can utilise the Memento Pattern. First we create a new object, which will be used to read and write from the database:
Note the property names match the column names of the table in the db, our read method could then get changed to this:
This gives us several benefits, in that we can change column naming and ordering freely without effecting the actual Person object, and we have made the class slightly more testable - we can pass it a faked PersonDto if we needed to load it with some data for a test.
We can however make another improvement to this - namely in the Read method, as this is a prime candidate for AutoMapper. To get this to work though, have two choices: the first is to manually specify the mappings of one object to the other, and the second is to write a profile which will do the work for us. Unsurprisingly, I went with the second option:
This class takes in a dictionary of types (in this case will be things like Person => PersonDto). It goes through each pair in the list and determines the prefix for the destination class (the dto). The GetPrefixFromProperty is virtual so that I can customise it for other uses later.
To use this we just need to initialise AutoMapper with the class once on start up:
This means our Person class becomes very small:
And the Entity class can take care of the mapping for us, but utilising AutoMapper's Type based Map method:
While the design of having each entity responsible for saving and loading of itself is not the best design, it is what the existing system has in place (around 400 entities exist at last count). By taking these steps we can remove a lot of boilerplate code from our codebase, which means when we wish to change to a different architecture (such as session or transaction objects in a similar style to RavenDB's ISession), it will be an easier transition.