Kerosene ORM Dynamic Records In Depth

Introduction

Kerosene ORM is dynamic, configuration-less and self-adaptive ORM library that overcomes many of the limitations and nuances found in other ORM solutions. It fully supports POCO objects without requiring any mapping or configuration files, polluting their code with attributes, or requiring them to inherit from any ORM-specific base class – so promoting a clear separation of concerns. It does not use any kind of conventions either, so we can write our classes and name their members and the columns and tables in our databases the way we want, not the way someone has decided for us. It gives us back full control on what SQL code will be executed: just what we write, no more, no less. It follows the Repository and Unit Of Work patterns, albeit implemented in a dynamic fashion which allows us to gain great flexibility.

All these characteristics make Kerosene ORM an ideal solution for iterative and agile development scenarios and, even more, where a number of disparate teams is involved, from developers to IT and database responsible ones. Kerosene ORM is currently being used in both in heavy client and web applications, and in data intensive scenarios.

This article explores the Dynamic Records operational mode of Kerosene ORM as well as other core concepts of the library. Please refer to the introductory article Kerosene ORM Introductory Article for more context information as this article elaborates on the concepts introduced in it.

Preliminary Concepts

Namespaces

The Kerosene.ORM.Core namespace contains the definition of the main structures and interfaces of the library, and it should be imported for all practical purposes. The Kerosene.ORM.Direct namespace contains these elements adapted to “direct connection” scenarios, where we connect to a database by using, fundamentally, a connection string. The WPF package will in turn provide the adaptation of these elements to WPF scenarios, but it is beyond the scope of this article and will not be discussed here.

Finally, the Kerosene.ORM.Maps namespace contains the interfaces and main structures used in the Entity Maps operational mode of Kerosene ORM that, by the way, is the one used more often. Please refer to the "Kerosene ORM Entity Maps in Depth" accompanying article for deeper details.

Data Engines

A data engine is an object that implements the IDataEngine interface, used by Kerosene ORM to represent the characteristics of a given database engine or version. Out of the box the core library comes with built-in support for generic ODBC, OLEDB, ORACLE and SQL SERVER engines.

Being out-of-the-box these engines are essentially a generic representation of their associated databases, but they take not into consideration any specific dialect syntax or database capabilities. In order to represent those varieties custom engines can be built than inherit from these base ones. For instance, the additional Kerosene.ORM.SqlServer package provides support for SQL SERVER 2008, 2012 and 2014 versions. Its code can also be used as a template to develop your customize engine to support your favorite database or version.

Locating and registering Data Engines

We can always create an engine using its class’ constructor. But the preferred approach is to use the EngineFactory static class, whose Locate() method will let us find the appropriate one for our scenario:

var engine = EngineFactory.Locate();

When no arguments are used this method will find in the application’s configuration file the details to use from the default connection string entry, the one whose name is specified by the following line in the <keroseneORM> configuration section:

<keroseneORM>
   <connectionString name="MyFavoriteDB" />
</keroseneORM>

This name shall refer to a valid connection string entry whose providerName property will be used to find the appropriate engine to use. For instance the following entry will be used to find an engine adapted to SQL Server databases:

<connectionStrings>
   <add name="MyFavoriteDB"
        providerName="System.Data.SqlClient"
        connectionString="..." />
</connectionStrings>

If we are using our own custom engines we can register them manually so they will be available. But the easiest way is to specify them also in the configuration file as follows. For instance, to register the custom engines provided by the Kerosene.ORM.SqlServer package we just have to add the following lines:

<keroseneORM>
   <customEngines>
      <add
         id="SqlServer2008"
         type="Kerosene.ORM.SqlServer.v2008.Concrete.DataEngine" 
         assembly="Kerosene.ORM.SqlServer.dll" />
      <add
         id="SqlServer2012"
         type="Kerosene.ORM.SqlServer.v2012.Concrete.DataEngine"
         assembly="Kerosene.ORM.SqlServer.dll" />
   </customEngines>
</keroseneORM>

If instead of using the default entry we want to find a specific engine we can use the additional arguments of the Locate() method:

• name: if it is not null it can either be the invariant name of the ADO.NET provider to use (as System.Data.SqlClient) or just its tail part (SqlClient), or even the name of the concrete connection string entry in the configuration file we want to use.

