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.