MicroLite 5.3 Released

MicroLite 5.3.0 has been released on NuGet.

The release mainly focuses on implementing support for database sequences and also an updated dialect which supports the new features in MS SQL 2012 onwards.

In addition, we have improved the SqlBuilder – it now supports In(params SqlQuery[] sqlQueries) and NotIn(params SqlQuery[] sqlQueries).

We covered the changes with examples in the MicroLite 5.3 upcoming features post so see that for further details.

Oracle and Sybase Contribution Request

I have added 2 new branches in the MicroLite repository for Sybase and Oracle as we would like to add support for both to MicroLite.

If anyone has any experience with using either of these databases (and/or) has an installation they can use to develop against – any contributions towards the implementation would be much appreciated.

MicroLite 5.3 upcoming features

MicroLite 5.3 is currently in development, you can see the progress by checking the GitHub 5.3 Milestone

Sequence Columns

The main change in this release is the support for sequence columns as identifiers, this will be supported in the Firebird dialect and also the MS SQL Server dialect.

In order to support the new features in MS SQL Server 2012, we are changing the existing MS SQL Server dialect. The existing dialect will be renamed to MsSql2005 and a new MsSql2012 will be added. As far as the configuration goes, if you use MS SQL Server you will need to change the following method call:

.ForMsSqlConnection("connection name")

to one of the following:

.ForMsSql2005Connection("connection name")
// or
.ForMsSql2012Connection("connection name")

MsSql2005 will work with MS SQL Server 2005 or later, the new MsSql2012 will work with MS SQL Server 2012 or later. In addition to using supporting sequences (as well as still supporting identity), the MsSql2012Dialect also uses the new OFFSET FETCH syntax instead of common table expressions which the 2005 dialect uses.

SQL Builder

The In and NotIn methods on the SqlBuilder now support multiple sub queries which means you can use it like this:

var subOne = new SqlQuery("SOME SQL");
var subTwo = new SqlQuery("SOME SQL");

var parentQuery = SqlBuilder
    .Select("Id", "Firstname", "Surname", "Username")
    .From("Users")
    .Where("Id").In(subOne, SubTwo)
    .ToSqlQuery();

The aim is to have MicroLite 5.3 finished by 17th September and we will hopefully have a beta build out in the next day or two, keep an eye out for it on nuget.org.

MicroLite 5.2 Released

MicroLite 5.2.0 has been released on NuGet.

The release mainly focuses on improving the usability from a query perspective. To that end, we have added support for:

  • Querying results into Tuples
  • Querying results into read-only pocos

In addition, we have added extra methods to the SqlBuilder and made a couple of minor refinements to the dynamic IL generation.

We covered the changes with examples in the MicroLite 5.2 upcoming features post so see that for further details.

MicroLite 5.2 Beta

A beta for MicroLite 5.2.0 has been released on NuGet, please feel free to download it and check out the new features we mentioned in the MicroLite 5.2 upcoming features post.

If you find any bugs, please raise them in the GitHub Issues along with any necessary information to reproduce it (or better yet, a pull request with a failing unit test) and we’ll make sure it’s squashed before the final release.

We should still be on track to release the final 5.2 build on the 20th August 2014.

MicroLite 5.2 upcoming features

MicroLite 5.2 is currently in development, you can see the progress by checking the GitHub 5.2 Milestone

We’ll cover the changes in greater detail once MicroLite 5.2 has been released, however here’s a sneak preview of some of the changes:

Includes

We have added a callback action which can be registered when specifying an include:

At the moment, if you want to use the Include functionality and store the result in a field, you need to do something like this:

var includeCustomer = session.Include.Single<Customer>(12345);

... // Something which results in the query being executed

this.customer = includeCustomer.Value;

The new callback functionality will allow you to express that same logic like this:

session.Include.Single<Customer>(12345)
    .OnLoad(include => this.customer = include.Value);

Read Only classes

Prior to MicroLite 5.2, we required that classes had an identifier mapped so that they could be used for select by Id, insert, update and delete calls. We added the dynamic projection support to allow ad-hoc select queries but sometimes it is still nice to have a typed class. Also, dynamics are not supported in .NET 3.5 so the .NET 3.5 build of MicroLite does not offer it.

To remove this limitation, we are adding support for read only classes.

If you are using the Attribute Based Mapping, simply don’t decorate the class with the [Identifier(IdentifierStrategy.DbGenerated)] attribute.

If you are using the Convention Based Mapping, don’t add an Id or {Class}Id property.

// Convention mapped read-only class (no identifier required)
public class CustomerSummary
{
    public string Name { get; set; }
    public int Age { get; set; }
}

Example usage:

var sqlQuery = new SqlQuery(
    "SELECT Name, Age FROM Customer WHERE CustomerId = @p0",
    1024);

var customerSummary = session.Single<CustomerSummary>(sqlQuery);

Console.WriteLine(customerSummary.Name);
Console.WriteLine(customerSummary.Age);

Read only classes will be supported by the following methods:

ISession.Include.Many<T>();
ISession.Include.Single<T>(SqlQuery);
ISession.Fetch<T>();
ISession.Paged<T>();
ISession.Single<T>(SqlQuery);

Tuples

MicroLite has had support for dynamic projections since MicroLite 2.0 and the functionality was extended and simplified more recently in MicroLite 4.0.

In addition to dynamic results, we are now adding support for Tuples which will allow you to do the following:

var sqlQuery = new SqlQuery(
    "SELECT Name, Age FROM Customer WHERE CustomerId = @p0",
    1024);

var tuple = session.Single<Tuple<int, string>>(sqlQuery);

Console.WriteLine(tuple.Item1);
Console.WriteLine(tuple.Item2);

Tuples will be supported for use in the following methods and will support singletons (one item) through to septuples (seven items) and be available in the .NET 4.0 and .NET 4.5 builds of MicroLite.

ISession.Include.Many<Tuple<T>>();
ISession.Include.Single<Tuple<T>>(SqlQuery);
ISession.Fetch<Tuple<T>>();
ISession.Paged<Tuple<T>>();
ISession.Single<Tuple<T>>(SqlQuery);

SQL Builder

NOT LIKE

var sqlQuery = sqlBuilder
    .Select("*")
    .From("Table")
    .Where("Column1")
    .IsNotLike("FOO%")
    .ToSqlQuery();

This gives us the equivalent negative for .IsLike("FOO%").

NOT BETWEEN

var sqlQuery = sqlBuilder
    .Select("*")
    .From("Table")
    .Where("Column1")
    .NotBetween(1, 10)
    .ToSqlQuery();

This gives us the equivalent negative for .Between(1, 10).

EXISTS and NOT EXISTS

var subQuery = new SqlQuery(
    "SELECT Id FROM Table WHERE Column = @p0",
    1024);

var sqlQuery = sqlBuilder
    .Select("*")
    .From("Table")
    .Where()
    .Exists(subQuery)
    .ToSqlQuery();

var sqlQuery = sqlBuilder
    .Select("*")
    .From("Table")
    .Where()
    .NotExists(subQuery)
    .ToSqlQuery();

DISTINCT

var sqlQuery = sqlBuilder
    .Select()
    .Distinct("Column")
    .From("Table")
    .ToSqlQuery();

The aim is to have MicroLite 5.2 finished by 20th August 2014, keep an eye out for it on nuget.org.