• minVersion and maxVersion: when they are not null their values specify the minimum and maximum acceptable version for the engine to find.

• validator: if not null it is a delegate to invoke with a candidate engine to decide if we are comfortable with it or not. It is mostly used internally by the library but it worth mention it in case we ever need it.

• settings: if it is not null it shall be a name-value dictionary whose entries will be used to modify the values of the properties of the engine located whose name match any of the entries in the dictionary, so we have not to settle with their default values.

For instance, to find an engine registered for SQL Server databases, whose server version is “11” (2012) or greater, we can use:

var engine = EngineFactory.Locate(	name = "SqlClient", minVersion = "11");

Data Engines’ Standard Properties

Engines carry a number of standard properties that maintain the main characteristics of the underlying physical database engine they refer to. For instance the InvariantName one maintains the name of the ADO.NET provider associated with them, ServerVersion maintains the server version the engine is built for, CaseSensitiveNames maintains if the names of the tables and columns in the database are case sensitive or not, SupportsNativeSkipTake maintains if the database supports a native syntax to implement the skip/take functionality, and so on.

Data Type Transformers

Data Engines also maintain a collection of data type transformers that will help us to work with our custom types when used as command arguments or part of the logic of our commands, even when these types are not understood by the underlying database.

For instance let’s suppose that our application use a custom CalendarDate type to represent dates, and that we are using it in one of our query commands:

var cmd = link
   .Where<Employee>(x => x.BirthDate >= new CalendarDate(1968, 1, 1));

The value used will be captured and stored in a parameter to be injected in the command when executed. But, obviously our SQL database is not aware of our custom type. So to tell Kerosene ORM what to do in this circumstances we just need to register a transformer for that type:

var engine = ...;
engine.AddTransformer<CalendarDate>(x => x.ToDateTime());
engine.AddTransformer<ClockTime>(x => x.ToString());

The first variant will transform the custom type into a CLR one that is understood by ADO.NET. The second one is a fall back approach where the delegate registered as the transformer for the custom type merely returns the string representation of its value.

Both CalendarDate and ClockTime are custom types defined in the supporting Kerosene.Tools library. Their respective transformers are registered by default in the engines provided by Kerosene.ORM. You decide whether you want to use them or not, as you wish.

Data Links

A link is an object that implements the IDataLink interface, used by Kerosene ORM to represent an agnostic connection with an underlying database-alike service. Indeed such connection can be with a regular database, by using a connection string, in which case it is said to be a “direct” connection, or it can be with a WPF proxy that hides the underlying database from our application, or with… whatever service as far as it complies with the interface.

Links take also care of opening, closing and disposing the underlying connection on our behalf. They also take care of the life cycle of other managed structures as needed. Our applications need not to bother with all those details.

Creating a Link

In a direct connection scenario the easiest way of creating a link is by using the Create() method of the static LinkFactory class without any arguments:

var link = LinkFactory.Create();

In this case it will find in the configuration file the default connection string entry, as explained before. We can also use explicitly its engine and mode arguments as follows:

using Kerosene.ORM.Direct;

var engine = ...;
var mode = NestableTransactionMode.Database;
var link = LinkFactory.Create(engine, mode);
link.ConnectionString = "...";

The mode argument specifies what will be the default mode for the transaction object the link will use (more on this below). Also note that, when used this method this way, we have to set afterwards the ConnectionString property of the new link with the appropriate contents before using it. This property is available because this instance is a "direct" one - other varieties may have their own properties.

Nestable Transactions

The Transaction property of our link instances maintain an instance of a INestableTransaction object, used by Kerosene ORM to manage transactions associated with that link that can be nestable.

• Their Start() method is used to start a new physical transaction or, if such already exists, to increase its nesting level. Its IsActive property returns true if the underlying physical transaction is active, and its Level property will return the nesting level (0 meaning no active).

• Similarly their Commit() method decreases the nesting level and, when needed, will commit the physical transaction.

• Finally, their Abort() method unconditionally aborts any physical transaction that might be active. It means that it doesn’t matter to what level the transaction is nested because it will be cancelled completely.

