Monthly Archives: August 2013

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.

MicroLite 4.0 – Convention Based Mapping Enhancements

In MicroLite 3.0, we introduced convention based mapping and with MicroLite 4.0 we are changing to make convention based mapping the default. Also, a number of enhancements have been made to allow your conventions to be specified.

The ConventionMappingSettings class now has a number of extra properties which can be configured to define your own conventions. The default values for these are backwards compatible with the conventions defined in MicroLite 3.0.

AllowInsert

The AllowInsert property is a Func<PropertyInfo, bool> which can be used to specify whether a property value can be inserted (the equivalent of setting AllowInsert = false on the ColumnAttribute if you were using attribute mapping). By default, this method will always return true, however if you had an audit property with updated date, you could configure the settings as follows:

Configure.Extensions().WithConventionBasedMapping(
    new ConventionMappingSettings
    {
        AllowInsert = (PropertyInfo propertyInfo) => 
        {
            return propertyInfo.Name != "Updated"; 
        }
    });

AllowUpdate

The AllowUpdate property defines a Func<PropertyInfo, bool> which can be used to specify whether a property value can be updated after it is first inserted (the equivalent of setting AllowUpdate = false on the ColumnAttribute if you were using attribute mapping). By default, this method will always return true, however if you had an audit property with created date which should not be changed, you could configure the settings as follows:

Configure.Extensions().WithConventionBasedMapping(
    new ConventionMappingSettings
    {
        AllowUpdate = (PropertyInfo propertyInfo) => 
        {
            return propertyInfo.Name != "Created"; 
        }
    });

Ignore

The Ignore property defines a Func<PropertyInfo, bool> which can be used to specify whether a property which would ordinarily be mapped should be ignored (the equivalent of not applying a ColumnAttribute if you were using attribute mapping). By default, this method will always return false.

IsIdentifier

The IsIdentifier property defines a Func<PropertyInfo, bool> which can be used to specify whether a property is the property mapped to the identifier column in the database table. By default, this method will return true if the property name is Id or {ClassName} + Id.

ResolveColumnName

The ResolveColumnName property defines a Func<PropertyInfo, string> which can be used to resolve the name of the column a property is mapped to. By default, the property name will be used unless the property is an enum in which case {EnumType} + Id will be used.

Example public string Forename would resolve to Forename whereas public CustomerStatus CustomerStatus would resolve to CustomerStatusId.

ResolveIdentifierColumnName

The ResolveIdentifierColumnName property defines a Func<PropertyInfo, string> which can be used to resolve the name of the column the identifier property is mapped to. This function will only be invoked if the IsIdentifier function returns true. By default, this will return the property name.

Example public int Id would resolve to Id so the expected column name in the database would be Id.

If your database schema defines the identifier column as TableName + Id (e.g. CustomerId) but in your code you want to use public int Id, you could implement the method as follows:

Configure.Extensions().WithConventionBasedMapping(
    new ConventionMappingSettings
    {
        ResolveIdentifierColumnName = (PropertyInfo propertyInfo) => 
        {
            return propertyInfo.DeclaringType.Name + "Id"; 
        }
    });

WebApi Extension 3.0.0 Released

MicroLite.Extensions.WebApi 3.0.0 has been released on NuGet.

Configuration and Filters

In WebApi Extension 2.1 we added the ability for the extension to register the MicroLiteSession attribute globally to save you having to apply it to each controller or action which you wanted an ISession/IReadOnlySession opening for. This was done by passing a boolean value for registerGlobalFilter to the WithWebApi configuration extension.

The MicroLiteSession attribute has been moved into the Filters namespace and 2 more filters have been added, one to verify Api method parameters are not null and another to validate them if they are decorated with DataAnnotation attributes.

The configuration of the extension has also been updated, it is now required to specify a WebApiConfigurationSettings class. The WebApiConfigurationSettings class currently exposes 3 properties:

RegisterGlobalMicroLiteSessionAttribute - defaults to true
RegisterGlobalValidateModelNotNullAttribute - defaults to true
RegisterGlobalValidateModelStateAttribute - defaults to true

