LLBLGen Pro v5.1 EAP1 released!

Today we released our first ‘Early Access Program’ build for LLBLGen Pro v5.1! When we moved to subscriptions (with perpetual licenses) when we released v5.0, the one thing I wanted to get rid of was the long delays between versions: no more 1.5-2 years of development to a massive release, but smaller releases which are given to the users quickly. So here is the first release of that. Additionally, we did a lot of work to make it release-ready. This means that the EAP build is a release like any other final release, including up-to-date documentation and fully tested. This is another big step for us, so we can switch an EAP build to ‘RTM’ at any time.

In the coming months we’ll release more builds till we reach RTM.

What’s included?

This first EAP release contains the following features. We’re focusing on our state of the art ORM framework this time around for most of the features planned for v5.1 RTM.

Temporal (history) table support (SQL Server 2016 / DB2 10)

For select / fetch queries, the LLBLGen Pro runtime framework now supports temporal tables. A temporal table is a table with a coupled history table which is managed by the RDBMS: an update or delete of a row will copy the original row to the history table with two date markers to signal the period in which this row was valid. For more information about temporal tables in e.g. SQL Server, see this article.

Temporal tables offer a transparent way to work with history data. This means you can now use temporal table predicates directly in Linq and QuerySpec (our fluent query API) queries to query on the current data but also on history data. See this example:

   1: var q = from e in metaData.Employee
   2:                             .ForSystemTime("BETWEEN {0} AND {1}", 
   3:                                             fromDate, toDate)
   4:         where e.EmployeeId == 1
   5:         select e;

Here a Linq query is defined which will query for the employee data of the employee with id ‘1’, and all rows valid between fromDate and toDate are included. This means that if the employee data of this particular employee was updated between these two dates, the original data which was updated will be included in the resultset as well.

On IBM DB2, LLBLGen Pro also supports Business Time temporal table predicates, something which isn’t supported by SQL Server 2016.

Table / View hints (SQL Server) and Index hints (MySQL)

To specify a hint for the RDBMS query optimizer has been a requested feature for a long time, but I never found a proper way to make it easy to specify. With the temporal table support, the same mechanism can be used for specifying hints for table / views, in the case of SQL Server, and indexes, in the case of MySQL. All other databases which support hints (Oracle and DB2 come to mind) aren’t supported here, as they force the hints to be present as comments in the projection of the SELECT statement, and the hint system works with hints specified on elements in the FROM clause. This isn’t that bad however, as hints on Oracle and DB2 are heavily discouraged by the vendors of these databases so it’s unlikely we’ll add support for these particular hints later.

To specify a table / view hint in a Linq query (or QuerySpec query), you simply call an extension method with the hint as argument as shown in the following example (SQL Server): 

   1: var q = from c in metaData.Customer
   2:                                 .WithHint("NOLOCK")
   3:                                 .WithHint("FORCESEEK")
   4:         join o in metaData.Order on c.CustomerId equals o.CustomerId 
   5:         where o.EmployeeId > 4
   6:         select c;

Here the target mapped by the entity ‘Customer’ will receive two hints specified in the SQL Query, namely ‘NOLOCK’ and ‘FORCESEEK’. The target mapped by the entity ‘Order’ doesn’t receive these hints.

Hints are a last resort to optimize a query and in general they should be left alone, but in cases where it can help a great deal, it’s good that an ORM Framework supports facilities to specify hints so the developer doesn’t have to fall back to raw in-lined SQL or a stored procedure.

More to come!

No Comments