Kerosene ORM: a dynamic, self-adaptive and configuration-less ORM with no need for configuration files

• Download KeroseneORM-5.5.zip - 290 KB

Introduction

Kerosene ORM is an ORM library that has been built to adapt dynamically to any schema your database may have without having to write any external mapping of configuration files. It provides you with full support to your POCO business objects without having to modify them in any way, not even having to add any attribute. And it let you write a more natural SQL-like syntax from your C# source code.

Kerosene uses two modes of operation. The first one, named "standard", is basically a dynamic wrapper around whatever schema is produced from the database, and it is suited for those scenarios where you have no business class able to receive those contents, or for intensive data processing scenarios. The second one, named "Maps", is basically a dynamic Entity Framework that does not require any configuration: it is able to "map" the contents retrieve from the database to your business classes without the need of any changes or decorations on those classes.

This article is the introductory one. Its aim is to provide a quick overview of Kerosene and its capabilities. The following accompanying articles provide you with more detailed information about the specific functionalities each of them cover:

• Kerosene ORM Fundamentals in Depth [LINK PENDING].
• Kerosene Maps: Your Entity Framework for POCO classes [LINK PENDING].
• Kerosene for WCF: Supporting WCF remote scenarios [LINK PENDING].
• Kerosene ORM Internals: How to Extended Kerosene (work in progress).

Version notes

This article describes version 5.5 of the Kerosene ORM library. It takes to its limits the concept of being a dynamic ORM, so some methods that appeared in previous versions are now deprecated. Also it incorporates a new rebuilt "Maps" engine that is easier to use and configure than what was introduced in previous versions.

Motivation

If you are reading this article you most probably are, like me, a heavy ORM user. You like the level of abstraction they provide. You like that they are supposed to let you focus on the business problem you want to solve instead of losing your time with the myriad of details you otherwise would have to take into consideration.

But you maybe are not, like I was, completely comfortable with all the time and efforts you need to devote to master most ORM solutions, nor with all the constraints do they typically impose on the way you think and develop. This is why, back in 2008, I started to develop the Kerosene ORM library to solve, among others, the following annoyances:

• They typically tend to be very complex environments hard to understand and difficult to use. I did not want to trade time devoted to deal with the database for time devoted to master yet another tool. I did want to free my time and use it to focus on solving what I had at my hands.
• Abounding on this complexity topic, they typically require you write and maintain a number of external mapping and configuration files, with a specific range of different tools and languages… just in order to start being productive. Even the simplest solution easily becomes hard to maintain even if you are using automatic code generation tools (which, by the way, have their own drawbacks).
• Many of them require you to modify your business classes with ORM related stuff. Even if those modifications are in the form or adding attributes they break, in my modest opinion, the principle of separation of concerns. And what is worse, often you do not even have access to their source code.
• You have very little control on the actual code they generate. So either you end executing very fat code against your database… or you suddenly find yourself writing embedded SQL code in text. I am not completely against it, but such activity is prone to errors and in any case means that you are abandoning the level of abstraction you were supposed to be using.
• And, finally, for me the most important one: the hidden assumption that you have a stable and controlled environment, all the way from your database schema, through those mapping files, up to your production code. The fact is that if anyone in your organization changes the tiniest piece then you have to recreate them all and pray no changes will break your solution.

Kerosene Basics

Let’s start with a quick example. Suppose that you are asked to obtain a list of the employees whose last name starts with 'D' or bigger. The DBA dropped a post-it at your desk telling you that there is a table named 'Employees' who has a column named 'LastName', in a MS SQL Server 2012 database.

Well, open your compiler because what follows, believe it or not, is all the code you need to write:

var link = new KLinkDirectSQLServer2012( "...your connection string..." );
var cmd = link.From( x => x.Employees ).Where( x => x.LastName >= "D" );
foreach( var obj in cmd ) Console.WriteLine( "\nRecord = {0}", obj );

