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

Kerosene ORM Dynamic Records In Depth

, 11 Mar 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
A deep dive tutorial on Kerosene ORM Dynamic Records and core concepts of the library

Introduction

This article provides a deep dive tutorial on the Kerosene ORM's "Dynamic Records" operational mode, and on the core concepts and elements the library uses. If you have not done it already, you really want to read the Kerosene ORM Introductory Article because, otherwise, you will feel lost. Also, this article comes with no specific download: it assumes you are using the one provided by that introductory article, where you can find all the samples and library packages.

This article is divided in two main sections. The first one provides a deep dive on the core components and concepts of Kerosene ORM. The second one is basically a collection of use cases where the different command types, and their methods, are analyzed in detail.

Preliminary Concepts

This section provides a deep explanation on the core elements of Kerosene ORM, with an special focus on the "Dynamic Records" operational mode. Later on in this article we will see how this concepts are applied in a number of use cases. This article does not provide details on the "Entity Maps" mechanism, the "Data Services" agnostic implementation, or on the "WCF Links Service" library, topics that are covered in the related articles mentioned in the introductory article mentioned.

Engines

Probably the most fundamental concept in Kerosene ORM is the concept of an 'agnostic engine'. An 'engine' is an instance of an object that implements the 'IKEngine' interface, whose job is to maintain the characteristics of an specific database engine, along with its vendor and version for identification purposes, in an agnostic way. It also works as a factory to instantiate other elements adapted to that engine.

Kerosene ORM comes out-of-the-box with generic engines (also known as adapters) for SQL Server, Oracle, ODBC and OLE DB databases. It does also come with versions of these engines adapted to 'direct connection' scenarios (more about this concept below when talking about links). Finally, it includes a supporting library that contains customized engines for MS SQL Server versions 2008 and 2012 databases.

When the library starts it automatically registers all these generic versions, so that they are all known to the 'engine factory', and hence can be located automatically when needed. Any customized engine must be registered explicitly. So, for instance, if you want to use the customized MS SQL Server engines mentioned above you need to register them as follows:

using Kerosene.ORM.SqlServer.v2008.Concrete;
using Kerosene.ORM.SqlServer.v2012.Concrete;

...
   KEngineFactory.Register(new KEngineSqlServer2008());
   KEngineFactory.Register(new KEngineSqlServer2012());
...

Obviously the recommended place where to register your custom engines is at the application's start-up code. You just need to use the 'Register()' method of the static 'KEngineFactory' class passing to it an instance of the engine you want to register. As a safety measure the library intercepts, and ignore, any attempts to register the same engine instance twice.

But you can register several instances of the same type, as far as they are different ones. The reason is because each can carry its own specific settings - indeed this facility is used internally by Kerosene ORM to adapt the instances it manages to the specific environments that would be required in a given moment.

Once an engine is registered it can be located using several methods. The first one is by using its 'invariant provider name' so, for instance, we can find the first SQL Server adapted engine registered by using:

var engine = KEngineFactory.LocateByInvariantName("System.Data.SqlClient");

A nice feature of Kerosene ORM is that it also supports partial matches on the trailing part of the provider name, so we could have used just 'SqlClient' instead. This method supports and additional parameter that permits us to specify the specific version of the engine we are interested in. For instance, if we wish to get the 2012 engine version we can use:

var engine = KEngineFactory.LocateByInvariantName("System.Data.SqlClient", "11");

In this case, SQL Server 2012 identifies itself with as an "11.x.y.z" version, and because we were not interested in the any specific minor version we can just use the first part of the string.

If we specify a given version the factory returns the engine whose version matches the one given, or the smaller version that best approximates the requested one - but never an engine with a bigger version. On the flip side, if we do not specify any version, the factory will return the registered one with the biggest version specification.

If filtering by version is not enough the factory provides another optional argument which is known as a 'validator'. It is a 'Func<IKEngine, bool>' delegate that takes a tentative engine and returns true or false depending upon if that engine is valid according to the rules that delegate wants to implement.

Finally, the latest optional argument is the 'settings' one, a 'Dictionary<string, object>', whose entries correspond to the names of the properties in the original engine, and whose values we want to customize. So, for instance, if we want to change the value of the 'CaseSensitive' property of the returned engine we just need to pass a dictionary containing an entry with that name, and with the value we want the property to have. Note that when using these 'settings' dictionary the engine that's returned is a clone of the original one.

A second way of locating an engine is by using the name of a connection string entry:

var engine = KEngineFactory.LocateByConnectionEntry("MyappDB");

In this example the factory tries to find a 'MyappDB' connection string entry in the configuration files, and uses its 'providerName' property to locate the appropriate registered engine. This method supports the same optional parameters explained above.

