Click here to Skip to main content
Click here to Skip to main content

Kerosene ORM: a dynamic, self-adaptive and configuration-less ORM

, 19 Mar 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
The sixth incarnation of a dynamic and self-adaptive ORM library, with full support for POCO objects, that doesn't require any mapping and configuration files, and providing support for natural SQL-like syntax from plain C#


If you are reading this article you most probably are, like me, a heavy ORM user. You like the level of abstraction ORM solutions promise to deliver so that you can, ideally, focus on solving your business problems only, instead of losing your time with the myriad of details that, otherwise, you would have to take into consideration when dealing with the integration between your application and your database.

But, maybe, your are not completely comfortable with how most ORM solutions deliver that promise. Maybe you dislike all the time and efforts you need to devote just to start understanding them - not to mention that mastering these solutions is, typically, an obscure and arcane science even assuming that you can find proper and organized documentation. Or, maybe, you feel limited by all the constraints they typically impose in the way you think, develop, and architect your applications.


You are not alone. Indeed I felt the same when, back in 2008, I started to develop Kerosene ORM to solve, among others, the following annoyances:

  • As mentioned, these ORM solutions typically tend to be hard to understand and difficult to use. Really, I do not want to trade time devoted to solve my business problems for time devoted to master yet another tool, or worse, a set of related tools.
  • They typically tend to be very complex environments. For instance, they typically require you to write and maintain a number of external mapping and configuration files just in order to start-up your application. What's worse, the set of tools they require you to use are not really well integrated into your development flow: you would need to shift your thinking from your main development languages into many others, maybe using different editors (less and less nowadays with modern IDEs) and, even if you are using automatic code-generation tools... well, they definitely have their own drawbacks.
  • They also typically require you, as soon as you want to go a bit beyond the most simple examples, to pollute your business classes with ORM related stuff. Even if these modifications are in the form of adding attributes they break, in my modest opinion, the principle of separation of concerns - this is not supporting POCO classes. And not to mention that, in many cases, they require your business entity classes to derive from an ORM specific one, which is something I cannot really stand with. Sometimes you have no access to the source code of the business classes you are using, or you are not allowed to modify them (for whatever reasons).
  • What's even worse is that, as soon as you use that modifications, then your application becomes tightly tied to that specific ORM solution, and changing it in the future or updating it becomes a maintenance nightmare.
  • You have very little control on the underlying database code they generate and use. This really drove me nuts because either you accept executing very fat code against your database, or suddenly you find yourself writing SQL code in text embedded in your code. I am not completely against doing so (sometimes you have no other option) but it is an error prone activity, with security risks and, in any case, it means you are abandoning the level of abstraction you were supposed to achieve by using an ORM solution.
  • And finally 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. But the fact is that, unless you live in a cave and develop your applications in complete isolation, you are part of a team with more people involved, developing code, maintaining your enterprise databases, creating deployment procedures, security restrictions, etc. And as soon as anyone changes the tiniest piece (and assuming you were lucky enough and you got notice of that...) then you will have to recreate probably many parts of your applications, and pray no changes done by anyone break your solution.

So, What is Kerosene ORM?

Kerosene ORM is a library that has been built to solve this problems, and much more. It dynamically adapts itself to any schema your databases may have, without you having to write any mapping of configuration files. It provides no-compromises full support for your POCO business classes, without having to modify them in any way, not even with any attributes, or to derive them from any given external class. It allows you to express the database operations you want to perform using a natural SQL-alike syntax from your C# source code. And it is inherently resilient, so in most cases it will continue to work even if someone changes the schema of your databases (yes, I do love this!).

Kerosene ORM is architected to support any database supported by ADO.NET. It comes out-of-the-box with adapters for generic SQL Server, Oracle, ODBC and OLE DB databases. It does also come with a supporting library containing customized adapters for SQL Server versions 2008 and 2012, which can be use as a template to develop your own adapter for your specific databases as you wish.

Kerosene ORM is currently being used in some data intensive applications on top of SQL Server and Azure databases, both in heavy client and web environments. I have also got notice of some deployments using Oracle and MySQL ones.

