Monthly Archives: November 2012

API Design Decisions

I’ve received a few questions since I first released MicroLite so I thought I’d post some of the common ones along with my answers to them.

Why do I have to write SQL queries instead of using Linq?
The main reason is to avoid the translation of a Linq expression into SQL. The DSL (Domain Specific Language) for databases is SQL so it makes sense to just write SQL in the first place. Additionally, Linq expressions are complex and trying to cover all possibilities would be impractical which means you would need the ability to use native SQL anyway.

“If you wanted to ask an Italian friend a question, would you write it in French first and then translate it into Italian?”

Why is there no method on SqlBuilder to do SELECT TOP N?
Originally I planned to add one but as I thought about it further, I realised it’s unnecessary. We can do TOP queries already by writing a standard select statement and using ISession.Paged (if you want the top (first) 20 results for a query, just use ISession.Paged(sqlQuery, page: 1, resultsPerPage: 20) instead).

Why have a separate Configuration class?
I wanted to be able to provide fluent APIs where possible to make it easy to understand what methods should be called and when. It also gives us a central place to add configuration options in future (like when the Configure.Extensions were added). It also moves the concern of configuration into a specific place, rather than being another set of methods on the ISession.

Why use interfaces for the API?
The public API for MicroLite is based around abstractions, the reason for this is so that callers are not bound to implementations. This makes it easier to do things like unit testing as the interfaces can be replaced with Mocks or Fakes. The exceptions to this are simple classes which only operate on their own data like SqlQuery and PagedResult.

Why do I have to use attributes to map classes?
I wanted MicroLite to be explicit about what it is doing and how it is used, the opt-in model of the attribute mapping makes it easy to understand how a class relates to a table. However one of the other key principles is that MicroLite should be extensible, so you can actually implement your own mapping convention if you don’t want to use attributes.

Why is the SqlDialect specified as a string instead of an enum in the configuration?
This is because MicroLite is designed for extension, although in MicroLite 2.1 the SqlDialect concept is internal, in version 3 I intend to make it public and allow people to create their own SqlDialect to add support for other databases. If an enum was used for the SqlDialect, other users could not add a value to it which would make it hard to register other dialects.

Should I implement the repository pattern when using MicroLite?
I would advise against it, treat MicroLite as a generic repository and just give it the relevant SqlQuery for the results you want. You will end up creating a lot of unnecessary classes if you create a repository for each class you use and make it a lot harder to use some of the benefits of MicroLite such as the includes. The repository pattern is useful to abstract database persistence or encapsulate a database API which cannot be tested, however MicroLite already does the former and is designed with testability in mind anyway.

Since there is no lazy loading, how should I use MicroLite in a screen where I need to show multiple types of data?
The Includes functionality introduced in MicroLite 2.0 is designed to help solve this issue.

For example, if you are creating an application using the MVVM pattern, the ViewModel should encapsulate the ISession and expose properties for each object or collection related to the root entity (for example the CustomerViewModel may have a collection of Invoices or Orders in addition to the Customer data.

Why would I use MicroLite instead of another Micro ORM?
MicroLite was built to be simple to use, easily tested and extensible. If any of these matter to you then it’s worth investigating the other features to see if it’s suitable for your application. MicroLite tries to be explicit about its behaviour and since it is not built around dynamic objects you still get good tooling support from intelisense and xml documentation.


Using MicroLite with ASP.NET WebApi

An extension package has been released to help integrate MicroLite with ASP.NET WebApi even more easily. Version 1 is essentially a port of the MicroLite MVC Extension.

To use it, install the NuGet package Install-Package MicroLite.Extensions.WebApi.

The key difference is that the base controller is called MicroLiteApiController to adhere to the conventions used by WebApi.

At present, there is no support for OData queries since MicroLite does not have an IQueryable provider. An update will be released in the near future which will add support for OData queries by translating them into SqlQuery instances.

Using Logging in Custom Listeners

We covered recently Extending MicroLite with custom Listeners and as a result you may find that you create a Listener that could be distributed for used by other people.

Lets for example say that you created a Listener to perform validation and wanted to log any validation warnings and errors, you can utilise the logging abstraction provided by MicroLite.

Now since we cannot guarantee that there will be a logger present, we don’t want to call log directly as it may be null. There are a set of extension methods available which will check whether a logger is in use and you should call them instead. They are in the MicroLite.Logging namespace so if you add a using for MicroLite.Logging instead of fully qualifying the ILog and LogManager class they will show up in intellisense.

This example is to show you how you could log the warnings and errors (Validator and ValidationException are fictional classes used as an example).

Firstly, add a static field to the class which is of type MicroLite.Logging.ILog which is resolved by the MicroLite LogManager class.

Then call the TryLog.. extension methods so that we don’t have to check explicitly whether we have a logger or not.

using MicroLite.Logging;

public class ValidationListener : MicroLite.Listeners.Listener
    private static ILog log = LogManager.GetCurrentClassLog();

    protected override BeforeInsert(object instance)
        log.TryLogDebug("Validating an object before insert");

    protected override BeforeUpdate(object instance)
        log.TryLogDebug("Validating an object before update");

    private void ValidateObject(object instance)
        var warnings = Validator.GetWarnings(instance);
        var errors = Validator.GetErrors(instance);

        foreach (var warning in warnings)

        foreach (var error in errors)

        if (warnings.Count > 0 || errors.Count > 0)
            throw new ValidationException(warnings, errors);

This article was written for version 2.1.0 of the MicroLite framework.

Using SQLite

One of the updates in MicroLite 2.1 was support for SQLite, however there are a couple of things you need to do if you wish to use SQLite which you don’t for MS SQL. This is due to the fact that the .NET framework supports MS SQL natively, whereas the SQLite implementations of the System.Data classes are provided by

The easiest way to get started is to install the System.Data.SQLite NuGet package, although you can download and install it from if you don’t/can’t use NuGet.

As usual you will still need to add a named connection string to your app.config:

    <add name="SQLiteTest"
         connectionString="Data Source=C:\MyApp\SQLiteTest.db3;Version=3;" 
         providerName="System.Data.SQLite" />

Notice that the Data Source points to the SQLite database file, see for additional information.

You also need to add another section to your app.config so that the .NET framework knows about the System.Data.SQLite provider:

        <remove invariant="System.Data.SQLite" />
        <add name="SQLite Data Provider" 
             description=".Net Framework Data Provider for SQLite" 
             type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />

Make sure that the version specified matches the version you have installed or you will get an exception thrown by the .NET runtime at startup when it tries to resolve the assembly.

Then finally, in you application startup, you need to call the new overload method for ForConnection so that you can specify the SQLiteDialect:

var sessionFactory = Configure
    .ForConnection(connectionName: "SQLiteTest", sqlDialect: "MicroLite.Dialect.SQLiteDialect")

It is also worth noting that SQLite does not support table schemas so don’t specify one when mapping the table. The schema value on the table attribute was made optional in MicroLite 2.1:

public class Customer

This article was written for version 2.1.0 of the MicroLite framework.

Update 1

As of MicroLite 4.0.1, use the ForSQLiteConnection method instead:

var sessionFactory = Configure

Update 2

As of version, System.Data.SQLite now has a dependency on EntityFramework… You should use the System.Data.SQLite.Core package instead.

Version Numbers

A version number is made up of 4 parts, Major.Minor.Patch.Build and there are 2 version numbers used by MicroLite.

The Assembly Version is the version of the dll that the .NET runtime will use to link an assembly. MicroLite only ever specifies the Major and Minor values for this which makes it easier to do drop in replacements of bug fix builds without having to re-compile an application.

The Assembly File Version is an informational version number which does include the Patch value. This value will always match the version number in the NuGet pagkage.

So an example would be an Assembly Version of and an Assembly File Version of

The Major version is increased when there are non backwards compatible change to the public API. This would be for example removing or renaming a class, interface, method or property which is publicly visible in the MicroLite assembly.

The Minor version is increased when there are backwards compatible enhancements or additions to the public API. This would be for example adding a new class, interface, method or property which is publicly visible in the MicroLite assembly. If any classes/methods are to be obsoleted in the next Major version, they will be marked obsolete in a Minor revision change with the replacement added. This allows a clean transition between Major versions where possible.

The Patch version is increased when there are backwards compatible bug fixes which do not change the public API at all.

What does this all mean?

If you are using MicroLite in your own application, you can safely assume the following:

  • Installing an update where the patch version has incremented will not require your code to change.
  • Installing an update where the minor version has incremented should not break your build, however there may be build warnings if anything has been obsoleted.
  • Installing an update where the major version has incremented may break your build depending on what’s changed and what features you are using.

If you have developed an extension, you can safely assume the following:

  • If you develop against version, you can depend on any version up to but not including version
  • If you develop against version, you can depend on any version up to but not including version, but not any version below

Targeting Multiple Databases

MicroLite supports targeting multiple databases within a single application by simply adding multiple named connections and then creating a SessionFactory for each connection.

This can be useful if you need to aggregate data from multiple databases within a single application, or you may have a separate database (or different database user) for read and write operations for your application.

class Program
    // Ideally these would be registered in an IOC container instead of being static properties.
    public static ISessionFactory NorthwindSessionFactory { get; private set; }
    public static ISessionFactory PubsSessionFactory { get; private set; }

    static void Main(string[] args)
        Program.NorthwindSessionFactory = Configure

        Program.PubsSessionFactory = Configure

If you are using the MVC Extension, you can simply specify a name in the MicroLiteSessionAttribute to configure which database to connect to:

public class CustomerController : MicroLiteController

This article was written for version 2.1.0 of the MicroLite framework.

SqlBuilder Updates

We have covered the SqlBuilder before, however it has been updated with enhancements in MicroLite 2.0 and 2.1.

MicroLite 2.0 introduced support for calling SQL functions (Average, Count, Min, Max, Sum) and to align Select, the shortcut SelectFrom method was changed.

// Instead of calling
// You should now call
var sqlQuery = SqlBuilder
     .Where("CustomerId = @p0", 1022)

var total = session.Advanced.ExecuteScalar<decimal>(query);

You can also alias the calculated column and use it in the WHERE clause:

var sqlQuery = SqlBuilder
     .Average("Total", columnAlias: "AverageTotal")
     .Where("CustomerId = @p0", 1022)
     .AndWhere("AverageTotal > @p0", 1000.00M)

Group By is also supported:

var sqlQuery = SqlBuilder

It was decided not to support the TOP keyword in SqlBuilder as the Paged method on ISession is specifically designed for paging.

MicroLite 2.1 Enhancements details examples of the further enhancements added to SqlBuilder.

This article was written for version 2.1.0 of the MicroLite framework.