Note that this argument is an optional one, and so we can use the 'LocateByConnectionEntry()' method with no parameters. In this case the factory tries to find an entry in the 'appSettings' section of your configuration files whose name is 'KeroseneORM.ConnectionStringEntry', known as the "Kerosene Selector". It then uses the value of this entry as the selector of the actual connection string entry to use. For instance, the following line in the configuration files let's use locate the same engine as in the previous example:

<add key="KeroseneORM.ConnectionStringEntry" value="MyappDB"/>

The third and recommended way of locating an engine is by using the 'Locate()' method of the factory. It can be used in any of the ways mentioned so far, and if a 'name' argument is used it is either interpreted as the name of a connection string entry or, if no such entry can be found, as the invariant provider name. If it is null then the selector entry is located and used as described above.

Engines have a number of additional properties and methods that we will analyze below in this tutorial as soon as we need them.

Links

Closely related with the concept of engines is the concept of 'agnostic links'. A 'link' is an object that implements the 'IKLink' interface and whose job is to represent a given connection with an underlying database (also known as a data context). When a link is created it is associated with a given engine instance. This way we may end up having as many links as we need against databases of the type represented by a given engine instance.

'IKLink' instances are quite generic ones, and they are completely agnostic about the specifics of the actual connection underneath. The reason for this is because we will have links against databases in a 'direct connection' scenarios, but we may also have a link against a 'conceptual' database hidden behind a WCF service. WCF clients and servers are covered by the accompanying article mentioned in the introductory one, so they won't be covered in this text.

"Direct Links" are those where the application uses a connection string to connect directly to a database. As you can imagine they are the ones used more often. They implement the 'IKLinkDirect' interface, that inherits from the 'IKLink' one, and can be created by the 'Create()' method of the 'KLinkDirectFactory' static class. As you can now probably imagine by now this method can be used without parameters (so looking for the aforementioned selector in the configuration files):

var link = KLinkDirectFactory.Create();

We can also use a not null 'name' parameter, it being either the name of a connection string entry, or an actual connection string. In this later case we will most probably need to use as well the optional 'engine' parameter to specify the one to use for this link.

Links are opened, closed and managed automatically on our behalf by Kerosene ORM. We can even forget to dispose them if we want so - but, if we can, it would be really a good idea to dispose them as soon as we don't need them any longer. This way, a number of internal resources are freed, and a number of safety checks are taken at the moment we want, instead of when the GC decides so.

I don't want to finish the tour on links without mentioning their 'ExtendedInfo' property. It is basically a 'Dictionary<string, object>' instance each link instance provides to carry whatever information you may want to attach to that instance. This dictionary is heavily used by Kerosene ORM so you are advised to mess up only with your own entries.

Also please take note of the following two additional facts. The first one is that if the link is cloned then the library tries to clone every entry in that dictionary (if they implement the 'ICloneable' or the 'ICloneableWithLink' interfaces), instead of just copying the original values into the dictionary of the cloned link. The second one is that, when the link is disposed all the entries on this dictionary are also disposed (if they implement the 'IDisposable' interface).

Links have a number of additional properties and methods that we will see below in this tutorial as soon as we need them.

Transactions

Kerosene ORM implements a specialized transactional mechanism. Each link has a 'Transaction' property that carries an object implementing the 'IKTransaction' interface. This interface represents an agnostic and nestable transaction.

When using "Entity Maps", transactions are automatically managed on our behalf and we don't need to worry about them. But when using "Dynamic Records" Kerosene ORM, by design, assumes you want complete control on your environment, and no transactions are initiated explicitly. You don't need to use the transaction object of your link if you do not want so.

But, if you use it, you gain a 'nestable capability' by which, if the underlying physical transaction was not started yet, it is started as usual; but if it was already started, then just its nesting level is increased. Correspondingly, when the transaction is committed, if it is in a nested state, then its nesting level is just decreased; the physical transaction is only committed when this nesting level reaches cero. When the transaction is aborted, the physical underlying transaction is aborted regardless of its nesting level.

There are two reasons why Kerosene ORM uses this interface. The first one is to manage "Entity Maps" transactions in a generic and nestable way because, when cascading dependency or navigational properties, we have not any a priori information on how many entities or operations may be involved in an unit of work context.

The second reason is to manage transactions in an abstract way, without having any information of what would be the underlying database. Not only this is useful in the general case, but this is a must when using WCF connections in which, by design, the client application has no information and no visibility on the physical elements of the underlying database proxied by the WCF server service - among them, whatever transactions the server may, or may not, be using.

Parsers

A 'parser' is an object that implements the 'IKParser' interface. When a link is created it gets its own parser instance. Its job is to parse any given arbitrary object or expression into a SQL string having the appropriate syntax understood by the database engine.

When parsing an object or expression the parser extracts the values found in it, and places them into a list of arguments. This way SQL injection attacks are prevented by design. Null values are not extracted but the appropriate 'NULL' string is injected into the parsed result.