What does this article contains?

This article is just an introduction to the 6.5 version of the Kerosene ORM library, providing a high-level overview of its characteristics and capabilities. The following accompanying articles provide more detailed information about the specific topics they cover:

  • Kerosene ORM Dynamic Records In Depth: a deep dive into the "Dynamic Records" mode of Kerosene ORM. This is the default operational mode of the library, adapted to deal with any arbitrary results your queries may produce from the database, or with customized CUD operations. This mode is also suited for scenarios where you have not a specific business class to represent these results, or for intensive data processing applications. The analogous of this mode would be Linq to SQL, for instance.
  • Kerosene ORM Entity Maps In Depth: a complete tutorial and deep dive into the Kerosene ORM "Entity Maps", where the library maps on your behalf your business classes against the tables you wish in your databases. It also covers how to customize the default mapping mechanism so that you can easily achieve very advanced scenarios with just a few lines of code. This mode is analogous to Entity Framework, or nHibernate, for instance.
  • Kerosene ORM for WCF: an accompanying library that allows a server-side service to provide database capabilities using WCF. It is not about REST or OData, it is about providing full-fledged database services without providing a connection against the database engine itself. With this solution the client-side application connects to the service to consume the database services as if it were connected with a regular database engine... but not even having any knowledge or visibility on what would be the physical database the server connects to behind the scenes. Quite useful when, for instance, the real database engine must remain protected behind a corporate curtain, for security or compliance reasons, but at the same time your heavy client applications must use those database capabilities.
  • Kerosene Dynamic Data Services: an agnostic and dynamic implementation of the Repository and the Unit of Work patterns. This additional library is well suited for n-Tier scenarios where your Front-End tier must remain independent from whatever underlying database or persisting technology you are using (yes, it does not even depend on the Kerosene ORM core libraries - hence why the "ORM" word is not used). Of course, it does also contain a specific implementation on top of the Kerosene ORM maps mechanism for your convenience.

Kerosene ORM Dynamic Records

The default operational mode of Kerosene ORM is known as the "Dynamic Records" one. It is architected to dynamically adapt itself to whatever kind of results your queries produce, without having to write any mapping files or specific classes, letting you focus on your business problems instead of on the details of the underlying database or ORM technology.

To use it you just need to drop the library elsewhere in your solution and start enjoying its easy and dynamic database capabilities.

Let's prove all of this.

A first example

Let's suppose someone in the HR department went nuts and was in a rush to obtain a list with the names of the employees whose last names starts with 'D' or greater. Why? Well, no clue, but these guys are the ones that pay the payroll ... and they have asked you to help! Obviously, it is Friday, late afternoon, and there is no one available to provide any explanations about the structure of the HR databases, or even what database engine they use.

Panic; your Friday night plans are under siege. But, suddenly, you find that old mail where there was a connection string, and also some side mentions to a table named 'Employees' that contains a column named 'LastName'.

Is this enough? Well, yes, open your compiler because when using Kerosene ORM all you needed to do is to write the following:

var cnstr = "... our connection string here ...";
var link = KLinkDirectFactory.Create(cnstr);

var cmd = link.From(x => x.Employees).Where(x => x.LastName >= "D");
foreach(var rec in cmd)
   Console.WriteLine("\n> Record = {0}", rec);

Fantastic! Barely a couple of lines and it worked as a champ! You just then changed the output to a file, mailed it to that HR guy, and packed everything to head home and enjoy a well deserved weekend.

While you were in your way home you suddenly realized that:

  • You have not had to write any mapping or configuration files. Actually, you were only required to have the bare minimum knowledge about your database environment: in the example, just the name of the table and column you were interested about.
  • You have not had to write any receiving business class or to write any wrappers inheriting from who knows what ORM specific classes. You just needed to write you command and to enumerate it in order to obtain a set of records as its result.
  • Incredible enough, you were able to write your query from your C# code using a SQL-alike syntax, in such a dynamic way that it allowed you to write a direct comparison between two string-alike objects, something that the C# compiler was not even supposed to support or allow.
  • And, of course, you did not worry about creating and closing any connections against the database.