If you are happy to use the defaults, you can simply tell MicroLite to use the default settings:

Configure.Extensions().WithWebApi(WebApiConfigurationSettings.Default);

otherwise, simply specify the values you wish to use:

Configure.Extensions().WithWebApi(new WebApiConfigurationSettings
    {
        RegisterGlobalMicroLiteSessionAttribute = false
    });

Registering these filters at a global level means that they are called for every action on every controller and you don’t have to have the following at the start of every method on every controller.

public void Post(Customer customer)
{
     if (customer == null)
     {
          throw new ArgumentNullException("customer");
     }

     if (!this.ModelState.IsValid)
     {
          return this.Request.CreateResponse(HttpStatusCode.BadRequest, this.ModelState);
     }
}

Remember that even though the MicroLiteSession attribute is registered globally, you can still override its default behaviour on a per controller or per action basis by applying the attribute to a class/method. This is probably something you don’t need to do very often and can be left to the infrastructure to open and assign a session for your controller and begin/commit a transaction around each action.

MicroLite 4.0 Released

MicroLite 4.0.0 has been released on NuGet.

Changes/Enhancements

The most important change to be aware of is that the default mapping convention has been changed from the Attribute based mapping convention to the Convention based mapping convention we introduced in 3.0.

If you want to continue using the attribute based mapping, simply tell MicroLite to use it when you configure MicroLite:

Configure
    .Extensions() // If used, load any logging extension first.
    .WithAttributeBasedMapping();

You will also need to specify this prior to any tests which use the SqlBuilder (see Unit Testing when using the Convention Based Mapping (adjust as necessary to register the attribute based mapping instead).

Listeners

Another important change and one to be aware of if you have created any custom IListeners, is that they are now managed as singletons rather than an instance per session as was previously the case.

Convention Based Mapping

The convention based mapping has been updated with more configurable support for tweaking the conventions to suit your preferred conventions. There will be a separate blog post detailing the changes.

Projections and Dynamic values

As you will see further in another blog post, the support for projections and dynamic values has been greatly enhanced. The dynamic keyword can now be used with any of 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 this, it is also possible to specify primative types in any method listed above. For example:

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

SqlBuilder

The SqlBuilder has been enhanced with a number of new methods, notably enhanced query support for individual columns and also SQL formatting.

.Where("Column").IsEqualTo("FOO");
.Where("Column").IsGreaterThan(10);
.Where("Column").IsGreaterThanOrEqualTo(10);
.Where("Column").IsLessThan(10);
.Where("Column").IsLessThanOrEqualTo(10);
.Where("Column").IsLike("FOO%"); // User is responsible for wildcards in LIKE.
.Where("Column").IsNotEqualTo("FOO");
.Where("Column").NotIn(1, 2, 3);
.Where("Column").NotIn(subQuery);
.Where("Column").IsNotNull();
.Where("Column").IsNull();

It also now supports chaining multiple SQL Functions (Count, Min etc)

SqlBuilder.Select("Column1").Count("Column2").Max("Column3").From...

There will be a separate blog post going into more detail about these changes.

ISession.Update

The method return type for ISession.Update is now bool instead of void.

Inflection Service

The inflection service mentioned in has been re-designed to allow globalization. There is a default implementation of IInflectionService which provides English inflection services (this is the default and currently only behaviour provided) however you can specify your own implementation of IInflectionService to provide support for your own language. See the blog post about the updates to convention based mapping for further information.

Obsolete

The IReadOnlySession.Projection method has been obsoleted and the functionality moved to ISession.Fetch(), the Projection method will be removed in MicroLite 5.0.

Bug Fixes

Issue #206 – Session.Paged errors if the query includes a sub query.
Issue #212 – SqlBuilder.Between and .In are Dialect Specific.
Issue #223 – SqlBuilder Between is not appending AND or OR.
Issue #224 – SqlBuilder SingleColumn predicates not appending AND or OR.
Issue #227 – PagedResults.TotalPages returns incorrect value.
Issue #228 – SqlBuilder.AndWhere and SqlBuilder.OrWhere don’t escape column name.