Engines are prepared to intercept some constructions and to translate them into the appropriate SQL code. The most common ones are the 'x.Not(...)' virtual extension method, which parses its unique argument and builds a '(NOT ...)' result string; the 'x.Distinct(...)' virtual extension method, which parses its unique argument and builds a 'DISTINCT ...' result string; the '...As(alias)' virtual extension method, used to associate an alias to whatever element this virtual method is applied to, and produces a '... AS alias' result string; the '...In(arg1, arg2, ...)' virtual extension method, which builds a '... IN (arg1, arg2, ...)' result string, and its accompanying 'NotIn(...)' virtual extension method that builds a 'NOT ... IN (arg1, arg2, ...)' result string; the '...Between(arg1, arg2)' virtual extension method, which builds a '... BETWEEN arg1 AND arg2' result string; and the '...Like(arg)' and '...NotLike(arg)' virtual extension methods, which build a '... LIKE arg' and '... NOT LIKE arg' result strings respectively.

These are the default constructions intercepted by the generic engine base class. The customized MS SQL Server engine adds more constructions, like the 'Left' and 'Right' ones, the 'Lower'and 'Upper' ones, the 'Year', 'Month', 'Day', 'Hour', 'Minute', 'Second', 'Millisecond' and 'Offset' ones, the 'LTrim', 'RTrim' and 'Trim' ones, and so forth.

The idea is that any customized engine will provide a factory method to create an adapted parser that, in turn, will be the only object that needs to know the specifics of the methods and syntax understood by the underlying database. So, if tomorrow, we want to add support for a brand new "Foo" database engine, whose syntax supports a strange 'Bar()' function, here is the place where to include that support (assuming that you cannot achieve that by using the 'escape syntax' that Kerosene ORM provides to deal with, precissely, this kind of needs).

Please take a look at the code in the download where you can find more examples and the concrete methods supported by both the base engine class and the SQL Server adapted ones.

Parameters

As just mentioned Kerosene ORM uses the concept of 'agnostic parameters': the parser extracts the values it encounters while parsing and object or expression, and places their values in a collection of generic parameters that can be used afterwards.

The nice thing to point out here is that those parameters are completely agnostic, and they do not have any knowledge on what would be the underlying database. So, inherently, they do not know what types are supported by any automatic ADO.NET conversions. Indeed, they can store any arbitrary value, including those of your custom application classes if needed.

For instance, if you take a look at the samples in the download, you will find two classes, 'CalendarDate' and 'ClockTime', which represents the date and time parts of a given DateTime instance. These classes have no more sense than being used as a test bed for this capability. So if we write something like:

var cmd = link
   .From(x => x.Employees)
   .Where(x => x.BirthDate >= new CalendarDate(1970, 1, 1);

when this code is parsed the value to compare the 'BirthDate' column against is captured and placed in the list of parameters that is associated with the command we were building, and this value is of the type of one of our custom classes.

When the command is executed Kerosene ORM needs to replace these values with those the underlying database can understand. What we need to do is to tell the library how to do so, by registering into the link the appropriate 'transformers' as follows:

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

Obviously the best place to run this code is as soon as we have created the link. Transformers are entries that associate a given custom type with a delegate that is able to transform the instances of that type into values understood by the database engine. In our example we have chosen to transform a 'CalendarDate' into 'DateTime', and to transform 'ClockTime' into its string representation (just to show that you have complete liberty on defining these transformers the way you may need).

Schemas

Another important element in Kerosene ORM is the 'schema'. This objects implement the 'IKSchema' interface and their job is to maintain an abstract description of the structure and metadata of the results produced by the execution of an 'enumerable' command.

A schema is composed by a collection of 'schema entries', each representing the metadata associated with a given column in the results' set. Each entry implements the 'IKSchemaEntry' interface, which provides a number of standard properties representing the most common metadata, and a dictionary containing the whole set of metadata the library was able to obtain from the database.

For instance, the 'TableName' and 'ColumnName' properties permit us identify what table and column a specific schema entry relates to (because, remember, we can get results from several tables simultaneously). If the 'TableName' property is null it means that this entry refers to the default table in a given context.

In the general case you will not use these objects directly very often, but they can really be quite useful when you want to implement, for instance, validation rules based upon the concrete characteristics of a column in the database.

Commands

The 'IKCommand' interface represents a generic command object. These are the objects that Kerosene ORM uses to execute your operations against an underlying database. There are five main command types: 'Query', 'Insert', 'Delete', 'Update' and 'Raw' commands. We will see their relevant usage and some samples in the Use Cases section below, so let's us just talk in this section about the considerations that apply to the abstract command interface regardless what would be its specific type.

The first one is that any command is associated with a given link, the one that will be used to execute that command. If you want to copy all the specifications of a command into a new one, but associating that new one with a different link, the command's 'Clone(IKLink newLink)' method does precisely this job.

As all commands are associated with one link, the easiest way of creating a command is by using the relevant extension method of your link instance: we have already seen many examples of this mechanism, as for instance in 'var cmd = link.Insert(...)'. Once you have your command created, each provides a set of methods, adapted to their specific clauses, that can be used in a fluent syntax chaining fashion to simplify the syntax and usage.

The second thing is that all commands carry a 'Parameters' property, whose job is to maintain the collection of agnostic parameters this command uses. In the general case you don't need to use this property, as the parsers populate it as needed, but it worth mentioning it.

And the third important thing regarding commands is to realize that creating a command does not execute it in any way. Command creation and execution are completely different operations. It is a common mistake to write something like this:

var cmd = link.Insert(...);

... and forget to enumerate it, or forget to use its 'First()', 'Last()', 'ToList()' or 'ToArray()' extension methods, or forget to execute it by using its 'Execute()' method. If you only create a command, well, you just have an instance of an object containing the specifications of that command, but it does not mean it has touched the database yet.

Apart of using the methods provided by your brand new command to specify its contents, another thing you can do with it is to obtain, at any time, a trace string by using its 'TraceString()' method. It returns the actual text of the command to be executed, based upon the current contents specified for the command, followed by the list of parameters captured on its 'Parameters' property.

Enumerable Commands

Enumerable commands are those able to iterate through the set of records produced as the result of the command execution. They implement the 'IKEnumerable' interface that, among other things, permits the library to instantiate the appropriate 'enumerator' instance for them. These enumerators are objects that implement the 'IKEnumerator' interface, and their job is to execute and enumerate the command regardless if the link is a direct one, a WCF one, and so forth.

The first interesting thing to mention regarding enumerable commands is that there are some extension methods that can simplify their usage: the aforementioned 'First()', 'Last()', 'ToList()' and 'ToArray()' ones, that return the results that their names imply.

Note that the 'Last()' extension method is provided just as a last resort mechanism. Its internal implementation gets and discards all possible records until the last one is found, so you may really want to reconsider your command's logic instead of using this method.

The second interesting thing to mention is the 'ConvertBy()' extension method that permits to set, in a fluent syntax fashion, the converter to use. As mentioned, a converter is a delegate executed each iteration of the command to convert the record obtained to whatever object the delegate wants to return. For instance, let's remember the example provided in the introductory article:

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

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

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

What happens behind the scenes is that the enumerator maintains a 'CurrentRecord' property that gives access to the record returned by the current iteration. When the getter of 'Current' property is used, Kerosene ORM calls that delegate if it has not been called already in the current iteration. Then the 'Current' property is set internally with the result of invoking that delegate, avoiding calling it twice in the same iteration. This architecture is by design for performance and memory consumption reasons.

Executable Commands

Executable commands are those able to execute the command and return an integer as its result - that, typically, but not always, is the number of records affected by that execution. These commands implement the 'IKExecutable' interface that, among other things, permits the library to obtain the 'IKExecutor' object that will be the one that, ultimately, will execute the command and obtain its result.

Their 'Execute()' extension method is used, as its name implies, to execute the command and to return the integer that results from that execution, using a fluent and convenient syntax.

Records

We will finish our tour on the Kerosene ORM core elements and concepts taking a look at the 'records'. These objects implement the 'IKRecord' interface and they are the ones that, by default, are returned by the library when an enumerable command is executed.

They are dynamic objects so that we can access their contents using a dynamic syntax. We can write something like what follows:

var cmd = ...; // whatever enumerable command
var rec = cmd.First(); // getting the first record only

dynamic r = rec; // to use the dynamic syntax
Console.WriteLine("\n> {0}, {1}: {2}",
   r.Emp.Id, r.LastName, r.Ctry.Id);

Obviously an exception will be thrown if the record does not contain the columns referenced. Note also that we can express the 'Table.Column' combination using a natural syntax and that, in the example, we have used the aliases defined in the command instead of using the longer table names. If there is no need to use a table name or alias, we can simply use the column name.

Note that you pay a price when you use dynamics. They are quite convenient but you loose IntelliSense code completion support, and there is a hit on performance due the late bound mechanism involved. For these reasons records also support an indexed access syntax, as follows:

...
Console.WriteLine("\n> {0}, {1}: {2}",
   r["Emp", "Id"],
   r["LastName"],
   r["Ctry", "Id"]);

Now, how can we know the structure of a given record? Easy, by using its 'Schema' property.

In the general case this property is automatically set by the enumerator when the command is executed. There is one caveat, though. It is really a border case but, when records are serialized, and for performance reasons, their schema is not serialized by default along with the records themselves. The idea is that we firstly serialize the schema and then we serialize the records without it... saving a ton of data bandwidth and improving performance. Then, when deserialized, we manually set their 'Schema' property with this instance deserialized previously.

This feature is heavily used in, for instance, WCF connection scenarios. Chances are high that you will never encounter yourself with the need to take these considerations into account, but at least I have seen once a scenario in which the records are serialized into a file and, in this case, you need to be aware of this feature. It might be also helpful if you are dealing with JSON.

Use Cases

This section provides a deep explanation on the different command types, their methods, and a number of samples of use cases where they can be used.

To get the most out from what follows, let's firstly describe the scenario that we will use in our examples. Let's suppose we are dealing with a (minimalist) HR system composed by just three tables:

DB diagram

The three of them have an 'Id' column - but this happened completely by chance! Kerosene ORM does not require you to use any given magic names, nor it forces you to follow any conventions for them, or to configure the names used for the primary key or identity columns. Actually, you don't even have to know that such primary key or unique valued columns exist, or maybe they do not, or what would be their names and types. Remember that, in the Dynamic Records operational mode, Kerosene ORM adapts itself dynamically to whatever structure your databases may have, and that in this mode the existence of such identity columns is not even required.

As a side note, when using the Entity Maps operational mode, Kerosene ORM does indeed require that the primary table of an entity has either a primary key column (or several) or an unique valued column (or several). But, again, that's it as the library will identify and use them as appropriate. But I don't want to digress here and I'd rather encourage you to take a look at the "In Depth" article regarding "Entity Maps" that is mentioned in the introductory one.

To make things a bit more interesting there are a number of references and self references among the tables: each employee can be associated with its manager through the 'ManagerId' column, which may be null if it has no manager, and also it must be associated with a given country though the 'CountryId' column; each country must be associated with a given region through the 'RegionId' column; and finally each region can be associated with its parent (or super) one through the 'ParentId' column, which can be null if that region is a top-most one.

Note that the above diagram just represents our current knowledge on the structure of the database. It might not be a complete description of it and, in the general case, it won't be. Also note that we just need to have a high level understanding of the types of the columns, as Kerosene ORM will handle the appropriate type conversions for us. This is the foundation of the resilience of the library.

Query Commands

'Query' commands are the ones you use to express 'SELECT' database operations. The easiest way to create a query command is by using the 'Query()', 'From()', 'Select()' or 'With()' extension methods of your link instance.

From clause

As a first example let's see how to create a query command that involves several tables:

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

It worth to point out that Kerosene ORM uses heavily a fluent syntax chaining design by which you use consecutively the methods that specify the command contents (and this happens not only with query commands, but with all other command types as well). Also, in the general case, there are no limits on how many times you use a given method, or in what order, as Kerosene ORM merely annotates the contents specified and keep them ready for when the time comes to generate the actual text of the command.

Another interesting fact to mention is the concept of 'virtual extension methods'. These are methods that you apply to a previous element in a DLE (Dynamic Lambda Expression) - where such element is bounded to a dynamic argument of the expression. This way we can use this methods, that do no exist in advance, to express any logic we may need. For instance, in the above example, we have used the '.Alias(alias)' one to specify the alias we want these tables to be associated with.

Each 'From()' method let's us specify one source of contents, typically being a table specification (with an optional alias), but not only. For instance, let's suppose we want to use another query as the source of contents. We can write:

var other = link
   .From(x => x.Countries.As(x.Ctry))
   .Where(x => x.Ctry.Id == "sp"));