Ain't it nice?

Some explanations

What does actually happen with the above code? Let me point out the following concepts and elements:

  • Kerosene ORM uses a 'link' object to represent a connection, or data context, against an underlying database. Because we have used a connection string we are in a "direct connection" scenario, in which the easiest way to obtain that link object is by using the 'KLinkDirectFactory.Create()' factory method.
  • Behind the scenes Kerosene ORM uses an 'engine' object to represent the type, vendor, and characteristics of the underlying database engine. If the argument of the 'Create()' method is the name of a connection string entry in your configuration file, then Kerosene ORM gets the 'provider' information from that entry, and then it locates the appropriate engine adapter from the registered ones. If it is just a raw connection string then you can provide an optional argument with the engine adapter to use.
  • Finally you could have used that method without any arguments (that, by the way, is the easiest way of using the factory), and Kerosene ORM would have looked for the 'KeroseneORM.ConnectionStringEntry' selector entry in the 'appSettings' section of your configuration files, using the value it founds in this entry as the name of the actual connection string entry to use. This way to don't have to hard-code any connection string entry name in your code.
  • You will typically write your queries and other commands using methods that will take a 'dynamic lambda expression' (or DLEs) as their argument. In the general case, DLEs are defined as lambda expressions where at least one of its arguments is a dynamic one. Note that in Kerosene ORM these arguments always have the 'Func<dynamic, object>' signature, so that you can express any arbitrary logic you need on the expression's dynamic argument. This is why you had been able to write that comparison between a string-column and a string-argument in your C# code.
  • Once you have written your query or command using DLEs, and you have executed it, Kerosene ORM captures on the fly the schema of the results produced by your database. With this schema it generates, per each iteration, an instance of a 'dynamic record' object. As mentioned, these records are able to adapt themselves, dynamically, to whatever schema was captured. All these things working together are the reason why you have not had to write and maintain any external mapping files, and why you have not had to write any receiving class or wrapper.
  • These elements also make Kerosene ORM quite a resilient solution. As far as the names of the tables and columns you are interested in don't change you don't have to worry about if anyone has changed anything in the database. On top of that 'Kerosene ORM makes no differences between tables and views so, for instance, if your beloved DBA modifies your database so that the table you were using becomes a view of a now hidden table, if its name can be still be used, and the names of the tables and columns your application used remain the same, well, your application will continue to work without suffering in any way.

More Database Operations

Kerosene ORM is extremely flexible and provides complete support for very complex query scenarios, including multiple tables, joins, CTEs, grouping and aggregations, ordering specifications, skip and take, etc.

For instance, you might know be asked to produce a similar listing but containing all the details of the non-active employees, along with the id and name of the countries they belong to. One way (*) of solving this problem is by using 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)
   .OrderBy(x => x.Ctry.Id, x => x.Emp.Id)
   .Select(x =>.Ctry.Id, x =>.Ctry.Name)
   .Select(x =>.Emp.All());

We have specified the tables we wanted to use, the conditions to filter what records to obtain, the order by which these records are to be obtained, and what specific contents these records are going to have.

(*) yes, I know, your seasoned SQL instincts were screaming for using a 'Join' clause, but in this example I just wanted to show how to query from several tables simultaneously. You can see how to use joins, and almost any other clause you can imagine, in the "In Depth" accompanying article mentioned above.

Obviously Kerosene ORM does also support the 'Insert', 'Delete' and 'Update' operations. As happened with the query ones the easiest way to create these commands is by using the corresponding extension methods of your link object. For instance, to insert a new record you just need to write:

var cmd = link
   .Insert(x => x.Employees)
      x => x.Id = "007",
      x => x.FirstName = "James",
      x => x.LastName = "Bond",
      x => x.CountryId = "uk");

