LLBLGen Pro v5.1 EAP 2 released!
Today we released the second ‘Early Access Program’ (EAP) build of LLBLGen Pro v5.1! Please see the previous post on the first EAP build and what’s included in that version. This version builds further on that foundation and includes new features (as well as all fixes made to EAP 1 and v5.0). Our EAP builds are ‘ready to release’ builds, with up to date documentation and are fully tested.
Updated packages (marked ‘Alpha-20160928’) have been pushed to Nuget as well for the folks who prefer referencing nuget packages.
What’s included in EAP 2?
EAP 2 has the following new features, which are again all part of the LLBLGen Pro runtime framework. I’ll link to the documentation for details on the topics discussed below.
Plain SQL API
Using an ORM in general doesn’t confront you with plain SQL queries, often the APIs in ORMs don’t even allow you to specify a plain SQL query. This is OK for most situations but there are occasions where you might need to run a hand-optimized plain SQL query or have to execute a SQL query which can’t be generated by the ORM query API.
Since the arrival of microORMs, which often have a plain SQL interface as their core query API, users of full ORM frameworks sometimes also use a microORM ‘on the side’ to perform these plain SQL queries when needed. While this might work, it also can be a bit of a problem as one can’t leverage aspects offered by the full ORM, like an active transaction or easy paging query generation.
As we were already working on an addition to our POCO projection pipeline, we thought: why not open the runtime some more to be able to project plain SQL resultsets to POCOs as well? (and of course be able to execute SQL statements which don’t return a resultset). This is the new Plain SQL API.
All methods have async/await variants of course.
Executing a SQL statement
Executing a SQL statement is meant to execute a non-resultset SQL query, e.g. to update or delete some rows. This is easy to do with the ExecuteSQL method.
1: Guid newId = Guid.Empty;2: using(var adapter = new DataAccessAdapter())
3: {4: var q = @"SELECT @p0=NEWID();INSERT INTO MyTable (ID, Name) VALUES (@p0, @p1);";
5: var idVar = new ParameterValue(ParameterDirection.InputOutput, dbType: DbType.Guid);
6: var result = adapter.ExecuteSQL(q, idVar, "NameValue");
7: newId = (Guid)idVar.Value; 8: }The example above inserts a row into MyTable and returns the generated NEWID() Guid through an output parameter. It passes the value ‘NameValue’ as parameter value to the query as well. There’s a flexible way to specify parameters with just the values as arguments of ExecuteSQL and if needed, you can define a ParameterValue instance with parameter specifics, like direction, DbType, length, precision, scale etc. This is also the mechanism used to obtain the output parameter value after the query has been completed.
Fetching a resultset
Of course you can also fetch a resultset and project to POCO classes using the Plain SQL API. This is done using the FetchQuery method. See the following example:
1: List<Customer> result = null;
2: using(var adapter = new DataAccessAdapter())
3: { 4: result = adapter.FetchQuery<Customer>(5: "SELECT * FROM Customers WHERE Country IN (@p0, @p1) ORDER BY CustomerID DESC",
6: "USA", "Germany");
7: }In the example above a query on the Customers table with a WHERE clause using two parameter values is projected onto the POCO class Customer. This is all nice and works great, but as the API is part of a full ORM, there’s more: we can leverage systems in the full framework. Here’s the same example again, but this time it utilizes the Resultset caching system in LLBLGen Pro and it also offers paging query creation for a specific offset / limit combination:
1: List<Customer> result = null;
2: using(var adapter = new DataAccessAdapter())
3: {4: // fetch first 5 rows, cache resultset for 10 seconds.
5: result = adapter.FetchQuery<Customer>(new PlainSQLFetchAspects(limit: 5,
6: cacheResultset: true, cacheDuration: new TimeSpan(0, 0, 10)),
7: "SELECT * FROM Customers WHERE Country IN (@p0, @p1) ORDER BY CustomerID DESC",
8: "USA", "Germany");
9: }Here the query uses an instance of PlainSQLFetchAspects which allows you to specify an optional limit, offset and caching specifics. In the example above a limit of 5 is specified, no offset (so the first 5 rows of the resultset). Additionally it specifies that the resultset has to be cached for 10 seconds. This means that if an exactly the same query is executed within 10 seconds, it will pull the resultset from the cache instead of obtaining it from the database again. It checks the compatibility mode of the adapter used and will create a paging query matching it, e.g. an OFFSET query on SQL Server 2012+ and a CTE on SQL Server 2005-2008.
The projections to POCO classes are done using property name-column name comparisons: every property in the specified POCO (here: Customer) with a name which is equal (case insensitive comparison) to a column in the resultset will receive the value in that column.
Executing a stored procedure to fetch a resultset is similar: instead of a SELECT… query you simply specify the EXEC proc_name parameters statement as the SQL statement. See the example below:
1: List<Order> result = null;
2: using(var adapter = new DataAccessAdapter())
3: {4: var result = adapter.FetchQuery<Order>("EXEC CustOrdersOrders @p0", "CHOPS");
5: }This example fetches the resultset returned by the stored procedure CustOrdersOrders while passing in a parameter, namely the CustomerID.
Transactions
As the plain SQL queries are executed through the same pipeline as normal entity queries are, a plain SQL query can participate in a transaction which also contains entity activity and you can e.g. use the known transaction mechanism to run multiple plain SQL statements in one transaction.
Is it fast?
You bet! Using my own RawDataAccessBencher system, the Plain SQL API is one of the fastest APIs to fetch data on .NET, beating many micro ORMs which are considered ‘much faster than full ORMs’: Results from 21st September 2016 (‘Poco with Raw SQL’ results). This makes LLBLGen Pro Runtime Framework one of the fastest ORMs on .NET, only Linq to DB is faster. The regular pipeline was already one of the fastest, with the Plain SQL api we made the final small step to reach the absolute holy grail: be (almost) as fast as handwritten materializer code.
One key aspect of why the Plain SQL API is a bit faster than the regular pipeline is that the Plain SQL api can cut some corners in the projection: it doesn’t have to run type converters if needed, as type converters aren’t supported in the Plain SQL API. This frees the pipeline from first boxing the row from the datareader in an object array. Type converters are a feature of the LLBLGen Pro runtime framework where you can transparently convert one type to another and back when persisting data to the database or reading data from the database (e.g. convert a byte[] to a bitmap class, convert a string to a number, anything is possible).
What about SQL Injection?
Plain SQL APIs have a downside: a developer can think s/he’s ‘smart’ and think “I’ll just embed this value in the query string, no need for a parameter!”, and with that opening the application to external SQL Injection attacks. With a Plain SQL API it’s not possible to check for this. What we have done instead are two things: add a specific tracer to log all plain SQL queries in a separate output, and add a way to disable the API entirely. (More info about these two options in the documention)
This gives options to our users: if they don’t want their developers to use the Plain SQL API, they can simply add a small code snippet and it will disable the API. If they instead want to monitor whether their developers cut corners by embedding values in the plain SQL statements (or use the API at all!) they can simply add a tracer through .NET’s config system and get the output in a file or other storage mechanism and check the output.
Easier projections to POCO classes in QuerySpec
We added an easier way to project QuerySpec queries to POCO classes. Besides the regular way to project a resultset to a POCO using ToValue<T>() calls for every property, it’s now also possible to simply specify the field list and the resulting type. Using the same property name-column name system as with the Plain SQL API, it’s no longer necessary to specify each field projection individually. See the example below:
1: var qf = new QueryFactory();
2: var q = qf.Create() 3: .Select<CustomerDTO>(CustomerFields.Country,4: Functions.IIF(CustomerFields.Country == "USA", CustomerFields.ContactName,
5: CustomerFields.ContactTitle).As("CheckValue"),
6: CustomerFields.ContactName, 7: CustomerFields.ContactTitle);The example above fetches 3 fields from the target Customer is mapped on and utilizes a function call (IIF, which results in a CASE statement) as well. The resultset is then projected to instances of CustomerDTO.
Easier projections to POCO classes of stored procedure resultsets
Projecting a resultset of a stored procedure call to a POCO class was already doable without much effort in the designer, but if you wanted to project a resultset to a POCO type you have written by hand it was rather verbose. We solved this with the same projection system as above, which results in simple code:
1: List<CustomerDTO> customers; 2: List<OrderDTO> orders;3: using(var adapter = new DataAccessAdapter())
4: {5: using(var query = RetrievalProcedures.GetCustomersAndOrdersOnCountryCallAsQuery("Germany"))
6: {7: using(var reader = adapter.FetchDataReader(query, CommandBehavior.CloseConnection))
8: { 9: customers = adapter.FetchProjection<CustomerDTO>(reader); 10: reader.NextResult(); 11: orders = adapter.FetchProjection<OrderDTO>(reader); 12: reader.Close(); 13: } 14: } 15: }The example above calls a stored procedure, which is mapped as a call in the designer, and fetches two resultsets from this procedure, as it returns two resultsets. It does this by using the FetchDataReader API and projects the resultsets using the new FetchProjection<T> method.
That’s it for now, more to come in the near future as we’re not done with v5.1 yet, stay tuned! 