Yes! You have not had to write any external mapping of configuration files. Indeed, you were not required to have any detailed knowledge of the database’s schema (apart from the name of the table and column). You have not had to modify any business class or write any wrappers (actually, you have just enumerated the records produced from your query). And, incredible enough, you have written a comparison among two string-alike objects in a way that the C# compiler was not supposed to allow. Finally, of course, you did not worry about creating, opening, closing and disposing any connections against your database.

Let me reinforce some of these points:

• Kerosene captures on the fly the schema of the results you are interested in. This is why you have not had to write or maintain any external mapping or configuration files. It creates dynamic records that adapt themselves automatically to this schema. We can access and manipulate their contents in any way, transforming them to our POCO instances without having to decorate their classes with any database related attribute.
• Kerosene is a resilient solution: as far as just the names of the tables and columns you have used remain the same you don’t have worry if anyone has changed anything in the database. What’s more, Kerosene makes no differences between tables and columns.
• One of the main topics behind Kerosene is to decrease the mismatch between SQL code and C# one. It heavily uses dynamic lambda expressions to specify what you want to achieve with your database. The parsing engine that is invoked by the late-binding mechanism of those expressions is what allowed you to write the above 'x => x.LastName >= "D"' comparison.
• But Kerosene does not impose you any compromises: if you want to use your favorite advance feature of your database you will always be able to write it down either in the form of virtual C# code, or even on the form of plain text if you like this way more.

Some Complex Scenarios

Still interested? Fantastic, because Kerosene allows you to address any complex scenarios you may have to solve. For instance, let’s suppose we are dealing with a (minimalist) HR system composed by three tables with the following schema:

• It happens that, in this scenario, all the three tables contain a key column named 'Id' – but this happened completely by chance: Kerosene does not require you to use any magic names nor it does use them in any way. Neither configuration nor convention to follow needed.
• What’s more, in this so-called “standard” mode of operation Kerosene does not even require your database to have a primary key. In this mode it just produces dynamic “records” that you can later manipulate in any way you wish. By contrast, we will see later that it does also provide a “mapping” mode of operation, specifically design to deal with your business entities instead of on records – in this mode either a primary key or a unique valued column (or columns) is needed to univocally identity those entities.
• Now, to make things a bit more interesting each employee can be associated to a manager through its 'ManagerId' column, if it is not null, and to a given region through its 'CountryId' column, which in this case must not be null. Each country must be associated with a given region through its 'RegionId'. And, finally, each region can be associated with a super-region through its 'RegionId' column if it is not null.

Querying Multiple Tables

Let’s now suppose that you are asked to produce a list containing the id, first and last names of the non-active employees, along with the id and name of the country they belong to, and ordered by the country id. One way to obtain this (*) is with the following code:

var cmd = link
   .From( x => x.Employees.As( x.Emp ) )
   .From( x => x.Countries.As( x.Ctry ) )
   .Where( x => x.Active == false || x.Active == null )
   .Where( x => x.Ctry.Id == x.Emp.CountryId )
   .Select( x => x.Emp.Id, x => x.Emp.FirstName, x => x.LastName )
   .Select( x => x.Ctry.Id, x => x.Ctry.Name )
   .OrderBy( x => x.Ctry.Id );

Console.WriteLine( "\nCommand = {0}", cmd.TraceString() );

(*) Disclaimer: I know your seasoned SQL instincts were screaming for using a 'Join' but, for this example, I tried to show how to query several tables simultaneously: I am going to focus on showing Kerosene’s capabilities, instead of writing the most effective SQL command.

Some other ORMs would not have allowed you to query from several tables simultaneously. While it makes no differences if you are dealing with your business classes or entities, this fact may impede you to use these solutions in intensive data processing scenarios. Other important things to mention about this example are:

• The 'Link' is an object whose mission is to maintain a 'generic' connection context against your database. By generic I mean that it can be a direct connection (using a connection string) or, for instance, a connection with a remote service Please see later the example for WCF scenarios.
• We have instantiated a 'Query/Select' command using a convenience extension method of the link object. We will see later examples of the other Insert, Update, Delete and Raw commands.
• We have chained several 'From', 'Where' and 'Select' methods together: you can use as many as you want. Kerosene's parsing engine just annotate their contents and, only when the time comes to execute the command, those contents are concatenated in the proper way to produce the actual SQL command.
• Except very few and obvious exceptions, all Kerosene’s methods take one parameter being a dynamic lambda expression with the signature 'Func<dynamic, object>'. This signature permits you to write whatever expression you need to write because its generic argument is a dynamic one. For instance, this feature permits you to include "virtual extension methods", as the 'As( alias )' one we have used to specify the tables’ aliases.