var cmd = link
   .From(x => x(other).As(x.Location))
   .From(x => x.Employees.As(x.Emp))
   ...;

When using another command as a source we need to specify an alias for its results. Because the query command class does not have an 'Alias' method, what we have done was to wrapper the source query under a direct dynamic argument invocation ('x => x(other)...'), using the rounded escape extensibility syntax mentioned above in the explanations of the core elements. Once we have done that, we have a dynamic element to which we can apply the 'Alias(alias)' virtual extension method.

We can use not only other command instances as an alternative source of contents, but also any valid SQL expression we can imagine and write. I encourage you to take a look at the samples included in the download for further information.

Where clause

We can specify the contents of the 'WHERE' clause by using one or many 'Where()' methods. For instance:

var cmd = ...
   .Where(x => x.Emp.Id >= "1003")
   .Where(x => x.Or(x.Ctry.Id == "uk"))
   ...;

Each 'Where()' method takes a DLE argument that specifies the logic of the condition to apply resolving into a boolean value. If several 'Where()' methods are used, their contents are concatenated in the 'WHERE' clause using, by default, an 'AND' operator. If this is not what you want, you can use the 'And()' or the 'Or()' virtual extension methods to express how the new contents shall be concatenated with the previous ones, if any. This is the approach we took in the second line of the above example.

Of course having the capability of using several 'Where()' methods does not mean that you must use it. The above 'WHERE' clause could have been also written as:

var cmd = ...
   .Where(x => x.Emp.Id >= "1003" || x.Ctry.Id == "uk")
   ...;

It doesn't matter what syntax you use. Kerosene ORM just captures these contents and uses them when there is the need of generate the actual SQL command text. These considerations apply to almost all other methods in the query command, and on the other command types as well.

Complex Tags

So far we have used single-character tags (a tag being the name of the dynamic argument: the 'x' in 'x => ...'). By default these tags are removed from the expressions when they are parsed. This was the only mode supported by previous versions of Kerosene ORM.

The library does now include support for the so-called 'complex tags'. A tag is considered 'complex' when its name has more than one character. In this the situation then now, by default, Kerosene ORM will not remove it from the parsed expression. For instance:

var cmd = ...
   .From(x => x.Employees.As(x.Emp))
   .Where(emp => emp.LastName >= "P")
   ...;

In this example the 'Where()' method uses a DLE whose dynamic argument is named 'emp' - that happens to be the alias defined in the 'From()' clause (but this is not really relevant for this discussion). What is relevant is that the tag's name is not ignored as it has more than one character and hence it is considered as a 'complex tag'. The equivalent syntax using 'standard tags' would have been:

var cmd = ...
   .From(x => x.Employees.As(x.Emp))
   .Where(x => x.Emp.LastName >= "P")
   ...;

... and whether you use one form or the other is just really a matter of personal taste. You can set whether to follow this new convention, or not, using the 'KeepComplexTags' property of the static 'IKParserHelper' class.

Select clause

The 'Select()' method let's us specify the contents of the 'SELECT' clause. For instance:

var cmd = ...
   .Select(x => x.Emp.Id, x => x.Emp.FirstName, x => x.Emp.LastName)
   .Select(x => x.Ctry.All())
   ...;

As you can now imagine you can use as many 'Select()' methods as you wish or need. Each takes a variable list of DLEs specifying, in principle, a given 'column', or 'table.column'. As happened with the 'From()' method you can also specify any external command or any valid SQL expression, so I won't repeat that discussion here again.

What it is interesting to mention instead is the '.All()' virtual extension method. When applied to a table-alike specification it instructs Kerosene ORM to include in the 'SELECT' clause all columns from the table specification it is applied to.

Again it is a matter of personal taste whether to use this extension method or the square or rounded escape syntax facilities. For instance, you can also specify selecting all columns from a table using any of the following constructions:

var cmd = ...
   .Select(x => x.Emp[".*"])
   .Select(x => x.Ctry.x(".*"))
   ...;

Distinc clause

The easiest way of including a 'SELECT DISTINCT' clause is by using the 'Distinct()' method, as follows:

var cmd = ...
   .Distinct()
   ...;

You can also use the aforementioned escape syntax, bla, bla, bla, ... and I will not bore you by repeating it all again.

The IN and NOT IN operators

As mentioned above in the 'parser' explanation, within the 'WHERE' clause Kerosene ORM supports the 'In()' and the 'NotIn()' virtual extension methods. For instance:

var cmd = ...
   .From(x => x.Employees)
   .Where(x => x.Id.In("2001", "2002", "2003", ...)
   ...;

You can use as many arguments as you need in these virtual extension methods, and each argument can be a value (as happened in the example) or any valid SQL statement you need to write. Kerosene ORM will parse each argument in turn (unless they are wrapped using a escape syntax, in which case they are injected as-is in the resulting string).

Joins

Query commands also have a 'Join()' method. Again, you can use as many ones as you need. Each takes a DLE that express the contents of their 'JOIN' clause. For instance:

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

The first form builds a standard 'JOIN' clause using the table to join to, an optional alias specification for that table, and the join condition by using the 'On()' virtual extension method.

The second form is used to specify the concrete join-alike operation we want to execute. It uses a specialized escape syntax in which the 'x(op, spec)' construction takes two parameters, the first one being a string that is injected as-is instead of the default 'JOIN' one, and a second parameter that specifies the contents of the join clause, using the same syntax as the one used in the first form.

Group By and Having

Yes, there is a 'GroupBy()' method available for query commands:

var cmd = ...
   .GroupBy(x => x.Whatever)
   ...;

If you use the 'GroupBy()' method then you can also use the 'Having()' one:

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

Its argument follows the same rules and considerations as the argument of the 'WHERE' clause, so I won't repeat that discussion here.

Order By

No surprises: query commands have an 'OrderBy()' method, and you can use as many as you want or need. Each one take a variable list of DLEs specifying the column by which the ordering shall take place:

var cmd = ...
   .OrderBy(x => x.Emp.CountryId, x => x.Emp.LastName, ...)
   .OrderBy(x => x.Emp.ManagerId.Desc(), ...)
   ...;

Unless otherwise specified the default ordering is an 'ascending' one. To explicitly set the ordering to use you can use one out of the following virtual extension methods: 'Ascending()', 'Asc()', 'Descending()' or 'Desc()', and apply it to the column specification you are interested in.

Top, Skip and Take

Query commands do support the 'Top()', 'Skip()' and 'Take()' methods. All of them take an 'int' as its parameter, and all of them interpret negative values as a signal to clear their respective clauses.

'Top()' is, well, quite easy to use (as in 'x => ...Top(top)'). You can use several 'Top()' methods if you wish, but the last one used wins. If a 'Top()' method is used then the contents of the skip/take ones are cleared. There is really nothing more to say about it.

Regarding 'Skip()' and 'Take()' some database versions implement a normalized syntax to support these capabilities, assuming some conditions are met regarding the contents of the command. Most old databases do not provide such capability, and must be emulated using convoluted SQL syntaxes.

The engine's 'NormalizedSkipTake' property provides an indication on whether the underlying database engine supports such syntax or not. Similarly, the query command's 'ValidNormalizedSkipTake' property returns whether, assuming that the engine does support the skip/take capability, the current contents of the command meet the conditions needed to implement it.

For instance, a generic SQL engine, and even the 2008 customized version, will return 'false' for their 'NormalizedSkipTake' property, whereas the 2012 MS SQL Server customized engine returns 'true'. In the later case, the 'ValidNormalizedSkipTake' property of a query command associated with a 2012 engine may return 'true' if, and only if, its 'ORDER BY' clause is not empty.

If that happens then Kerosene ORM will use the database's specific syntax to implement the skip/take capability. This is, as you can imagine, the most optimized way of doing so. If not, Kerosene ORM emulates these capabilities by software. But this is considered as a fall back mechanism because its internal implementation firstly gets and discards the 'skip' records and then, if there are still records available, iterates through the next 'take' ones at most. So either you custom engine supports a specific syntax, or either your command's contents shall be reconsidered (probably using the escape syntax) to avoid using this fallback implementation.

The download of this article includes customized engine adapters for MS SQL Server 2008 and 2012 databases. Out of these, only the later provides support for the normalized skip/take syntax we have discussed. You can use this library as a template to build your own custom engine adapter is you need it.

CTEs and the With clause

This version of Kerosene ORM is the first one that provides support for the 'WITH' clause, also known as 'Complex Table Expressions'. The easiest way of using them is by creating a new query command using the link's 'With()' extension method:

var other = ...;
var cmd = link
   .With(x => x.AliasName, other)
   ...;

The first argument is a DLE that specifies the mandatory alias for the 'WITH' clause. The second argument is the source of contents for this clause, and it can be an external command (as happens in the example), or any valid SQL expression for this clause.

Insert Commands

'Insert' commands are the ones you use to express 'INSERT' database operations. The easiest way of creating them is by using the link's 'Insert()' extension method that takes a DLE (Dynamic Lambda Expression) that resolves into the name of the table affected by this command:

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

Once we have this command created we can use its 'Columns()' method to specify the columns and values of the record to be inserted into the database:

var cmd = link
   .Insert(x => x.Employees)
   .Columns(
      x => x.Id = "007",
      x => x.FirstName = "James",
      x => x.LastName = "Bond",
      x => x.CountryId = "uk",
      x => x.Photo = new byte[] { 0, 0, 7, ...});

This method takes a variable list of DLEs each specifying a given column, and its value, using an assignation syntax. Note that, in the right side of the assignation, you can use both any valid value, or any valid SQL expression if you wish.

Let me remember again here that just creating the command means nothing in terms of affecting the database. You must either enumerate the command, or execute it, in order to persist these changes into the database.

Delete Commands

'Delete' commands are the ones you use to express 'DELETE' database operations. The easiest way of creating them is by using the link's 'Delete()' extension method, that also takes a DLE that resolves into the name of the table to be affected by this operation:

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

Then, the 'Where()' method can be used to filter the concrete records that will be deleted. Note that if you don't use a 'Where()' method then all records in the database will be deleted. Yes, this is by design, Kerosene ORM treats you as a grown-up software deleveloper so it might be precisely what you wanted.

Again, you must either enumerate the command or execute it to persist these changes into the database.

Update Commands

'Update' commands are the ones you use to express 'UPDATE' database operations. The easiest way of creating them is by using the link's 'Update()' extension method:

var cmd = link.
   .Update(x => x.Employees)
   .Where(x => x.Id == "007")
   .Columns(x => x.ManagerId = "M");

Again, you may want to use the 'Where()' method to filter what records will be affected. If you don't use any, then all records will.

And, similarly to what happens with the 'Insert' commands, you can use the 'Columns()' method to specify the columns to be updated.

Raw Commands

'Raw' commands are the ones you use when you want to execute database operations whose specifications are not covered by any of the commands we have seen so far. Being more precise, they permit you to specify explicitly the concrete SQL text the command will execute.

The easiest way of creating raw commands is by using the 'Raw()' extension method of your link instance:

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

There are several overrides for this extension method. The one we have used in the example takes, as its first argument, a string containing the SQL text to execute, where the command's arguments are specified using the positional "{n}" syntax. Then, it accepts a variable list of arguments each representing the value of these parameters.

You could have also used a DLE to express in one shot both the text and parameters of the command.

Note that, when using raw commands, Kerosene ORM has not any a priori indication on whether the command is going to be enumerated or just executed. In the example above our intention was to get back the record inserted, doing so by enumerating the command. So, we needed to explicitly include an 'OUTPUT INSERTED.*' construction, or otherwise the command enumeration will fail.

Raw commands can be very useful when we want to execute stored procedures. The following example shows how to do that in the test database used by the sample applications provided in the download:

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

Transactions

As mentioned in the previous general discussion, each link object carries a 'Transaction' property holding an object that can be used to start, commit and abort nestable transactions on that link. The way we can use this property is as easy as follows:

try {
   link.Transaction.Start();
   ...; // execute a command
   ...; // execute another command
   ...; // etc
   link.Transaction.Commit();
}
catch { link.Transaction.Abort(); }

As this object supports 'nestable' transactions, you don't need to bother with if it was already started or not. You don't have also to bother with opening the underlying database connection, or closing it afterwards, as it will be handled automatically by Kerosene ORM.

Now, in a direct connection scenario, our specialized 'IKLinkDirect' link has a 'Connection' property that holds the underlying 'IDbConnection' object this link is using. We may be tempted to use it to have complete control of what's going on, which is, absolutely, a valid way of using it. But we then need to remember that this property might be null if the link is not 'opened' yet. So the correct way of using it is as follows:

IKLinkDirect link = ...;
IDbTransaction tran = null;
try {
   link.Open();
   tran = link.Connection.BeginTransaction();
   ...; // execute a command
   ...; // execute another command
   tran.Commit();
}
catch { tran.Rollback(); }
finally { link.Close(); }

Finally, unless you are in a WCF connection scenario, you can also use a transaction scope if you wish so. The appropriate way of using it is as follows:

using(TransactionScope scope = new TransactionScope())
{
   ...; // execute a command
   ...; // execute another command
   scope.Complete();
}

Why not in a WCF connection scenario? Firstly because, by design, the client has no information and no visibility on what would be hidden behind the WCF server service. Secondly, because in this scenario we don't really want to allow that what happens with a client affect all other clients the server may be connected to at any given time. So, any exception at the server is bubbled up to the specific client affected by it, without interfering with any other clients.

Nested Readers

What this section contains is really a border case and, if you find yourself involved in this kind of scenario, you may really want to consider using Entity Maps instead.

Let's suppose you have a business class named 'Country' that 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 countries. One way of doing so is as follows:

var cmdCtry = link.From(x => x.Countries.As(x.Ctry));

foreach(Country ctry in cmdCtry.ConvertBy(recCtry =>
{
   dynamic c = recCtry; Country objCtry = new Country() {
      Id = c.Id,
      Name = c.Name,
      RegionId = c.RegionId
   };
   
   var cmdEmp = link.From(x => x.Employees).Where(x => x.CountryId == objCtry.Id);
   
   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);

In this example we are using inline delegates for simplicity but you could have used external defined ones if you wished so, of course.

As you can see we are using a nested loop on the 'cmdEmp' commands inside the out-most loop on the 'cmdCtry' one. For this code to work your connection must be configured to support multiple simultaneous result sets.

Nested Updates and Database Constraints

Let's now suppose that what you want to do is to modify the primary key of one of your countries. Opps, not only we are dealing with a primary key (whose modification is not even typically supported by other ORM solutions), but also we need to keep track and modify the foreign key columns in the tables that may have a dependency on that primary one. While the database constrains are active there is no way of achieving that without receiving back an error.

So, how can we get it done? One possible way is as follows:

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();

Again, the basic idea is to execute a nested loop on the employee records inside an external one on the country ones. In our case, the commands that we are executing are update ones, instead of query ones, but otherwise the structure is almost identical to the one used in the previous example.

Finally note that I have used a single raw command to de-activate the constraints and to re-active them after the execution of the command. Because it pleases my economy of resources taste I have reused that command instance four times, but you can use brand new objects each time if you wish.

I have not included in this example any transaction-related code, but it will be absolutely needed in a production scenario. I leave it as an exercise to the reader.

What else?

Now that you have complete information about this 'Dynamic Records' operational mode you may want to take a look at the "In Depth" entity maps tutorial. To do so please refer to the link that is given in the introductory article.

License

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

Share

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

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141220.1 | Last Updated 11 Mar 2014
Article Copyright 2014 by Moises Barba
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid