Tag Archives: Feature

Support for Sequences

Sequences are used in some databases as an alternative to identity or auto increment columns. As of MicroLite 5.3, sequence support has been added for the following databases:

  • MS SQL 2012 or newer
  • Firebird

If you wish to use sequences, you will need to alter how you configure identifiers for mapped classes:

If you use attribute based mapping, change to use IdentifierStrategy.Sequence and also specify the sequence name:

[Column("CustomerId")]
[Identifier(IdentifierStrategy.Sequence, "CustomerIdSequence")]
public int Id { get; set; }

If you use convention based mapping, you will need to specify a convention in the configuration (for example, “ClassName_IdName_Sequence” – e.g. Customer_Id_Sequence):

ResolveSequenceName = (PropertyInfo propertyInfo) =>
{
    return propertyInfo.DeclaringType.Name + "_" + propertyInfo.Name + "_Sequence";
}

That’s all there is to it, MicroLite will take care of assigning the sequence value to the identifier property after performing an Insert (as it does with IdentifierStrategy.DbGenerated).

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.

MicroLite 5.0 – New Features

The last few posts have covered some of the changes coming in MicroLite 5.0, now that the major ones are covered let’s take a look at at some of the new features:

ObjectDelta

The ObjectDelta class and the ISession.Advanced.Update(ObjectDelta) add a neat way to perform partial updates against a database row.

// Firstly we need to instantiate a new ObjectDelta instance and 
// provide it with the class the update is targeted against and 
// the identifier value of the instance to update.
var objectDelta = new ObjectDelta(
    type: typeof(Customer), identifier: 12823);

// Then we can add a number of updates.
objectDelta.AddChange(propertyName: "Locked", newValue: 0);
...

using (var session = sessionFactory.OpenSession())
{
    using (var transaction = session.BeginTransaction())
    {
        // Apply the update and confirm if a record was updated.
        bool wasUpdated = session.Advanced.Update(objectDelta);

        transaction.Commit();
    }
}

UriTypeConverter

The type converter support has been increased by adding support for the System.Uri class. As before with the XDocument support in MicroLite 3.0.3, it’s as simple as setting the property type:

public class Thing
{
    public Uri Website { get; set; }
}

The conversion is managed by the MicroLite.TypeConverters.UriTypeConverter and is registered in the TypeConverter.Converters collection by default.

ConnectionScope

We have added finer control over when the connection to the database is opened and closed. The default behaviour matches MicroLite 4.0 and earlier which is to open a connection when a transaction is started and closed it when the transaction is completed.

However there may be times where you want to open the connection and close it when a session is opened and disposed (e.g. performing multiple transactions in a single session where it is more performant to hold a connection open or where you use SQLite in-memory databases where the database is deleted when the connection is closed).

In order to use it, call the overload on ISessionFactory when opening a session:

var session = sessionFactory.OpenSession(ConnectionScope.PerSession);

MicroLite 5.0 – Changes to Type Converters

In MicroLite 5.0, we have enhanced the Type Converters which we introduced in MicroLite 3.1.

The following method has been added to the interface:

object ConvertFromDbValue(IDataReader reader, int index, Type type);

This allows you to read the value from the IDataReader at the given index in the most efficient way (e.g. as an Int32 without the value being boxed).

From MicroLite 5.0, the full interface now looks like this:

ITypeConverter MicroLite 5.0

The ConvertFromDbValue(IDataReader, int, Type) is called when building an object from the results of a query, the ConvertFromDbValue(object, Type) method still exists but is only used when using ISession.Advanced.ExecuteScalar as there is no data reader used for that operation.

New methods on SqlBuilder

Some handy new methods have been added to SqlBuilder.

They implement the standard SQL comparison operators for =, <>, >, >=, < and <=, together with IS NULL, IS NOT NULL, IN and NOT IN.

Why are these useful? After all, MicroLite allows you to write any clause you need in the form .Where("LastName = @p0, "Smith").

But there’s the catch: "@p0" is specific to only certain database engines, such as MsSql. The clauses will all need to change to support other platforms.

This is where the new methods come in. They respond to the flavour of SQL you are using, as defined when you created your session factory e.g.

var sessionFactory = Configure
    .Fluently()
    .ForConnection("TestDB", "MicroLite.Dialect.MsSqlDialect")
    .CreateSessionFactory();

So when you write…

var sqlQuery = SqlBuilder
    .Select("FirstName")
    .From("Contacts")
    .Where("LastName").IsEqualTo("Smith");

MicroLite helpfully generates…

"SELECT FirstName FROM Contacts WHERE LastName = @p0", "Smith"

It’s important to remember that if you do embed your own conditional clauses e.g. .Where("LastName = @p0, "Smith"), you continue to be responsible for amending the syntax to suit the flavour of SQL in use.

If you wish to engage with a different database part way through your application, it’s possible to amend SqlCharacters on the fly…

SqlBuilder.SqlCharacters = SqlCharacters.PostgreSql;

var sqlQuery = SqlBuilder
    .Select("FirstName")
    .From("Contacts")
    .Where("LastName").IsEqualTo("Smith");

The new methods are…

.Where("MyColumn").IsEqualTo("a")
.Where("MyColumn").IsNotEqualTo("a")
.Where("MyColumn").IsGreaterThan(200)
.Where("MyColumn").IsGreaterThanOrEqualTo(new DateTime(2010, 1, 1))
.Where("MyColumn").IsLessThan(50)
.Where("MyColumn").IsLessThanOrEqualTo(1000)
.Where("MyColumn").IsLike("Fred%")
.Where("MyColumn").IsNull()
.Where("MyColumn").IsNotNull()
.Where("MyColumn").In("a", "b", "c")
.Where("MyColumn").In(new SqlQuery("SELECT MyValue FROM MyTable"))
.Where("MyColumn").NotIn("a", "b", "c")
.Where("MyColumn").NotIn(new SqlQuery("SELECT MyValue FROM MyTable"))

And a most useful feature: these methods will chain, so you can write…

var sqlQuery = SqlBuilder
    .Select("FirstName")
    .From("Contacts")
    .Where("LastName").IsEqualTo("Smith")
    .AndWhere("FirstName").In("Jane", "John");

MicroLite 4.0 – Changes to Projections and Dynamics

In MicroLite 2.0, we introduced the Dynamic Projections feature and although useful, it did suffer from a couple of small limitations.

  • Firstly, it wasn’t compatible with the Include API so you had to perform 2 separate queries if you wanted to query a mapped class and also display a projection.
  • Secondly, you had to manually implement paging as projection had no paging capability.
  • Thirdly, it always retuned a collection so it was more effort to return a single record.

In order to address these limitations, the .Projection() method has been obsoleted and instead the dynamic keyword can be used the following methods:

ISession.Include.Many<dynamic>();
ISession.Include.Single<dynamic>(SqlQuery);
ISession.Fetch<dynamic>(); // This is the direct replacement for ISession.Projection()
ISession.Paged<dynamic>();
ISession.Single<dynamic>(SqlQuery);

In addition to the change to dynamic support, the methods listed above can also be supplied with primitive types and a query which is restricted to a single column:

var dates = session.Fetch<DateTime>(new SqlQuery("SELECT Date FROM Table"));

This makes it much easier to use dynamic projections in your application.

WebApi 3.0 MicroLiteApiController update

MicroLiteApiController<TEntity, TId>

It now contains a new controller MicroLiteApiController<TEntity, TId>.

There are 2 generic args, one is to specify the entity type, the other the type used by the identifier property (usally int but MicroLite supports other identifier types).

The new controller supports Get, Post, Put and Delete on an opt-in basis. The methods are all declared protected, so in order to use them you need to create a public method which calls the protected one. The reasons for this are to make the functionality opt-in not opt-out which is safer and it works nicely with the WebApi help extension.

In order to use the new controller, simply create a custom controller which inherits from it and provides the type which the controller will deal with.

For example, if we have a customer class we can create a CustomerApiController:

public class CustomerApiController : MicroLiteApiController<Customer, int>
{
     ...
}

The methods all return HttpResponseMessage, the base controller is responsible for setting the HttpStatus code (e.g. 200, 404) depending on the request type and whether the request is successful.

To enable a HTTP action, simply implement the public version of the method and call the protected version.

public HttpResponseMessage Delete(int id)
{
     return this.DeleteEntityResponse(id);
}
public HttpResponseMessage Get(int id)
{
     return this.GetEntityResponse(id);
}
public HttpResponseMessage Post(Customer entity)
{
     return this.PostEntityResponse(entity);
}
public HttpResponseMessage Put(int id, Customer entity)
{
     return this.PutEntityResponse(id, entity);
}

The MicroLiteApiController&lt;TEntity, TId> class can be used to reduce the amount of boiler plate repetitive code you need to write in order to create WebApis.