Transactions also have a mode (of the NestableTransactionMode enumeration) associated to them, carried in the Mode property, whose value can either be Database or GlobalScope. The first one instruct the transaction to use the traditional database oriented approach, whereas the second one uses the modern global scope one.

Using Transactions

In the Dynamic Records operational mode the library will never, by design, start a transaction automatically. Remember that in this mode we are basically playing at low level and Kerosene ORM will not interfere in how your application behaves. You need to start, commit, and abort them explicitly.

On the contrary, if we were using the Entity Maps operational mode then Kerosene ORM will wrap all the pending change operations within a single transaction when they are executed against the database. The reasons is to honor the Unit of Work pattern by which all those operations succeed or fail as a unit.

Command Parameters

When Kerosene ORM encounters in one of our expressions something that looks like a value it will be captured and extracted out from that expression and replaced by the name of the parameter created to hold that value. This way SQL injection attacks are prevented by default. The results of method invocations are also considered values and captured accordingly.

A parameter is an object that implements the IParameter interface, used by Kerosene ORM to represents an agnostic parameter, not tied to any specific database type or structure. When the command a parameter is captured for is executed Kerosene ORM will transform its value, if needed, into a value the underlying database can understand, and the appropriate low-level ADO.NET parameter instance is created and injected into the command.

Just for the sake of completeness the IParameterCollection interface represents a collection of parameters. This collection is maintained in the Parameters property each command carries with it. You are advised not to mess with it unless you really know what you are doing.

Parsers

Even if our applications will seldom use parsers directly it worth to discuss these objects as they are at the core of the dynamic and self-adaptive capabilities of Kerosene ORM. A parser is an object that implements the IParser interface, used by Kerosene ORM to represent the ability of parsing any arbitrary object and translating it into a SQL syntax the underlying database can understand.

Parsers are closely associated with engines as they maintain what specific structures they have to intercept to accommodate to the characteristics and capabilities of their associated databases. Each link carries a Parser property that maintains the appropriate parser to use with it.

Additional considerations on parsers

Parsers have just one interesting public method: the Parse() one. Its argument can be, as said before, any arbitrary object, from null references, any valid value of object instance, and dynamic lambda expressions:

var s1 = link.Parser.Parse(null);
var s2 = link.Parser.Parse(7, pars);
var s3 = link.Parser.Parse(x => x.Id == "007", pars);
var s4 = link.Parser.Parse(cmd);

The first line will resolve into NULL or whatever other representation is appropriate for NULL values for the underlying database. When used in comparisons NULL values can also be translated into the appropriate 'IS NULL’ or 'IS NOT NULL’ constructions.

The second line will captured the value passed as its first argument, extract it into a new argument stored in the collection of parameters passed as its second argument. The Parse() method will return the name of the new parameter created. If no collection of parameters is used then the string representation of the value is returned instead.

The third line parsers a dynamic lambda expression. The parser will translate it into the appropriate syntax understood by the underlying database, also capturing its arguments if needed... and if a collection of parameters is specified.

Finally the fourth line received a command as its argument. In this case the command’s text is used and its parameters, if any, are captured and injected into the collection of parameters passed as its argument that, in the general case, will be the one used by the receiving command.

Parsers and Dynamic Lambda expressions

Dynamic Lambda Expressions (DLEs) are defined as lambda expressions where at least one of its arguments is a C# dynamic one. Kerosene ORM typically uses the Func<dynamic, object><dynamic, object=""> signature for these expressions.

When a DLE is written the C# compiler will make no attempts to bind the members and methods specified for these dynamic arguments at compile-time. Rather, this binding is deferred till run-time when the Kerosene ORM parser will execute the expression once to capture these operations and arguments. This information will be, in turn, translated into the appropriate SQL syntax expected by the underlying database.

That execution uses some special techniques to avoid the expression to depend on any specific data type. Combined with the deferred binding mechanism this is why we can write expressions like 'x => x.LastName >= "P"' to compare two string-alike objects using a natural SQL-alike syntax.

This is also what permits the parsers to intercept the specific SQL constructions associated with their underlying engines. For instance, the default base parser intercepts x.Not(…) and translates it into the SQL equivalent ‘(NOT …)’, x.Name.As(x.Alias) into ‘Name AS Alias’, and so on. The SQL custom parsers intercepts x.BirthDate.Year() and translates it into 'DATEPART(YEAR, BirthDate)', among many other extensions it is aware of.