var obj = cmd.First();
  • The 'Insert()' extension method takes a DLE argument that specifies the table the command will be executed against. Once that table is defined, the 'Columns()' method takes as its arguments a variable list of DLEs, each specifying, using an asignation syntax, the name of the column to insert and the value to assign to that column. In this example, and because 'Insert' is an "enumerable command", we can use its 'First()' extension method that, as its name implies, executes the command and returns the first record produced - in this case the record we have just inserted.
  • Let me now mention at this point an important difference between Kerosene ORM and other ORM solutions: by design, if the database produces no records when executing a command, then 'null' is returned (or, if the command produces a list-alike result, then it will be empty). You can agree with me, or not, but for me obtaining no records is not an error, but a completely valid result, and no exceptions shall be thrown in this case. As an added bonus, this design enables you to write more simpler code, as the exception control code is notably simplified.
  • If your command is also an "executable" one you could have used the 'Execute()' extension method instead, that merely returns the number of records affected by the execution of the command. All Kerosene ORM commands, except the query ones, are executable commands.

Let's now update a record. It is as simply as:

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

int r = cmd.Execute();
  • The first difference in this case is that we use a 'Where()' method to filter what records are affected by the command. This method takes as its argument a DLE that resolves to a boolean value (it follows the same considerations as the 'Where() method of query commands). If you don't use a 'Where()' method, all records in the table are affected by its execution: Kerosene ORM assumes you are a grown-up programmer and that you want to use your own logic. So, if you don't use a 'Where' clause, Kerosene ORM assumes that you know what you are doing and that this has been done on purpose. In this case, having the same effect as if you write a SQL update script without that clause.
  • Another interesting thing to mention, something that permeates all Kerosene ORM, is that nothing impedes you to express any valid SQL logic that would be allowed by your database engine in a given context. For instance, in the example, we have assigned the new value of the column not by using an argument value, but rather by writing a SQL expression.

As you can now imagine, to delete a record is as simply as:

var cmd = link
   .Delete(x => x.Employees)
   .Where(x => x.Id == "007");

int r = cmd.Execute();

The same discussion about the 'Where' clause applies here. Really, there is no much more to say about this command.

Extending Kerosene ORM's syntax

As mentioned in the introduction often we find ourselves in situations where having the ability of writing pure SQL code is definitely an advantage. It also happens that, by design, Kerosene ORM must be prepared to deal with any arbitrary database engine, with its own dialect and characteristics and, worse, to deal with any possible new version the vendor consider convenient to launch to the market.

To achieve these objectives, Kerosene ORM allows you to write any SQL code that might not be covered by the set of commands and methods provided by default. Actually, the library supports two mechanisms to achieve this objective: the so-called Raw commands, and the so called Extended Syntax mechanism. Let's briefly take a look at them.

Raw Commands and Store Procedures

Raw commands permit you to explicitly set the text and parameters of the command to be executed, while keeping the advantages provided by Kerosene ORM. For instance, if instead of using an 'Insert' command we wish to perform and insertion writing explicitly the SQL code for that operation, we could have done it as follows:

var cmd = link
      "INSERT INTO Employees (Id, FirstName, LastName, CountryId)"
      + VALUES ({0}, {1}, {2}, {3})",
      "007", "James", "Bond", "uk");

Why would you prefer to write that raw SQL insert code instead of using an 'Insert' command? Well, actually, I see no reason in this case, but there are many circumstances where having this capability really helps.

As a side note, please observe that you have had to explicitly include an 'OUTPUT' clause because we wanted to enumerate the results of the command. Note also that, in order to specify the arguments of the command, you use a '{n}' string format syntax, and then you have specified their values using an optional list of arguments. Please take a look at the "In Depth" accompanying article and materials for more details.

Another usage of 'Raw' commands is when the SQL syntax is so convoluted that no class or method in Kerosene ORM supports it. Yes, sometimes it happens Frown | :(

Finally, I often find myself using 'Raw' commands when I need to invoke stored procedures. For instance:

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

Extended Syntax Support

The second extensibility mechanism is achieved by using the 'escape syntax' feature of Kerosene ORM. Using it, you can embed any SQL-alike text you like in your code. It also provides with a fall-back way of translating any SQL methods or functions your database may support that were not known or treated appropriately by the 'parser' associated with the database's 'engine'.

Let's see an example. Suppose you want to obtain the number of employee records in your database. One, among many other ways of doing so, is by using the following code:

var cmd = link
   .From(x => x.Employees)
   .Select(x => x.Count(x("*")).As(x.Result));

var result = ((dynamic)cmd.First()).Result;

We basically wanted to execute a 'SELECT Count(*) AS Result FROM Employees' SQL command, but we have two problems: the first one is that the 'Count()' method is not among the ones intercepted by the 'parser', and the second one is that this method takes an strange argument, '*', that the C# syntax do not accept.

No panic. The argument of the 'Select()' method is a DLE we can use to write whatever we need in a dynamic fashion. In this case, we have used a 'Count()' virtual extension method (*) on the dynamic argument of the lambda expression. As it is not among the ones the 'parser' is prepared to intercept, then Kerosene ORM injects its name straight into the SQL command to execute, along with any arguments it may have.

(*) a virtual extension method is a method applied to a dynamic element in a dynamic lambda expression (DLE) that, in the general case, is not previously defined. By using dynamics we are telling the C# compiler not to early bind that method, but to postpone the binding for run-time (a mechanism known as "late bound"). The nice thing is that this non-existent method is never executed (*), because it is just used to express the arbitrary logic to be parsed.

(*) actually this is not completely true, but here is not the place where to discuss how Dynamic Lambda Expressions are parsed. Please refer to the supporting technologies articles mentioned at the bottom of this article.

Next thing: C# does not allow us to have a hanging '*' symbol. Hence why we have used the so-called 'rounded escape syntax' (using a 'x("*")' form), by which whatever thing that appears inside a dynamic argument invocation it is also injected straight into the SQL command. This feature allows us to write any text without it being captured as a command's argument. We could have also used the so-called 'square escape syntax' (using a 'x["*"]' format) that works exactly the same way but sometimes is easier to use from a C# syntax perspective.

By the way, what's a 'parser'? It is an object associated with an 'engine' instance, which knows how to parse the C# expressions we write (actually, not only expressions but any arbitrary object), and how to translate them into the appropriate SQL syntax understood by the underlying database engine. Please take a look at the "In Depth" article for more details.

Converting the results into Entities

So far we have just obtained 'record' instances as the results of our commands. They are quite powerful dynamic objects, as they are prepared to adapt themselves to whatever schema the results may have, and to provide a dynamic way of accessing that contents.

But, obviously, they are not enough in many circumstances: those when we want to use instances of our business classes. 'Kerosene ORM' provides two main mechanisms to achieve this objective: the converters and the entity maps.

Dynamic Records' Converters

The first mechanism Kerosene ORM provides is used when, for instance, we want to 'project' the results into a given class instance, an anonymous type, or one of our business classes. It works by defining a 'converter' that takes, in each iteration, the dynamic record and converts it into whatever object the converter wants to return. Let's see an example:

var cmd = link...; // any enumerable command you wish

Func<IKRecord, object> converter = rec =>
   dynamic r = rec; // Just to simplify the syntax when using dynamic records...
   return new {
      Name = string.Format("{0}, {1}", r.LastName, r.FirstName)

foreach(var obj in cmd.ConvertBy(converter))
   Console.WriteLine("\n> Converted = {0}", obj);

The 'converter' is a delegate that takes a 'record' instance and uses it to generate an object that will become the 'Current' element of the iteration - in the example, an instance of an anonymous type. Please take a look at the "In Depth" accompanying article for more details.

Kerosene ORM Entity Maps

The second mechanism Kerosene ORM provides is architected to map the columns in the database into the members of your POCO business classes. It is the analogous to the Entity Framework or nHibernate frameworks but, in my modest opinion, easier to use and clearer to understand.

In its simplest, and more often used form, it requires nothing from your side but to say out loud what those entities are (their types) and where to find them in the database (using a link instance and specifying their primary table).

Why? Because it takes a number of educated guesses on your behalf to gather what columns are available in the database and how to map them into the appropriate members of your business classes. There is no need to mention that you can customize to any imaginable extend that default mechanism. For instance, you can define explicitly what columns to map, or not, you can identify and use navigational properties and dependencies, and so forth. Please take a look at the accompanying "In Depth" article mentioned above for deeper details.

Let us take, in any case, a brief look at some easy examples. For instance, let's suppose we have a (minimalist) HR system composed by three tables that keep track of the employees, of what countries do they belong to, and of what regions and super-regions are those countries associated with. We have also created the corresponding POCO classes (take a look please at the sample code). We can easily register the maps by using the following code:

new KMetaMap<Employee>(link, x => x.Employees);
new KMetaMap<Country>(link, x => x.Countries);
new KMetaMap<Region>(link, x => x.Regions);

We do not need even to keep track of these map instances as Kerosene ORM will do so in our behalf. In advanced scenarios, where we may want to customize the mapping mechanism, we typically will create our own derived map classes.

Once we have a map registered in our 'link' instance we can use its generic extension methods to create the commands we are interested on, and Kerosene ORM will do all the heavy lifting of the conversions for us, while keeping the ability of writing any arbitrary logic we may need. For instance, to obtain the top-most region in the database we can write:

var cmd = link.Where<Region>(x => x.ParentId == null);
var reg = cmd.First();

It is also very nice that we are not constrained by any pre-cooked queries, as it happens in other non-dynamic ORMs, but rather we can write whatever query logic we want for our entities regardless if they have, or not, the members used in that query. We can also write quite complex queries involving more convoluted logic, other tables, joins, and so forth. This capability permits us to implement an unique Dynamic Repository Pattern. I strongly recommend you to take a look at the "Data Services" accompanying article for more details.

Obviously the maps mechanism notably simplifies the syntax for the standard CUD operations. For instance, once we've got that instance, if we want to update it, it is as simply as:

var root = link.Find<Region>(x => x.ParentId == null);
if(root != null) {
   root.Name = "New Name";

The 'Find()' method runs, behind the scenes, an optimized version of a query command, and returns immediately the first instance found that matches the given conditions (or, remember, null if if there are no records available).

The interesting thing here is that Kerosene ORM inherently implements the Unit of Work Pattern: the generic 'Update<T>()' method creates a command that, if we are happy with it, we can submit into the link for future execution. Once we have submitted all the commands we want to execute as a unit of work then we call the 'SubmitChanges()' method of the link, and all of them are executed at once under an ad-hoc transaction created for this set of operations. Again, please take a look at the accompanying articles for more details.

Just for completeness let's take a look at how can we delete that entity, and how can we insert a new one in the database (all in one shot under the same automatic transaction):

var root = ...; // obtained as before

var newroot = new Region() { Id = "000", Name = "New Root Region" };


What else?

Actually there are a ton of things we have not covered in this introductory article. I encourage you to take a look at the accompanying articles for more details about Kerosene ORM.

Also, there are a number of supporting technologies and elements without which Kerosene ORM would not have been possible. Among them I would like to mention:

  • Dynamic Parser: describes the internals of how to parse an arbitrary DLE (dynamic lambda expression) into a tree of agnostic logic operations, without being constrained by any specific type. This technology is the core of the parsing mechanism of Kerosene ORM.
  • Deep Object: a serializable multi-level dynamic object that can be used to carry any arbitrary dynamic members nested to any arbitrary depth. Kerosene ORM uses this class in WCF connection scenarios to customize how the server-side WCF service may serve the arbitrary WCF client-side applications that connect to it.
  • C# Easy Extension Properties: the way Kerosene ORM manages your mapped entities, without requiring you to use in your POCO classes any attributes or modifications, or forcing them to inherit from any ORM specific one, far resembles the mechanism presented in this article to implement C# extension properties. The concrete implementation happened to be very specific to Kerosene ORM, but the fundamentals are the same, and can be found in this article.

NuGet packages for this new version are in the making as you are reading this article.

There is an additional library, in alpha stage at the moment this article is published, whose aim is to permit you to dynamically define in your code the structure of your databases, and to automate the maintenance of their tables as needed (to some extend what Code First Migrations does if you wish). I have currently no compelling reasons to devote all my energies on this side project (as I am old-schooled and rather prefer to write my own SQL scripts), but I do understand it can be very convenient, so I will publish it as soon as I finish it and I feel it is robust enough for production usage.

Also, sooner or later, I will extend the generic support for Oracle and MySQL databases the same way I did with MS SQL Server ones, providing custom adapters for these engines. I'm really open to help and to recognize the contribution of anyone that wants to take on these supporting libraries. Just contact me in the forum.

What comes with the Download?

The download comes with the complete source code of the library packages, covering dynamic records, Entity Maps, WCF link services, Data Services, a specific customized engine package for MS SQL Server databases, and sample applications and tests. All these topics are explained in the related accompanying articles mentioned at the introduction.

The easiest way of using the sample applications is to unzip the download into your favorite folder, open either the 'Kerosene' solution or the 'Blazer' one, and to select from them what will be start-up project (or projects, in the case of the WCF client and server).

The first solution, 'Kerosene', is designed to test the core functionality of Kerosene ORM in isolation. It provides an extensive set of samples to show how Kerosene ORM really mimics, from C#, whatever SQL code you may need in your application. You can also use them as a template to write your own code.

The second solution, 'Blazer', is just a minimalist HR application designed to test the libraries in an "n-Tier Scenario with Data Services". Indeed, it comes with three (yes, 3) application stacks: one for 'Table Maps', one for 'Eager Maps', and one for 'Lazy Maps', so that you can choose which one better adapt to your own needs and use it as an starting point for your application. Note that each stack comes also with its own Front-End, Mid-Tier and Back-End specific projects.


Performance will very much depend on, obviously, what platform are you using to run the code, on how you have architected your application, and how you have used your favorite software development patterns. Anyhow, the download includes some performance tests, along with the database scripts you may need to recreate the test-bed they use.

As an indication, in my 3 years old laptop, running Windows 7 64 bits, and the development version of MS SQL Server 2012, with 4 megs of RAM and an aging 5400 rpm IDE hard disk, when compiling in release mode I'm obtaining:

  • About 38k record per second when using dynamic records.
  • About 15k records per second when using custom converters.
  • About 8k records per second when using table entity maps.
  • About 5k records per second when using lazy entity maps.
  • About 4k records per second when using eager entity maps (but note that this mode needs to take into consideration other elements, as memory consumption, and the complexity of the dependency properties you might be using).
  • About 2k records per second in a TCP/IP based WCF connection scenario with dynamic records underneath.

In my environment, and with the specific software development patterns I'm using, Kerosene ORM successfully compares against Entity Framework while providing all the benefits we have seen in this article. But, as English people says, your mileage may vary, so I do encourage you to do your own tests in your own environment.


  • Version 6.5, February 2013: This current version comes with some minor bugs fixed and some minor improvements on the way the library automatically handles 'cascading' dependencies. This version also comes with explanations on how the library implements an unique Dynamic Repository pattern, and with a generic implementation of the Unit of Work pattern, with sample applications that demonstrate those capabilities.
  • Version 6.0, April 2013: Its focus was to include a completely revised "Entity Maps" mechanism and architecture, supporting table, eager and lazy loading variations for navigational or dependency properties, and an improved and cleaner overall architecture. It does also include the concept of 'generic engines' so supporting out-of-the-box MS SQL Server, Oracle, ODBC and OLE DB database generic engines.
  • Version 5.5, January 2013: A maintenance version whose aims were, firstly, to simplify the syntax so that we don't need any longer a specific override for each possible clause variation and to, secondly, include an improved extensibility mechanism for the Kerosene ORM's "Entity Maps".
  • Version 5.0, September 2012, Kerosene ORM: It was unfortunate that one of the industry juggernauts chose 'Kynetic' as the name of one of its products, so I got tired of asking unrelated support questions, and decided to change the name. In any case, the focus of this version was to formalize the "Entity Maps" mechanism that substitutes the ones available in both Entity Framework and nHibernate frameworks.
  • Version 4.5, May 2011: Included firstly an improved support for generic command arguments. Since this version the library accepts any arbitrary value for a command parameter, even those of your custom classes, regardless if they are supported or not by the underlying database, and without the need of converting them into strings. Secondly, it included a more advance preliminary support for what later became the "Entity Maps" mechanism.
  • Version 4.0, January 2011: This version introduced the concept of 'agnostic links' that decouples the library from any specific connection or data context type. This was needed in order to provide support for WCF connection scenarios. It did also include, buried behind this WCF support, a first tentative version for the "Entity Maps" operational mode.
  • Version 3.0, October 2010, Kynetic ORM: Its focus was on an improved and more convenient parsing mechanism, using fully the concept of DLEs (Dynamic Lambda Expressions), and on performance. As the 'MetaDB' name was not compelling enough I changed it into 'Kynetic ORM'.
  • Version 2.0, August 2010, MetaDB: Its focus was to incorporate support for CUD operations (hence why the its name changed from MetaQuery to MetaDB), and to resolve some bugs.
  • Version 1.0, June 2010, MetaQuery: it was the first public version of this project. Its focus was mainly to just sending kind-of dynamic queries against a concrete underlying database, and very primitive CUD-alike operations. It was, basically, a wrapper around the query capabilities of ADO.NET.
  • Versions 0.x, 2008-2009: Preliminary non-public initial versions.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Moises Barba

Spain Spain
Moises Barba has worked as CIO and CTO for some start-ups, and as Consulting Director for some major multinational IT companies. Solving complex puzzles and getting out of them business value has ever been among his main interests - that's why he has spent his latest 20 years trying to combine his degree in Theoretical Physics and his System Engineer MCSE with his MBA... and he is still trying to figure out how all these things can fit together. Even if flying a lot across many countries, along with the long working days that are customary in IT management and Consultancy, he can say that, after all, he lives in Spain (at least the weekends).
Follow on   Twitter   LinkedIn

Comments and Discussions

QuestionGreat Work! PinmemberRyan Criddle24-Sep-12 15:33 
AnswerRe: Great Work! PinmemberMoises Barba24-Sep-12 23:55 
Hello Ryan, and many thanks for your comments.
Actually, your second sentence was able to perfectly summarize, and in a surprisingly small number of words, the objectives and benefits of Kerosene... Have you considered working as a professional journalist? (joking)
Regarding your question: bear in mind that the expressions are parsed only once and then executed, they are not parsed in each iteration of the command, it would have been madness. So in comparison with the time it takes to go to the database, execute the command, and return potentially hundreds or thousands of records... the impact of the parsing of one expression is neglictable in most circumstances.
If you business problem is, otherwise, not extremely database intensive, then yes you may end producing and consuming a higher expressions to execution ratio. I believe it won't ever be a major problem, and this is why I have never done formal performance tests.
If you are interested in my informal ones... well, using my desktop virtualized environment with 1x CPU 2.60 GHz, 2 GB RAM, running Windows 2008 server R2, SQL 2008 server, Visual Studio, and all the development stuff you can imagine:
- Parsing 1000 query commands: 2.5 seconds average.
- Parsing 1000 update commands: 1.77 seconds average.
The reason for the differences, apart of using more complex or easier expressions, is also because the Query commands have a heavier structure, and you pay a small penalty when creating them.
Thanks. Regards, Moisés
GeneralRe: Great Work! [modified] PinmemberRyan Criddle25-Sep-12 0:11 
GeneralRe: Great Work! PinmemberMoises Barba25-Sep-12 0:27 
GeneralRe: Great Work! PinmemberRyan Criddle25-Sep-12 0:31 
GeneralRe: Great Work! PinmemberMoises Barba25-Sep-12 0:35 
GeneralRe: Great Work! PinmemberRyan Criddle25-Sep-12 0:50 
GeneralRe: Great Work! PinmemberMoises Barba25-Sep-12 1:07 
AnswerRe: Great Work! PinmemberMoises Barba11-Mar-14 4:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150224.1 | Last Updated 20 Mar 2014
Article Copyright 2010 by Moises Barba
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid