Kerosene Basics

Using Kerosene is extremely easy. It has been designed on purpose to let you forget all those details and nuances that, otherwise, you have to take into consideration when using other ORMs. This tutorial will guide you through the "Core" mode of operation of Kerosene and the most important components it uses.


Example Scenario

To easy the discussion that follows, let´s assume you are dealing with a (minimalist) HR system composed by three tables: Employees, Countries and Regions, as shown below:



To make things a bit more interesting each Employee belongs to a given Country through its not null "CountryId" column, and can have an optional manager if its "ManagerId" column is not null. Each Country belongs to a given Region through its "RegionId" column, which must not be null. And each Region can belong a a given super-Region if its "ParentId" column is not null.

Some comments about the scenario

The first thing to note is that Kerosene makes no differences between tables and views. So the above tables can be views if you prefer this approach (for instance for security reasons). It is up to you to specify the appropriate connection string to access the tables or views you want to use.

The second thing to note is that it happens, by chance, that all those three tables contain a column named "Id". We have used this name just for our convenience: we were thinking in terms of our business logic without paying attention to what requirements might the future ORM tool impose to our design.

The nice thing is that Kerosene makes no such requirements. It does not need any special or magic column names, nor does it need to avoid name collisions. You can use any names you wish, or have been used by whomever created the database. The column names don't even need to compulsory match the names of properties and fields on your business classes (as far as you are prepared to write a small converter, but we will see these customizations later).

What's more: unlilke other popular frameworks and tools Kerosene does not even require you to know in advance the complete schema of your database. Not even what columns are your primary key ones... if any exists. Yes, if your tables do not contain any primary columns you will still be able to use the Kerosene's "Core" mode without problems.


Your First Query

Let's analyze again the example we used int the home page's introduction:

var link = new KLinkDirectSQL( "... your connection string here ..." );
var cmd = link.From( x => x.Employees ).Where( x => x.LastName >= "C" );
foreach( var obj in cmd ) Console.WriteLine( "Returned record: {0}", obj );
  • The first line creates a Link object. Links are used to maintain all the details about how to connect to an underlying database, how to open and close the connections when needed, how to interact with the transactional mechanism, and so forth.
  • In this example we have created an instance of a specialized Link adapted for Microsoft SQL Server databases - but note that Kerosene is agnostic, and it is built to be extended with support for other databases as needed.
  • The specific link we have instantiated is a "direct" one, meaning it will access directly a physical database by virtue of using our old friend the connection string. Yes, behind the scenes the Kerosene's "Core" mode uses ADO.NET as it has no sense to reinvent the wheel.
  • The second line creates a Query command. A command is just a class that helps you to write the specific SQL command you want to use in a handy way. As expected, there are also command classes for the Insert, Delete and Update operations. If, despite the dynamic and extensible syntax we will see later, you rather wish to write your own code in plain text, don't worry, a Raw command class is available specifically for those circumstances (and it is very handy if, for instance, you want to execute store procedures).
  • Note also how we have used a fluent syntax: chaining several methods to compose our command. The first method is an extension method of the IKLink interface, which all Link objects implement. Then, all the command classes are built to permit the chaining of their methods to achieve this fluent syntax.
  • Inside those methods we have used dynamic lambda expressions to express both the table and the logic condition. A "dynamic lambda expression" is defined as a lambda expression where at least one of its parameters is a C# dynamic one. Most methods in Kerosene take just one parameter of this form.
  • As those dynamic lambda expressions are, well, dynamic, they use the late-binding mechanim C# dynamics are built for. This fact allow us to write whatever expression we wish without receiving any complaints from the compiler, and this is why we are able to write any SQL-like expression in plain C# code - for instance take a look at the comparison between the two string-like objects in the example above, something the C# compiler was not supposed to allow!
  • As expected, the parameters found in the expression are extracted and stored for future use, in order to avoid injection attacks by design. This is what happens, for instance, with the "C" string in the example.
  • The third line executes your command and, in this case, enumerates through its results. By default Kerosene returns those results in the form of "dynamic records", instances of the specialized class KRecord which is a dynamic class that will adapt itself to whatever columns are returned. Being dynamic it will let you access them by using a dynamic syntax, or an indexed one if you feel more comfortable. In this example we have merely printed out those records - we will see later how to manipulate them.

So far so good. A lot of things in just three lines. Although the above are, actually, the most important things to understand in order to use Kerosene, what follows will provide you with a broader vision in order to allow you to face more complex scenarios and to understand a bit of what’s going on behind the scenes.


Your Second Query

Let's move on with a slightly more complex example:

var cmd = link
   .From( x => x.Employees.As( x.Emp ) )
   .From( x => x.Countries.As( x.Ctry ) )
   .Where( x => x.Emp.LastName == "C" )
   .Where( x => x.Or( x.Emp.Id != null ) )
   .Select( x => x.Ctry.All() )
   .Select( x => x.Emp.Id, x => x.Emp.FirstName, x => Employees.LastName );

foreach( dynamic obj in cmd ) Console.WriteLine(
   "Id:{0} First Name:{1} Last Name:{2}",
   obj.Emp.Id, obj["FirstName"], obj.Employees.LastName );

In this example we have also used a Query command, but in this case we are going to fetch contents from several tables. We have specified those tables by chaining multiple From(...) methods. This is a common topic in Kerosene: you can chain its methods as many times you need and in whatever order, Kerosene will combine them and produce the appropriate SQL code for you.

Let's know analyze the command we wrote in the above example:
  • As the "Id" column appears in the two tables used we have needed to use aliases to qualify them. We have used the As(...) "dynamic extension method" attached to the table specification to assign the alias associated with that table. A "dynamic extension method" is defined as a virtual method attached to a dynamic argument, and hence parsed by Kerosene when needed. Afterwards, we have used the "x.Table.Column" form to qualify those columns when writting the Where conditions.
  • We have also chained several Where(...) methods. By default they are combined using an AND logical operator. But note that, in our example, we have used the Or(...) dynamic extension method to combine them using an OR logical operator. Note also that, whereas the As(...) method is available all across Kerosene, the And(...) and Or(...) ones are specific for the Where clauses only.
  • Finally we have used two Select(...) methods to specify the columns we want to obtain. The first one uses the All() dynamic extension method to specify that we want all the columns from the table it is attached to. The second form let us specify a list of column specifications to add to the Select clause.

Note that, by design, Kerosene will not generate any code you have not written specifically. There will be no surprises and no fat code injected without your control. What's more, Kerosene will not, ever, execute anything you have not told it explicitly to - not even transactions: as they are a critical element of your application's logic, and even if it would be an interesting feature, you will need to manage them explicitly. The only exception to this rule is that, if there is not an active connection when the command gets executed, a connection is created, opened, and closed afterwards on your behalf.

If you are interested in obtain the actual SQL code this command will execute you can obtain it using its CommandText() method. It takes a boolean argument that specifies whether you want the iterable version of it, or not (it has no effect in Query commands, but it is a handly feature for the Insert, Delete and Update ones). If you want to append to it the parameters the command will use you can use the TraceString(...) method instead. So the above command will produce the SQL code shown below:

SELECT Ctry.*, Emp.Id, Emp.FirstName, Employees.LastName FROM Employees AS Emp, Countries AS Ctry WHERE (( Emp.LastName = @p0 ) OR ( Emp.Id IS NOT NULL ))

Using a SQL-like syntax

One among the major ideas behing Kerosene was to decrease the mismatch between SQL code and C# one. That's why Kerosene uses heavily dinamic lambda expressions: because they permit us to write SQL-like code (despite it might not be completely correct from a C#'s compiler perspective), parse it, and translate it into the specific SQL dialect our database will be expecting.

This is why we were able to write "x => x.FirstName >= "C"" that, you will surely agree, is more natural and closer to the SQL syntax that its pure-C# equivalent (that in other frameworks might have needed special methods applied to the "C" string). This is possible because, by using dynamics, we are implicitly using late-binding and so postponing the validation of the existence of methods and properties till run-time, which is precissely what we want in this case.

We can basically use any valid C# expression with its own operator and it will get translated into the appropriate SQL code. The properties of the dynamic argument, as in "x.Column" or in "x.Table.Column", will get translated into column names (with or without the Table/Alias qualifier). The C# variables or plain methods are captured when the expression is parsed and the values converted into parameters passed to the SQL command.

Extending the Dynamic Syntax

The methods that are applied to the dynamic arguments (or to their dynamic "properties") are used to extend the dynamic syntax supported by Kerosene, and to adapt to whatever changes in the syntax the various databases may experiment in the future.

How it works? If something appears in the expression that Kerosene does not understand, it supposes you know what you are doing, and translates it straight into SQL code. For instance, Kerosene does not know that your SQL database might have a built-in "Count" function, so if it encounters the following code:

var cmd = link.From( x => x.Employees ).Select( x => x.Count( x.Id ).As( x.SumOfEmployees ) );

it gets translated into the following SQL-code:

SELECT Count( Id ) AS SumOfEmployees FROM Employees
  • The Count method is translated straight into a Count function in the SQL code, as it has not been intercepted by the Kerosene's parsing engine. Its arguments are parsed and passed as the SQL's function arguments.
  • As Kerosene requires all columns returned have a name (it does not support unnamed columns) we have used an Alias(...) dynamic extension method to give a name to the column that will maintain the result of the "Count" function.

Now, for your convenience, Kerosene will intercept some constructs in order to easy the way you write your dynamic lambda expressions. They typically take the form of dynamic extension methods attached to specific columns (properties) as can be seen in the following example:

var cmd = link
   .From( x => x.Employees.As( x.Emp ) )
   .Where( x => x.Emp.BirthDate.Year() >= 1970 );

which gets translated into:

SELECT * FROM Employees AS Emp WHERE ( DATEPART( YEAR, Emp.BirthDate ) >= @p0 )

In this case the "Year()" extension method has been intercepted by the parsing engine that interprets that the column it is attached to is of type "date", and will produced the equivalent SQL code on your behalf. You could have written the "Datepart" function directly if you wished so: this is completely an optional mechanism and if you don't feel comfortable with it, or if you are a purist, you don't need to use it and you can revert to the standard way.

As you can imagine, the parsing engine we have used in this example, the one for Microsoft SQL databases, also supports the "Month()", "Day()", "Hour()", "Minute()", "Second()", "Millisecond()" and "Offset()" ones to use with "date" or "time" types. It also supports other dynamic extension methods for other SQL types, as: "Left()" and "Right()", "Len()", "Lower()" and "Upper()", "Like()", "NotLike()", "Contains()", "PatIndex()" and "SubString()", "LTrim()", "RTrim()" and "Trim()", and "Cast()", with the usage and number of arguments you can expect. Other parsing engines adapted for other databases may choose the methods they would like to intercept depending upon the specific functions the database supports.

Escaping from the Parsing Engine

No library can cope with all possible database versions, syntaxes, command's options, and possible changes in the future for all of those, without taking an exorbitant amount of time. So Kerosene provides ways to include any arbitrary text in the lambda expression if you need so.

This mechanism is activated any time you write a "direct invocation of your dynamic argument", meaning any expression of the form "x => x( ... )". In this case, the parsing engine will merely concatenate with spaces the arguments it finds inside the parentheses. If any of those arguments is a string, this is the only case where this string is not parameterized but rather copied straight into the SQL code. Any other kind of argument is parsed as usual.

For instance, one among the clearest examples is the Group By clause. It accepts multiple options and variants in each database version, so hard coding and using properly all of them would be a nightmare. Thus the easiest way is to use this escaping mechanism as shown in the next example:

var cmd = link ... GroupBy( x => x( x.CountryId, "HAVING", x.CountryId >= "us" ) );

which gets translated into:

... GROUP BY CountryId HAVING ( CountryId => @p0 )

Yes, you could write your whole expressions in plain tex, but then it would be harder to extract the parameters in an easy and handy way,

Enumerating the Results

Well, it is time now to move on and understand how can we access the results returned. As we were using a Query command we do expect to enumerate through the results, and hence why we have used a foreach loop.

Each iteration will produce, by default, an instance of the mentioned KRecord class, which permits us to access its "columns" using either a dynamic or an indexed syntax, as we can see in the following example:

foreach( dynamic obj in cmd )
   Console.WriteLine( "\n> Generated => {0}: {1} {2}",
      obj.Employees.Id, // Using the table name
      obj["Emp","FirstName"], // Using the table alias with an indexed syntax
      obj.LastName ); // Using the "default" table as there are no column name collisions

Using dynamics is more easy and handy, and in more complex scenarios it will take care of type conversions on your behalf. On the flip side, the indexed syntax is slightly faster, and in data intensive operations you may want to choose this way.

Query commands are just one case of enumerable commands (you already know who are the other usual suspects: Insert, Delete, Update, and Raw commands). All of them implement the IKCommandEnumerable interface which can be used with the following extension methods:
  • ToList(), that returns a list (potentially empty) containing all the records returned from the database, so it might be very intensive in memory.
  • ToArray(), that returns an array (potentially empty) containing all the records returned from the database, so it might be very intensive in memory.
  • First(), that returns the first objects as it was returned from the database, or NULL if there are no records returned.
  • Last(), that returns the last one or NULL. Note that the generic implementation of this method gathers all the records from the database discarding them until the last one is found, so potentially implying a lot of network traffic. The assumption is that either you rewrite your command with a different sort order, or you override this method for your specific database.
  • SkipTake(), that discards the first "skip" records and then permits to enumerate at most the next "take" ones, or null if there are not more records available. The same considerations mentioned for the Last() method apply here.

For instance, to find the first occurrence from a query you can use:

var cmd = link.From( x => x.Employees ).Where( x => x.LastName >= "Smith" ).OrderBy( x => x.Id );
var obj = cmd.First();

Results can be NULL

Unlike other ORM frameworks Kerosene will return null when there are no more available records, or when no records are found. But it does not raise any exceptions in those circumstances. In Kerosene exceptions are only raised when something went wrong – and, in my modest opinion, an empty result set is not wrong per-se, but conceptually a very valid possible result.

Records are forgotten

It is also important to note that, in this "Core" mode of operation, once a record is returned from the database it is just "forgotten": Kerosene won’t keep track of them. It is the application’s responsibility to do so if needed. The rationale behind this approach is that, when using this "Core" mode of operation, you know what you are doing and you will deal with the records in the most appropriate manner. In this case, Kerosone’s responsibility is just to provide with an easy way to interact with the persistence layer.

On the flip side, if you are using the "Maps" Entity Framework included in Kerosene this won't be true: Kerosene will keep track of your entities and their states, letting you operate with them in a natural and handy way. You can find more about "Maps" here: Kerosene Maps.

Non-Enumerable commands

Basically all command (except Query ones) objects in Kerosene implement the IKCommandExecutable interface. This interface just define one method, Execute(), whose mission is to execute the command and return the number of rows affected.

For instance, to delete a set of employees you could use the following code to check how many records were deleted:

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

Some More Deeper Details

Let's now move on and dive into some deeper details you may want to know.

Parameters and Kerosene

We have already talked a bit about parameters: their values are extracted from the dynamic lambda expressions parsed, an instance of the class KParameter is created to hold this value, and the original value is substituted with the Name of the new parameter. Kerosene uses this class to manage those parameters in an agnostic way, not dependent on the specific underlying database.

Each command object has the Parameters property that maintains the collection of the parameters used in this command. You can add more parameters, remove them, and manipulate the collection if you need so. When the time comes, Kerosene will use them to inject into the actual database's command these parameters.

There are two exceptions to this mechanism. We have already seen the escape syntax, by which we can include any arbitrary string in an expression without it being parameterized. The second exception is about null values. They are treated in a special way from the perspective of the SQL syntax. So when a null value is found it is translated into the appropriate "NULL", "IS NULL", or "IS NOT NULL" literals depending upon the circumstances.

Another thing to mention is how those values stored in the generic parameters are translated into the appropriate types understood by your database: it may very well happen that the types you are using in your application have no correspondence with the types supported by your database. But no panic: Kerosene provides you with the parameters transformer mechanism. You can register in your link object what delegate to invoke when a parameter of a given type is found to transform it into whatever type your database understand.

As an example let's suppose you have a ClockTime class you are using to track specific moments in a 24-hrs clock (and that you are using instead of C#'s DateTime class for whatever reason). Let's also suppose that your Employees table contains a column named "ShiftStart", of type time, that contains the time the employee’s shift starts. If you want to find all the employees that work in the late shift you would like to use something like what follows:

var start = new ClockTime( 16, 0, 0 );
var cmd = link.From( x => x.Employees ).Where( x => x.ShiftStart >= start );

As you can expect it will fail because the underlying ADO.NET engine doesn't know what to do with the instance of your own type, the ClockTime class. In this case the solution is to use the mentioned mechanism to register a transformer to your parameter class:

link.AddParameterTransformer<ClockTime>( x => x.ToString() );

The AddParameterTransformer<T>(...) method merely annotates what delegate Kerosene needs to invoke to transform the parameters of type "T" into whatever thing is accepted by your database. In the example, we are transforming our type into a string, just for simplicity, but you can create any instance of any C# type that ADO.NET is able to understand.

Obviously it is a good idea to register such delegates in advance, typically either in the declaration of your own link class, if you will use many instances, or otherwise manually when its singleton instance is created.

A final note: it is also a good idea to return serializable transformed values. The reason is that in advanced scenarios, as WCF ones, this collection of parameters will be serialized and sent to the WCF service, so if any value is not serializable then you will receive WCF exceptions. See Kerosene WCF for more details.

Transactions

As mentioned before, Kerosene will not wrap the execution of any command inside a transaction. This is done by design: even if it would be an interesting feature, it would introduce a critical element beyond your control – and so, following the spirit of simplicity, and the spirit of letting you to specify exactly what you want to do, it will not initiate any transactions unless you tell it specifically to do so.

Any IKLink instance provides you with the TransactionStart(), TransactionCommit(), and TransactionAbort() methods. You can use these methods the way you are expecting to, as in the following example:

link.TransactionStart(); try {
   ... your operations ...
   link.TransactionCommit();
}
catch { link.TransactionAbort(); throw; }

The IKLink interface provides you with two additional properties. The first one, named TransactionMode, lets you control what kind of transactions you want Kerosene to use: its value can be either Database, for database transactions, or Scope, if you rather wish to use the TransactionScope mechanism. As far as there is not an active transaction you can set its value at any moment. Its default value is not defined, and it will depend on the specific implementation of your IKLink object.

The second property, named TransactionState, lets you interrogate your IKLink instance and obtain in what transactional state it is: Empty if there is no transaction active, Active if there is a transaction active, or Aborted if the last transaction used was aborted.

The default mechanism implemented by Kerosene also allows you to nest transactions as needed, by using a simple counter mechanism. If this is not enough for your needs, or if you wish to have more deep control on the specifics of the transactions to use, then you can use two mechanisms. The first one is obvious: use the TransactionScope mechanism by yourself. As Kerosene won't initiate any transaction unless you tell it explicitly to do so, you won't have to worry for rogue transactions appearing and dissapearing without your control.

The second one is by using the Connection property of your link object. But, obviously, this property is only available to "direct" link objects (those that are instantiated by using a connection string). In this case, you will need first to be sure the link object is opened, using its IsDbOpened property. If it is not opened, you can use DbOpen() to open it. Then you can use the Connection property to create and manage your own transactions. Finally, you can the use its DbClose() method to close the connection held by your link object.

Disposing your objects

Basically all Kerosene classes implement the IDisposable interface. This is because all of them will ultimately refer to an instance of a given IKLink object, and this ones may maintain unmanaged resources. So don’t forget to dispose your commands, and of course, your Link objects, as soon as you are done with them.

Regarding the latter, note that either you can create your Link objects when you are about to use them, or you can use a Singleton pattern, or mix them both. Kerosene is built in such a way that the cost of creating as many Links as you need is not high in most circumstances. But there are scenarios, typically when you are dealing with the Maps mechanism, where this cost can be noticeable (because per each map instantiation there will be a trip to the database to validate it). So, you can choose which way fits better into your needs.

Finally note that, when disposing a Link, its registered objects, as Maps or Transformers, are disposed as well. Keep this in mind if you want to reuse these objects, because you may have to clone them and register them later into another Link before disposing the original one. The good news are that all the interesting classes in Kerosene implement the ICloneable interface.

Converting your Results

So far we have seen that Kerosene operates by default returning records: instances of the KRecord class that provide with a resilient and self-adaptive mechanism able to deal with whatever schema your database may have without the need of external configuration or mapping files.

Although this mechanism is quite powerful and flexible, we all can easily agree that in many scenarios we would like to use our own business classes instead of the database-oriented records produced by default. Kerosene provides with two mechanisms to achieve this objective: converters and maps. The latter is an Entity Framework alike capability built specifically for POCO classes, whose details are covered in this specific section of the documentation: Kerosene Maps.

Let's now focus on the Converters. All enumerable commands permit you to specify a delegate that will be used to convert the record they take as its argument into whatever object you wish to return from this command. The easiest way to use this mechanism is by using the ConvertBy() extension method, as we can see in the next example:

foreach( var obj in cmd.ConvertBy( rec => {
   dynamic d = rec;
   return new {
      d.Id,
      Name = string.Format( "{0}, {1}", rec["LastName"], rec["Emp", "FirstName"] )
   };
} ) ) Console.WriteLine( "\n> Converted => {0}", obj );

In this example we have transformed the record "rec" into a new instance of an anonymous type, accessing the columns using both a dynamic syntax and an indexed one. This new type contains two properties, one named "Id" which is obtained straight from the "Id" column, and a "Name" one that is built using two columns from the record.

Similarly we could have created a new instance of our business class and load it contents using the values stored in the record passed as the argument.

There are no limits in what you can do inside the converter delegate. We could have created a new instance of a given business class instead of an anonymous one, and load its contents using the values obtained from the record. Or, if we need so, we can go again to the database to get more information to build the instance.

This is called a "nested reader", and can be used, for instance, when we have a property in our business class that we need to load from the database. Let's suppose we have a "CountryTable" business class with a property named "Employees", being a list of the employees that belong to this country. While reading the countries we can load this property by using something like the following code:

var ctryCmd = link.From( x => x.Countries ).OrderBy( x => x.Name );

foreach( var ctry in ctryCmd.ConvertBy( rec => {
   CountryTable ctryTable = new CountryTable();
   dynamic c = rec; // Handy substitution
   
   ctryTable.Id = c.Id;
   ctryTable.Name = c.Name;
   ctryTable.RegionId = c.RegionId;

   var empCmd = link.From( x => x.Employees ).Where( x => x.CountryId == ctryTable.Id );

   foreach( var emp in empCmd.ConvertBy( rec2 => {
      EmployeeTable empTable = new EmployeeTable();
      dynamic e = rec2;
      
      empTable.Id = e.Id;
      empTable.FirstName = e.FirstName;
      empTable.LastName = e.LastName;
      empTable.CountryId = e.CountryId;

      ctryTable.Employees.Add( empTable );
   } ) ) ;
   empCmd.Dispose();

   return ctryTable;
} ) )
Console.WriteLine( "\n>> Country = {0}", ctry );
ctryCmd.Dispose();

Note that, in order to use this nested readers feature, your database must support multiple concurrent results sets. In the case of the MS SQL Server version included in the download as an example, you need to add a "MultipleActiveResultSets=true" in the connection string.

Nested readers can be used not only with Query operations, but with the other Insert, Delete, Update and Raw ones as well. In these cases a note of caution apply: if you are modifying the key columns, as there might be referential integrity rules enforced in your database, it may happen that you will receive errors. Kerosene assumes you know what you are doing, and so it does not try to impede you for doing such operations – but in order them to work you may need to suspend those constraints and to reactivate them later. Again, this is very database dependent, and I encourage you to take a look at the examples provided.

Command Objects in Kerosene

A command is just a specialized object that will help you to write what operation you want to execute against your database. Their mission is to annotate and maintain all the details they will need to generate the appropriate SQL code to be executed when needed.

They implement the IKCommand interface, that provides a Link property, that refers to the specific link this command is created for, a Parser property, maintaing an instance to the specific parser to use when parsing the dynamic lambda expressions used to write the specific contents of the command, and a Parameters property maintaining the list of parameters to use when executing this command.

They have also the CommandText( iterable ) method that is the one that will produce the actual SQL code. Its "iterable" argument permits you to specify if you want to obtain its enumerable version or its executable one. Its TraceString() extension method adds to this string a representation of the parameters to be used.

There are several command classes for the Query, Insert, Update and Delete operations. There is also a fifth class for Raw commands, that are those that permit you to write in plain text your command, but keeping the dynamic nature of the results obtained. They are also handy to invoke stored procedures.

Query Commands

A Query command is a specialized object for generating SELECT commands against your database. You can instantiate them using its constructor, or by using some extension methods of the link objects: the Query() one, that basically mimics the constructor (but permits a handy chaining of method invocations), and the several overrides of the From() one.

Query commands provides with a number of specialized methods that will help you to write the contents of each clause in the command. You can use them as many times as needed, and in whatever order: Kerosene will reorganize the contents you have specified to produce a syntactically correct SQL code when needed, as in the next example:

var cmd = link
   .From( x => x.Employees.As( x.Emp ) ).Where( x => x.Emp.JoinDate >= new CalendarDate( 2000, 1, 1 ) )
   .From( x => x.Countries.As( x.Ctry ) ).Where( x => x.Ctry.Id == x.Emp.CountryId )
   .Select( x => x.Ctry.All() )
   .Select( x => x.Emp.Id, x => x.Emp.BirthDate, x => x.Emp.LastName );

This command will produce the following SQL code:

SELECT Ctry.*, Emp.Id, Emp.BirthDate, Emp.LastName FROM Employees AS Emp, Countries AS Ctry WHERE (( Emp.JoinDate >= @p0 ) AND ( Ctry.Id = Emp.CountryId ))

Disclaimer: I am not, in any way, presuming this is the most effective SQL command. The examples are just code provided to show how to use the capabilities of Kerosene, and how these capabilities are designed to accommodate to your way of thinking.
The IN and EQUALS syntaxes are also allowed. As an example, let's assume now that you want to find all the employees that do not belong to the "Europe, Middle East, and Africa" super region. One possible way to achieve it is as follows:

var cmd = link
  .From( x => x.Employees ).Where( x => !x.CountryId.In(
    link.From( y => y.Countries ).Select( y => y.Id ).Where( y => y.RegionId.In(
      link.From( z => z.Regions ).Select( z => z.Id ).Where( z => z.ParentId =
        link.From( p => p.Regions ).Select( p => p.Id )
          .Where( p => p.Name == "Europe, Middle East & Africa" )
) ) ) ) );

that will produce the following SQL code:

SELECT * FROM Employees WHERE ( NOT CountryId IN ( SELECT Id FROM Countries WHERE RegionId IN ( SELECT Id FROM Regions WHERE ParentId = ( SELECT Id FROM Regions WHERE ( Name = @p0 ) ) ) ) )

The "x => x.Member.In( Expression )" construction is the entry for the "IN" syntax, translating it into the "Member IN ( Expression )" SQL statement. The same logic applies when using the assignment operator "=", so translating "x => x.Member = expression" into "Member = ( Expression )", or "x => x.Member != expression" into "NOT Member = ( Expression )" (note that this second example uses the "!" negation operator).

You can also specify Join operations. The Join() method has two overrides: the easy one just takes a dynamic expression that states the contents of your JOIN clause. The second one takes an additional first argument being a string with the specific JOIN type (you can use just "JOIN", or "LEFT JOIN", or "INNER JOIN" ... or any variant you may need).

The dynamic lambda expression permits you to specify the table of the join, using the "x => x.Table" syntax, followed by an optional As() dynamic extension method in case an alias is needed, and a mandatory On() extension method where you will specify the condition of the join clause. Take a look at the next example:

var cmd = link
   .From( x => x.Employees.As( x.Emp ) )
   .Join( x => x.Countries.As( x.Ctry ).On( x.Ctry.Id == x.Emp.CountryId ) )
   .Join( x => x.Regions.As( x.Reg ).On( x.Reg.Id == x.Ctry.RegionId ) )
   .Join( x => x.Regions.As( x.Super ).On( x.Super.Id == x.Reg.ParentId ) )
   .Where( x => x.Super.Name == "Europe, Middle East & Africa" )
   .Select( x => x.Emp.All() )
   .Select( x => x.Reg.All() )
   .OrderBy( x => x.Reg.Id ).OrderBy( x => x.Emp.Id );

that will produce the following SQL code:

SELECT Emp.*, Reg.* FROM Employees AS Emp JOIN Countries AS Ctry ON (( Ctry.Id = Emp.CountryId )) JOIN Regions AS Reg ON (( Reg.Id = Ctry.RegionId )) JOIN Regions AS Super ON (( Super.Id = Reg.ParentId )) WHERE ( Super.Name = @p0 ) ORDER BY Reg.Id ASC, Emp.Id ASC

The Top() method takes an int specifying the number of records to return. If a previous Top method were invoked, the contents of the new one substitutes the old ones. There is really nothing else to say.