When a given construction is not intercepted by the parser its name is injected into the returned string and then its arguments, if any, are also parsed and injected – this is, for instance, what happened with the COUNT example discussed in the introductory article.

Where to find more information

The inner details of the Dynamic Lambda Expressions’ parsing mechanism is an interesting but complex topic whose explanation is beyond the scope of this article. For further information please refer to the "DynamicParser: How to parse Delegates and Dynamic Lambda Expressions and convert them into Expression Trees" article.

Commands

Objects that implements the ICommand interface are used by Kerosene ORM to represent different commands that can be executed against the underlying database. The IQueryCommand, IInsertCommand, IDeleteCommand, IUpdateCommand and the IRawCommand interfaces represent those command types. The easiest way of creating these commands is by using the appropriate methods of the link instances that will make sure of creating the appropriate ones for them:

var cmd1 = link.Query();
var cmd2 = link.Insert(...);
// etc..

Commands carry the CanBeExecuted property that returns false if, for whatever reasons, the state of the command does not permit its execution: this is the case when their syntax is not completed yet. Otherwise this property returns true. Note that Kerosene ORM never perform checks about the validity of the command’s syntax: it will use whatever one you have written (directly or through the command’s methods).

Enumerable commands

Enumerable commands implement the IEnumerableCommand interface, and when executed will return the records produced by the database. In addition of being enumerable instances they also provide the ToList(), ToArray(), First() and Last() methods. The two last ones will return null if no records are produced by the command’s execution.

The Last() method is provided as a fall-back mechanism because it will retrieve and discard all available records till the last one is found. When possible it is strongly recommended to modify the command’s logic to use the First() method instead.

Scalar commands

Scalar commands implement the IScalarCommand interface and, when executed, will return an integer as its result. This integer could be the number of records affected by the execution of the command or, in the general case, any value the command decides to return. Scalar commands are executed by invoking their Execute() method.

Records

As said the execution of enumerable commands will return the records produced by the database. Kerosene ORM will use objects that implement the IRecord interface to hold their contents and metadata.

Records are dynamic objects that adapt automatically to whatever structure those contents have: it doesn’t matter how many columns are returned from the database, or what their types are, we will be able to manage all these scenarios in the same unified way. If we need to understand the details of that structure each record carries the Schema property precisely for that purpose, which we will discuss later in this document.

Reproducing the discussion in the introductory article we can access their contents using both dynamic and indexed ways:

foreach (dynamic rec in cmd)
   Console.WriteLine("\n- Country: {0}, Employee: {1}, {2}",
      rec.Ctry.Id,
      rec["Emp", "LastName"],
     rec.FirstName);

• As we have used the dynamic keyword for the enumeration variable we have been able to access its contents dynamically, as in 'rec.Ctry.Id'. Note that in this case we have specified the table and column we are interested in, but if there were no ambiguity we could have used instead just the column name (as in 'rec.FirstName').

• Note that we have used in the example the table alias, but we could have also used the table name if we have preferred to do so: 'rec.Countries.Id'. Records keep track of the aliases used so both forms are equivalent.

• Finally, accessing the contents dynamically is quite nice but it has some performance penalty. If we don’t want to pay this no-so-big price we can also use the indexed approach where we can specify the table and column names, or just the column one.

Records do not implement, by design, the INotifyPropertyChanged interface: they are not intended to be used in a user interface but rather they basically are the carry pigeons between the database and our application – a kind of DTO (Data Transfer Objects).

To generate objects to be used in those scenarios you are better served by creating the concrete instances with the Convert mechanism (more on this later), or by using the Entity Maps operational mode.

Schemas

As mentioned above Kerosene ORM uses objects that implement the ISchema interface to maintain the structure and metadata of the records retrieved from the database. Each schema contains a collection of ISchemaEntry entries that, in turn, are the ones that ultimately carry the metadata associated with a given column in a given table.

Their TableName, ColumnName, IsPrimaryKeyColumn, IsUniqueValuedColumn and IsReadOnlyColumn properties maintain the values their names imply. These values are obtained automatically by Kerosene ORM each time a command is executed so we don’t have to specify them in advance, or in any configuration or mapping file.

Note that the TableName property can be null if only one table is used in the query, or when the table is considered the “default” one in the scenario where the schema is used.

On top of those standard properties each schema entry also carries a dictionary that contains the complete set of metadata retrieved from the database, which can be enumerated using the Metadata property, or by using the this[metadataName] syntax. This metadata typically includes the SQL type used by the column, its size or other restrictions, and so on. This information can be handy to implement validation rules, for instance.

Use Cases

The discussions that follow will use the business scenario presented in the introductory article that won’t be reproduced again here.

Query Commands

Query commands are used to express a ‘SELECT’ operation against our database. The easiest way of obtaining such objects is by using any of the Query(), From(),or Select() methods of our link instances:

var cmd = link.From(x => x.Employees)...;

The Query method

This method just instantiates a query command of the appropriate type for our link (engine) instance.

The From method

The From() method lets us specify from what table we want to retrieve contents. We can use as many From() methods in the same command as we need to query from several tables simultaneously:

var cmd = link
   .From(x => x.Employees.As(x.Emp))
   .From(x => x.Countries.As(c.Ctry))
   ...;

This command will translate so far into:

SELECT * FROM Employees AS Emp, Countries AS Ctry ...

Note that because both the Employees and the Countries tables have an Id column the SQL syntax needs us to specify aliases to disambiguate between them. We have used the Alias() virtual extension method appended to the name of each table to do so.

It may also happen that the FROM clause refers to another query or command. As we need to provide an alias for these contents the easiest way to achieve it is by using the Kerosene ORM "scape syntax":

var otherCmd = ...; // Any kind of command
var cmd = link
   .From(x => x(otherCmd).As(x.Whatevername))
   ...;

We are not restricted to use names or other commands only, we can also use any valid SQL code in the FROM method as far as it is understandable by the underlying database engine.

Where method

Obviously almost all query commands will need to use a WHERE clause to filter what records to retrieve:

var cmd = link
   .From(x => x.Employees.As(x.Emp))
   .Where(x => x.Emp.Id >= "007" || x.Emp.ParentId == null);

The argument of the Where() method is a dynamic lambda expression where we can write any arbitrary logic we may need. We can also chain several Where() methods; in this case the logic each one contains is concatenated, by default, using an AND operator. If you wish to use an OR operator instead then you can use the Or() virtual extension method as follows:

var cmd = link
   .From(x => x.Employees.As(x.Emp))
   .Where(x => x.Emp.Id >= "007")
   .Where(x => x.Or(x.ParentId == null));

Select method

If we don’t use a Select() method then we will obtain the 'SELECT *' clause that, in general, cannot be considered as a best practice. To specify what columns we are interested in we can chain as many Select() methods as we wish:

var cmd = link
   .Select(x => x.Emp.Id, x => x.Emp.Name)
   .Select(x => c.Ctry.Id);

In this example we have selected three columns from two tables. Note that we can use also several dynamic lambda expressions as the arguments of a single Select() method, or can freely mix both approached as we like.

If we ever need to specify that we are interested in all columns from a given table we can use the All() virtual extension method attached to the name or alias of the table:

var cmd = link
   .Select(x => x.Emp.Id, x => x.Emp.Name)
   .Select(x => c.Ctry.All());

Distinct method

Yes, there is also a Distinct() method we can use:

var cmd = link
   .From(x => x.Employees.As(x.Emp)).Select(x => x.Emp.All())
   .Distinct();

that gets translated into:

SELECT DISTINCT Emp.* FROM Employees AS Emp

Let me emphasize again that the order in which we use the methods, or how many methods we chain, is not important: Kerosene ORM will annotate the relevant contents and place them in the proper order for the SQL command.

In and NotIn methods

Both virtual extension methods are intercepted by the parser and translated into their SQL equivalents as follows:

var cmd = link
   .From(x => x.Employees)
   .Where(x => x.Id.In("007", "008", "009"));

This command is translated into:

SELECT * FROM Employees WHERE Id IN (@0, @1, @2)

and its Parameters collection will store the concrete values we have used.

Join method

We can, obviously, use joins as well, using the Join() method:

var cmd = link
   .From(x => x.Employees.As(x.Emp))
   .Join(x => x.Countries.As(x.Ctry).On(x.Ctry.Id == x.Emp.CountryId));

The dynamic lambda expression used as its argument has both the As() and the On() virtual extension methods where we can specify the alias of the joined table and the join condition to use. The command in this example gets translated into:

SELECT * FROM Employees AS Emp
JOIN Countries AS Ctry ON (Ctry.Id = Emp.CountryId)

We are not restricted to use just the standard JOIN clause. If we want to use any of its variants we can use the following syntax:

var cmd = ...
   .Join(x => x("LEFT JOIN").Regions.As(x.Super).On(x.Super.Id == x.Reg.ParentId));

In this case we just need to use the scape syntax with the JOIN variant we want to use and prepend it to the table name (this is the x(“LEFT JOIN”) part). In its string argument we can write basically anything we want as far as it makes sense for the SQL engine we are using.

GroupBy and Having methods

We can also use the GROUP BY and the HAVING clauses as follows:

var cmd = ...
   .GroupBy(x => x.WhatEver)
   .Having(x => ...);

The Having() arguments follows the same rules as the Where() ones.

OrderBy method

No surprises, we can also use a Order<code>By() method:

var cmd = link...
   .OrderBy(x => x.Id)
   .OrderBy(x => x.Name.Desc());

Its arguments are dynamic lambda expression used to specify the names of the columns. By default the order will be ascending, but we can use the Desc() or Descending() virtual extension methods instead.

Top, Skip and Take methods

Query commands do support the Top() method with the syntax we can now figure out:

var cmd = link...Top(3);

They do also support the Skip() and Take() methods with the same syntax, but there is one caveat to bear in mind: that many (mostly old) database engines do not have a normalized syntax to implement this capability. If this is the case Kerosene ORM still support this syntax but will emulate this capability by retrieving and discarding records until the skip number is reached, which can lead to not-optimal performance.

Actually the engines carry the SupportsNativeSkipTake property to interrogate them about this native support. If its value is true then Kerosene ORM will use the appropriate database syntax. If not… you can rely in the emulation or, if you prefer it, you can also always use a Raw command instead.

CTE expressions

Support for the With() method is dropped in the version 7 API. The workaround is to write the concrete syntax supported by your database using a Raw command.

Insert, Delete and Update commands

The syntax of these three command is actually much easier than the query one, and we basically are going to reproduce here the discussion contained in the introductory article.

To insert a new record database we can use:

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

The argument of the Insert() method is, again, a dynamic lambda expression that resolves into the name of the table we are interested in. Then we use its Columns() method, which takes a variable number of dynamic lambda expressions each specifying the column affected and its value, where this value can resolve into any valid SQL sentence, as well as to any reference or value we can obtain from the surrounding C# code.

Updating a record follows the same pattern. We just need to locate what records we are interested in and then specifying the columns to modify:

var cmd = link
   .Update(x => x.Employees)
   .Where(x => x.Id == "007")
   .Columns(
      x => x.Id = "008",
      x => x.FirstName = x.FirstName + "_Surrogate");

Note that the WHERE clause does not need to resolve to just one record, but also to a set of them if we want to update many records in just one operation.

We can now figure out what we have to do to delete one or many records from our database:

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

Note that if we have not used the WHERE clause then we will end up trying to delete all records from the table we are dealing with. Remember, Kerosene ORM treats us as grown up developers, and assumes we know what we are doing.

Raw Commands

From time to time it may happen that we want to execute some logic that is not completely covered by any of the standard commands. For instance, we may want to use a CTE expression, or we want to invoke a stored procedure. For this scenarios Kerosene ORM provides us with the specialized “Raw” command. Let’s see an example:

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

Whatever text we write as the argument of the “Raw” command will be executed against the database: in this example we are, for instance, invoking the ‘employee_insert’ stored procedure. There are two nice things to bear in mind:

• The first one is that we can specify the command arguments using the standard curly brace ‘{n}’ syntax. Kerosene ORM will capture those arguments to avoid SQL injection attacks.

• And the second one is that Raw command can be both enumerated and executed as a scalar command, depending upon the concrete logic we are using. So, in the first case we will receive the records produced as happened with any of the standard commands, and obtain the metadata associated with them.

Converting Records into Entities