Once you have written your command you may want to obtain the real SQL code the parsing engine will generate. Every command object has the 'TraceString()' extension method that returns that string, along with the parameters extracted from the expressions you wrote. Using it you will obtain the following result:

SELECT Emp.Id, Emp.FirstName, LastName, Ctry.Id, Ctry.Name FROM Employees AS Emp, 
  Countries AS Ctry WHERE ((Active = @p0) OR (Active IS NULL)) AND 
  (Ctry.Id = Emp.CountryId) ORDER BY Ctry.Id ASC -- [@p0='False']

This method takes an optional command to generate the enumerable or the non-enumerable version of the command as needed (it makes no differences for query commands, though). You can also use the command’s 'CommandText( iterable )' method, that produces the same string but without appending to it the parameters used, and where the Boolean parameter is mandatory.

Using Joins

Yes, obviously, you can also use 'Joins'. So to solve the above problem you could have written the next code instead:

var cmd = link
   .From( x => x.Employees.As( x.Emp ) )
   .Join( x => x.Countries.As( x.Ctry ).On( x.Emp.CountryId == x.Ctry.Id ) )
   .Select( x => x.Emp.Id, x => x.Emp.FirstName, x => x.LastName )
   .Select( x => x.Ctry.Id, x => x.Ctry.Name )
   .OrderBy( x => x.Emp.Id );

I have chained two 'Select()' methods because it pleases my personal taste to separate the columns coming from each table, but there is absolutely no need to do so: you prefer to combine them all in a single statement... or, if you rather prefer, you can use one method per each column! Anyhow, the above C# will produce the SQL one you can expect:

SELECT Emp.Id, Emp.FirstName, LastName, Ctry.Id, Ctry.Name FROM Employees 
  AS Emp JOIN Countries AS Ctry ON (Emp.CountryId = Ctry.Id) ORDER BY Emp.Id ASC

Again, you can use as many 'Join()' methods as you need and Kerosene will take care to produce the proper SQL code.

Now, what if you need to use other 'Join' variants? For instance, let’s suppose you want to use a 'LEFT JOIN' statement. No problems, just follow your instincts, prepend your table with a 'Left' part as follows:

... Join( x => x.Left().Countries.As( x.Ctry ).On( … ) );

Insert, Update and Delete

At this moment I am sure you can expect how these operations will work: instantiate a command using a convenience extension method, write their contents using dynamic lambda expressions, and execute them.

Let’s start by inserting a new record:

var cmd = link.Insert( x => x.Employees ).Columns(
   x => x.Id = "007",
   x => x.FirstName = "James",
   x => x.LastName = "Bond",
   x => x.CountryId = "uk"
);
Console.WriteLine( "\n> Command = {0}", cmd );
var obj = cmd.First();

• It is as easy as just specifying table and the columns affected. The table is given as the parameter of the 'Insert()' extension method. The column (or columns) you want to insert are given to the 'Columns()' method in the form of a variable list of dynamic lambda expressions - each specifying the name of the column and the value you want to insert. You can use just one 'Columns()' method or as many as it pleases you.
• In this example we have executed the command using the 'First()' extension method, that just returns the first record produced by the command's executions - in this case the record we have inserted. We could have used instead its 'Execute()' extension method that returns instead the number of records affected.

Let’s now modify that record:

var cmd = link.Update( x => x.Employees )
   .Where( x => x.Id == "007" )
   .Columns( x => x.LastName = x.LastName + "_Updated" );

• The main difference is that in this case we need to filter the records to update using a 'Where()' method because, otherwise, you will update all the records in that table. You can then enumerate the results (potentially many records can be updated this way) or just execute it to obtain the number of records affected.
• By the way, nothing impedes you to use any valid SQL code to specify the contents to update. For instance, see how we have appended a string to the previous last name of the employee in the example above.

Let’s finally delete that record:

var cmd = link.Delete( x => x.Employees.Where( x => x.Id == "007" );
int n = cmd.Execute();
Console.WriteLine( "\n> Records = {0}", n );

• In this case we just have to specify the filter using the 'Where()' method (you don’t want to delete all the records in the table, do you?).
• Let me reinforce this: you can use any filter you want so you may end deleting (or updating) not just one record but many (or all of them if you have not used any filter).
• Finally, note also that in this case, as an example, we have not enumerated it but rather executed it, and returned the number of records affected.

Raw Commands and Stored Procedures

If you rather prefer to write your own SQL code in text don’t fear: Kerosene provides you with a convenient way to do so. This functionality is useful to, for instance, use stored procedures.

Let’s suppose you have one named 'employee_insert' that, as its name implies, inserts a new employee. You can use it as follows:

var cmd = link.
   Raw( "EXEC employee_insert @FirstName = {0}, @LastName = {1}", "James", "Bond" );

You just write your SQL code and specify the placeholders for the parameters the same way as you specify them when formatting or printing a string: you then include their values as the arguments of the method, as you have expected. You can enumerate this new 'Raw' command or just execute it depending upon its nature.

Working with Entities' Converters

What we have obtained so far, when we have enumerated our commands, are instances of the dynamic 'KRecord' class. These objects adapt themselves to the schema of the records produced from the database, and they let you manipulate their contents in a very natural and handy way.

Let’s first take a look at how to convert them to an anonymous object:

var cmd = link.From( x => x.Employees.As( x.Emp ) ).Where( x => x.LastName >= "C" );
foreach( var obj in cmd.ConvertBy( rec => {
   dynamic r = rec;
   return new {
      r.Id, Name = string.Format( "{0} {1}",
      rec["FirstName"], r["Emp", "LastName"] ),
      r.Emp.CountryId
   };
} ) ) Console.WriteLine( "\nConverted = {0}", obj );

The basic idea is to specify a converter that will be invoked each iteration. It is a delegate that will take, as its parameter, the 'KRecord' instance that, for this iteration, was returned by the database. This delegate can manipulate this record in any way the delegate wants, and whatever it returns will be the result of the current iteration.

As these records are dynamic objects you can use either the 'x.Column' or 'x.Table.Column' syntaxes to access the contents (instead of the table name you can also use its alias, as we have done in the example).

Using C# dynamics has a small performance penalty. You can also use the indexed property syntax shown above if you prefer this approach or if you don’t to pay the price of accessing the contents in a dynamic way.

Obviously, you can also return instances of your business classes instead of just anonymous objects if you want so.

For instance, if you have an 'Employee' class you could have done something like what follows:

Func converter = rec => {
   dynamic r = rec;
   Employee emp = new Employee();
   emp.Id = r.Id; emp.FirstName = (string)rec["FirstName"];
   emp.LastName = (string)rec["Emp", "LastName"];
   emp.CountryId = r.Employees.CountryId;
};
foreach( var obj in cmd.ConvertBy( converter ) )
   Console.WriteLine( "\n> Converted => {0}", obj );

In this example I have chosen to, instead of defining the converter on-line, write it and assign it to a variable that I later used as the parameter of the 'ConvertBy()' method. Both forms are completely equivalent so you can choose the one you like it more.

Nested Readers

As it was mentioned before, within a converter you can do any operation you may need. Yes, you can also access again the database if you need so – you just need to make sure that your database is configured to support multiple active results sets simultaneously.

For instance, suppose that you have a 'Country' class that has a 'List <Employees>' property, and taht you want to load this property while you are querying the database. The following code will do this job:

var cmdCtry = link.From( x => x.Countries.As( x.Ctry ) );
foreach( Country ctry in cmdCtry.ConvertBy( recCtry => {
   dynamic dinCtry = recCtry;
   Country objCtry = new Country();
   objCtry.Id = dinCtry.Id;
   objCtry.Name = dinCtry.Name;

   var cmdEmp = link.From( x => x.Employees ).Where( x => x.CountryId == objCtry.Id );
   foreach( Employee emp in cmdEmp.ConvertBy( recEmp => {
      dynamic dinEmp = recEmp;
      Employee objEmp = new Employee();
      objEmp.Id = dinEmp.Id;
      objEmp.FirstName = dinEmp.FirstName;
      objEmp.LastName = dinEmp.LastName;
      objEmp.CountryId = dinEmp.CountryId;
      objCtry.Employees.Add( objEmp );
      return objEmp;
   } ) )
   Console.WriteLine( "\n\t\t> Employee => {0}", emp );
   return objCtry;
} ) )
Console.WriteLine( "\n> Country => {0}", ctry );

In this example we have an outer loop and an inner one. We are using the employee record generated in the inner command to load the 'Employees' property of each country instance generated in the outer one. Of course you can do anything you wish, with your classes or invoking any other operation against your database. Just make sure you support multiple active results sets simultaneously.

Entities and Maps

The examples we have seen so far belong to the so-called “standard” mode of operation of Kerosene. In this mode, it merely works with the dynamic record instances returned – and remember you can "convert" them to your business entities if you want to. This mode can be understood, if you wish, as a dynamic database access wrapper, and it is very well adapted to intensive data processing scenarios.

But when you need to work with your business classes and entities, Kerosene provides you with a very simple yet powerful mechanism called "Maps". In its easiest form it takes care of everything, converting the records to your business objects, so you just have to write something like what follows:

var map = new KMap( link, x => x.Employees );

var cmd = link.Query();
foreach( var emp in cmd ) Console.WriteLine( "\nEmployee = {0}", emp );

The first line just registers a map for the 'Employees' class with your link. You need to specify the "primary" table where to find the records for these entities (it is named "primary" because you can extend the base mechanism in many useful ways). You just have to do this step once per class and link. As soon as it is registered you can just start to operate against the database returning 'Employee' instances as expected.

In this example we have instantiated a "mapped" query command, which is the equivalent to the "standard" query command we have seen earlier. We can, obviously, use its extension methods, as the 'Where()' one, if we need them for our purposes.

This "Maps" mechanism does not require you to modify your business classes in any way, not even with attributes. If you need to address more advanced scenarios, for instance to deal with dependent properties, the accompanying article mentioned above shows you how this mechanism can be extended.

There is one caveat, though. If you want to use the Insert, Update and Delete equivalent commands there must be a way for Kerosene to identify the specific entity in the database. It means that either the table has a primary key column, or a column tagged as having unique values per each row. But the nice thing is that you don’t have to know or specify which one is this column (or columns) as Kerosene will find out automatically the ones it needs to use.

You can insert a new entity into the database creating first your entity and then using the 'Insert()' extension method:

var emp = new Employee {
   Id = "007",
   FirstName = "James",
   LastName = "Bond",
   CountryId = "uk"
};
emp = link.Insert( emp ).Execute();
Console.WriteLine( "\nRecord = {0}", emp );

• Please do not forget to execute the command. The reason of this two-step approach is that, in this way, you can obtain the actual SQL text the command will execute for, for instance, logging purposes. The 'Execute()' method will always return the entity instance, if succeeded, or null if some error happened.

Updating your entity is, as you can expect, as easy as using the 'Update()' extension method:

emp.LastName += "_Updated";
emp = link.Update( emp ).Execute();

And, I am sure you can guess it now, to delete it you will use:

emp = emp.Delete( emp ).Execute();

What else?

In this introductory article we have barely scratched the surface of what Kerosene is able to do. For instance:

• The Maps mechanism can be customized in many ways to adapt it to your specific needs, hiding columns from your users, or producing properties that do not match with any column in the database. Or you can customize how the Insert, Delete and Update operations behave to take into consideration linked properties among different classes.
• It does also provide a mechanism to deal with transactions. Unlike other ORMs, Kerosene will not, ever, create a transaction automatically (I believe this is something it must be kept under the programmer's realm). But it is as powerful and complete as you can expect.
• Kerosene's syntax is designed to be extensible: you can modify how it parses the dynamic lambda expressions in several ways, and you can even inject your own text using the "escape sequence" feature. If you need to include a database’s specific statement in your code, don’t fear, it will be as easy as what you have done so far.
• Of course Kerosene does not only support MS SQL Server databases. With the library are included specific link classes for the 2008 and 2012 versions. They can be used as a template for your own implementation, of to guide you to write your own link for your favorite database.
• Kerosene does not limit itself to use direct databases (those you access to by virtue of a connection string). For instance the library includes a link version adapted for WCF scenarios that can be used by a client to access a WCF server that hides all the details of the real underlying database.

... and much more. Please see the references to the specific articles that appear at the beginning of this document to get a deeper understanding of each of these features, how to address more complex scenarios, and how to customize Kerosene to meet your very specific needs.

Future Plans

Version 5.5 is a mature and stable release and, at this moment, there are no plans to incorporate to it more generic features (but I will gladly receive any feedback). What I do have in my personal backlog are the following advanced ones:

• Build a version to support MySQL. The generic classes do a decent job, but I agree that it is not the full thing (... anyhow I’m not sure when I will find time to do so).
• Build a version to support Azure. This is something I definitely have to face soon.
• Build a version to support JSON remote scenarios, elaborating on the topic of remote scenarios (again, this is something I’ll have to face more sooner than later).
• Because its dynamic nature it looks like natural to build a JavaScript interface. It might be very useful in a number of scenarios. Indeed, if I find time enough, I might be tempted to provide a full ASP.NET MVC example...

If anyone wants to lead any of the above points (or another one from your personal preferences) I will gladly provide support.

References

Apart from the rest of the articles of this series, there are other pieces of infrastructure that are used by Kerosene:

• DynamicParser: Describes how to use C# dynamics to convert a dynamic lambda expression, in the form of a delegate, into an expression tree. This functionality is at the core of the Kerosene's parsing engine. It can be found here: DynamicParser.
• DeepObject: A serializable dynamic multi-level object that is used to pass agnostic connection packages to a WCF server service without knowing in advance what information will be used. It is just a small but useful piece of infrastructure. It can be found here: DeepObject.
• The way that Kerosene manages your business entities, without requiring any modifications in the source code of their classes, is by dynamically attaching to them, real-time, a package of metadata. The actual mechanism used is very specific to Kerosene but its fundamentals are similar to those described in my article “C# Easy Extension Properties”, that can be found here C# Easy Extension Properties.

History

• [v5.5, May 2013]: This version has two main topics. The first one is to simplify the command’s structure, so the number of overloads of their specific methods is dramatically decreased. Each one has now a self-adaptive logic that permits them to adapt to many scenarios. The second one is a new Maps extensibility architecture. It is now much simpler and powerful, and does not require the number of helper classes that the previous version had to maintain.
• [v5, September 2012]: Kerosene ORM is the fifth version of this project. Its main topics are: a cleaner solution’s architecture, better performance, and an improved dynamic parsing mechanism. It includes a first version of the dynamic entities framework known as Maps. Note that I changed its previous name (I had to, I was tired to explain it has nothing to do with the popular games platform).
• [v4.5, May 2011]: This was a maintenance version that allowed Kynetic ORM to use any arbitrary type for the parameters of a command, with a transformer mechanism that converts them to instances of objects understandable by the ADO engine. It avoided the need of converting them to strings.
• [v4, January 2011]: This fourth public version added support for serialization and WCF scenarios, an improved support for transactions, and corrected some minor bugs.
• [v3, October 2010]: Kynetic ORM was the third public version of this project. It was focused on an improved parsing mechanism, and on performance.
• [v2, August 2010]: MetaDB was the second public version of this project. Its focus was to include some improvements (in particular around the CUD operations) and to resolve some bugs.
• [v1, June 2010]: MetaQuery was the first public version of this project. It focus was basically to send queries against a MS-SQL database, with primitive Insert, Delete and Update operations. It was, basically, a dynamic wrapper on top of ADO.NET.
• [v0, 2008-2009]: Non-public initial versions.