The OrderBy() method is used to annotate the contents for the ORDER BY clause. Its first argument is a lambda expression that specifies the column to use to sort the results. Its second argument is a boolean one, being true by default to specify that the sort order should be "ascending", or false to specify it should be "descending". For instance:

var cmd = link.From( ... ) ... OrderBy( x => x.Id, ascending: false );

The GroupBy() method is used to annotate the contents of the GROUP BY clause. It takes a variable list of lambda expressions, each of them being the specification of a given column. Please see the above discussion on Extending the Dynamic Syntax to see more examples on how to customize the contents of this clause.

Update Commands

The Update commands are, again, instantiated either by their constructor or by the Update() extension method of the IKLink objects. Their TableName property will contain the table being updated, and they provide two specific methods:
  • The Where(...) method, used to locate the record to update (or records if the operation can apply to many of them).
  • The Column(...) method, used to specify the columns to update and how, by using an assignation syntax as in "x => x.Name = "New Name"". You can use as many specifications as needed separating them by commas, or chain as many Column(...) methods as you wish.

Let’s see the next example:

var cmd = link.Update( x => x.Employees )
   .Where( x => x.FirstName >= "E" )
   .Column(
      x => x.ManagerId = null,
      x => x.LastName = x.LastName + "_1",
      x => x.Photo = new byte[] { 99, 98, 97, 96 }
);

Here we are modifying all the records whose first name is bigger or equal than "E" setting their "ManagerId" column to null, appending to its "LastName" column a "_1" string, and setting its "Photo" column to an array of bytes. Note that Kerosene imposes no limits to the SQL code you want to write: this is why, for instance, we were able to append such string to the "LastName" column using its previous contents as a seed.

Insert Commands

The Insert commands are very similar to the Update ones, except that they do not have a Where() method. As expected they are instantiated either by using their constructor, or by using the Insert() extension method.

Delete Commands

Similarly, the Delete commands are quite similar as the Update ones, except that they only have the Where() extension method, and that they are instantiated either by its constructor or by the Delete() extension method.

Notice that, if you don’t use any WHERE clause, when executed they will delete all the records in the table they refer to. Kerosene does not try to impede it as it might be precisely what you wanted.

Raw Commands and Stored Procedures

Finally, Kerosene provides a way to permit you to write in plain text the code you wish to execute, but keeping the dynamic nature of the results obtained. It is achieved by instantiating Raw commands, either by using their constructor, or by using the Raw() extension method of the IKLink objects. Its first argument is a string containing the SQL code. Its second argument is an optional variable list with the values of the arguments to be used. They are specified within the text using the standard C# format scape sequence:

var cmd = link.Raw( "SELECT * FROM Employees WHERE BirthDate >= {0}", new CalendarDate( 1969, 1, 1 ) );

Actually, you can add new ones to whatever contents a Raw command may have by using its Append() method, as in:

cmd.Append( " AND Id >= {0}", "007" );

One important thing to mention is that if you want to iterate through the contents produced by the command you should build the command to support it. This is not really relevant for Query commands, but it is needed for Insert, Update and Delete ones. For instance take a look at the "OUTPUT INSERTED.* string in the next example:

var cmd = link.Raw(
   "INSERT INTO Employees ( Id, FirstName, LastName, CountryId, BirthDate, JoinDate, Photo )"
   + " OUTPUT INSERTED.*" // Needed!!!
   + " VALUES ( {0}, {1}, {2}, {3}, {4}, {5}, {6} )",
   "007", "James", "Bond", "uk", new CalendarDate( 1969, 1, 1 ), null, new byte[] { 0, 0, 7 } );

Another handy use of Raw commands is that they permit you to invoke stored procedures. For instance, in the next example we will invoke the "employee_insert" stored procedure with just two explicit parameters and a number of implicit ones:
var cmd = link.Raw(
   "EXEC employee_insert @FirstName = {0}, @LastName = {1}",
   "James", "Bond" );

What Else?

Actually, a lot of things. This tutorial has covered only the basic mode of operation of Kerosene. We have not discussed here how it supports the Entity Framework alike capabilities for POCO classes by using its Kerosene Maps mechanism. Nor we have cover its Kerosene Internals or the Kerosene WCF scenarios.

Feel free to browse through the above links and to comment anything to wish.

Last edited Sep 24, 2012 at 5:45 PM by mbarbac, version 22

Comments

No comments yet.