The standard way of dealing with POCO entities is by using the Entity Maps operational mode of Kerosene ORM. But in some scenarios this is not required, for instance when there is no receiving class for these entities because they are only used in a concrete or isolated part of our application.

For this scenarios the Dynamic Records operational mode supports a convenient mechanism to convert records into entities, even instances of anonymous classes, by using the so-called “converters”. The Kerosene ORM enumerators used to execute enumerable commands have the Converter property, , a delegate whose signature is Func<irecord, object=""> that, if it is not null, is then invoked each iteration to convert the current record to whatever object the delegate wants to return. For instance:

var cmd = link.From(x => x.Employees);
foreach (var obj in cmd.ConvertBy(rec =>
{
   dynamic r = rec;
   return new { r.Id, Name = string.Format("{0}, {1}", r.LastName, r.FirstName) };
}))
Console.WriteLine("\n> Object: {0}", obj);

This example converts the records retrieved from the database into instances of the anonymous type defined inside the on-line converter defined in the ConvertBy() method. This method is a convenient way of instantiating a new enumerator and to set its converter in a single shot.

Other goodies

The unique dynamic approach of Kerosene ORM let it support non-conventional scenarios, among them the two examples we will discuss below.

Nested readers

Let's suppose that our Country business class has a property named Employees that is a list of employees. You want to populate that list, along with other members of your class, when you are enumerating a command that returns these countries.

If we are not using the Entity Maps operational mode (that would be the recommended approach) we can use a nested converter (or reader) as follows:

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;
   objCtry.RegionId = dinCtry.RegionId;

   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.BirthDate = dinEmp.BirthDate;
      objEmp.FirstName = dinEmp.FirstName; objEmp.LastName = dinEmp.LastName;
      objEmp.ManagerId = dinEmp.ManagerId; objEmp.CountryId = dinEmp.CountryId;

      return objEmp;
   })) ;
   return objCtry;
})) ;

The only caveat to bear in mind is that our connection needs to be configured to support multiple simultaneous results sets. Actually this is also the recommended setting for the “Entity Maps” operational mode.

Nested change operations and database constraints

We can also use a similar approach to execute nested updates or change operations. For instance to modify the primary key of one of our countries (something really hard to achieve by other ORM solutions, and even harder in cascade scenarios) we can write:

var raw = link.Raw();
raw.Set("ALTER TABLE Countries NOCHECK CONSTRAINT ALL"); raw.Execute();
raw.Set("ALTER TABLE Employees NOCHECK CONSTRAINT ALL"); raw.Execute();

var cmdCtry = link
   .Update(x => x.Countries)
   .Where(x => x.Id == "es")
   .Columns(x => x.Id = "es#");
   
foreach(Country ctry in cmdCtry.ConvertBy(recCtry =>
{
   dynamic c = recCtry; Country objCtry = new Country() {
      Id = c.Id,
      Name = c.Name,
      RegionId = c.RegionId
   };
   
   var empCmd = link
      .Update(x => x.Employees)
      .Where(x => x.CountryId == "es")
      .Columns(x => x.CountryId = "es#");
   
   foreach(Employee emp in cmdEmp.ConvertBy(recEmp =>
   {
      dynamic e = recEmp; Employee objEmp = new Employee() {
         Id = e.Id,
         FirstName = e.FirstName,
         LastName = e.LastName,
         CountryId = e.CountryId
      };
      
      objCtry.Employees.Add(objEmp);
      return objEmp;
   }));
}))
Console.WriteLine("\n> Country = {0}", ctry);

raw.Set("ALTER TABLE Countries CHECK CONSTRAINT ALL"); raw.Execute();
raw.Set("ALTER TABLE Employees CHECK CONSTRAINT ALL"); raw.Execute();

The basic idea is to execute a nested loop on the employee records inside an external one on the country ones. We have surrounded this operations using raw commands to de-activate the constraints and to re-active them afterwards. Because it pleases my economy of resources taste I have reused that raw command instance four times, but you can use brand new objects each time if you wish. Finally, please note that I have not included in this example any transaction-related code, but it will be absolutely needed in a production scenario.

What else?

Now that you have complete information about this “Dynamic Records” operational mode you may want to take a look at the similar one that discuss the “Entity Maps” operational one. To do so please refer to the link that is given in the introductory article.