Category Archives: Tutorial

MicroLite.Extensions.WebApi 6.6.0

The OData query parsing library which was spun out of the MicroLite WebApi extension has undergone quite a few changes and some of those have been incorporated into the MicroLite WebApi extension.

The main difference is that the OData query support has been split out into a separate library so if you want to use WebApi controllers but don’t use OData, you no longer have a dependency on the Net.Http.WebApi.OData library. Starting with version 6.6.0 of the MicroLite WebApi extension, there are now 2 packages available:

What do I need to do?

“I use the WebApi extension and want OData 4.0!”

  1. PM> Install-Package MicroLite.Extensions.WebApi.OData -Version 6.6.0

“I use the WebApi extension and don’t use OData query support”

  1. PM> Install-Package MicroLite.Extensions.WebApi -Version 6.6.0
  2. PM> Uninstall-Package Net.Http.WebApi.OData

Query Option Changes
$inlinecount=allpages has been replaced by $count=true.

The JSON response for paged results ($count=true) has changed as follows:

  1. data.__count is now data['@odata.count']
  2. data.results is now data.value

Function Changes
The substringof function has been replaced by contains in the $filter query option so substringof('Alfreds', CompanyName) would become contains(CompanyName, 'Alfreds')

Data Type Changes:
The DateTime type has been removed, there is a new Date type for dates only which is a literal and doesn’t need a type prefix or quotes $filter=DateOfBirth eq 1974-08-19. To specify date and time, use DateTimeOffset literal instead which also no longer requires a type prefix or quotes $filter=Created gt 2002-10-15T17:34:23Z don’t forget the timezone!.

The Guid type no longer requires a prefix or quotes TransactionId eq guid'0D01B09B-38CD-4C53-AA04-181371087A00' becomes TransactionId eq 0D01B09B-38CD-4C53-AA04-181371087A00

The library now requires the entity model to be defined, see the Wiki for further details.

Advertisements

Matching Database Types

In order for SQL queries to perform as fast as possible, it is important to ensure that the types you use in your .NET code match the types you use in your database. This is especially important when you have indexes because if the data type of the SQL parameter doesn’t match the data type of the column, the index for that column won’t be used!

Consider the following table and class:

CREATE TABLE [dbo].[Customers]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Forename] NVARCHAR(50) NOT NULL,
    [Surname] NVARCHAR(50) NOT NULL,
    [DateOfBirth] DATETIME2 NOT NULL,
    [CustomerStatusId] INT NOT NULL
)
public class Customer
{
    public int Id { get; set; }
    public string Forename { get; set; }
    public string Surname { get; set; }
    public DateTime DateOfBirth { get; set; }
    public CustomerStatus Status { get; set; }
}

The default configuration of MicroLite is to map string to DbType.String (NVARCHAR) since .NET strings are unicode. If you don’t use unicode columns in your database, you should change the default type mapping for strings as follows:

// Map strings as ANSI instead of unicode
TypeConverter.RegisterTypeMapping(typeof(string), DbType.AnsiString);

Also, the default mapping for DateTime is to DbType.DateTime so if you use DATETIME2 columns, you should change the default mapping as follows:

// Always use DbType.DateTime2 instead of DbType.DateTime for System.DateTime
TypeConverter.RegisterTypeMapping(typeof(DateTime), DbType.DateTime2);

Note, in MicroLite 7 we plan to change the default DateTime mapping to DbType.DateTime2.

You can see the full set of default mappings Here

Comparing which is better

I received an email asking

I am comparing “MicroLite 6.2.5” with EF & nhibernate for PostgreSQL.

May I ask you in two lines, why “MicroLite 6.2.5” is better?

Unfortunately it’s not actually possible to answer that question without actually defining how are we measuring “better”.

What would have been more sensible would have been to ask “How are they different?” and then compare the features each offers and the pros and cons to decide which is better for you.

MicroLite OData update

The latest release of MicroLite.Extensions.WebApi (6.1) and Net.Http.WebApi.OData (3.1) now provide support for nearly the entire OData 3 spec that can be supported by MicroLite.

It is now possible to compare 2 properties:

$filter=FirstName eq LastName

And call nested functions:

$filter=concat(concat(City, ', '), Country) eq 'Berlin, Germany'

Decimal/Double and Single values don’t require .0 for whole values:

$filter=Price eq 5M can now be used as well as $filter=Price eq 5.0M

Method parameters no longer require a space after the comma:

$filter=endswith(CompanyName,'Futterkiste’) can now be used as well as
$filter=endswith(CompanyName, 'Futterkiste')

Literal strings (text wrapped in single quotes) can now contain a single quote by escaping it with another single quote:

$filter=LastName eq ‘O’’Brien’

Grouping is now supported in queries:

$filter=LastName eq 'Smith' and (Title eq 'Mr' or Title eq 'Mrs')

Without the grouping, the query would be translated as:

LastName = Smith and Title = Mr
Or
Title = Mrs

There is no concept of an “in list” in OData so a grouped set of “or”s is the way to achieve the same result.

endswith, startswith, and substringof now no longer require eq true at the end:

startswith(CompanyName,’Alfr’) is the same as startswith(CompanyName,’Alfr’) eq true

The not operator has also been implemented which means you can negate function calls:

not startswith(CompanyName,’Alfr’)

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.0 – String Handling

In .NET, strings are Unicode, however some databases have specific data types for strings which differentiate between ASCII and Unicode (e.g. in MS SQL Server VarChar being ASCII and NVarChar being Unicode).

If the database makes the distinction and the wrong type is used in a SQL command, it can result in poor performing queries (table scans over index lookups or unnecessary type conversion).

In MicroLite 5.0, the database connection/command handling has been exposed via a ‘database driver’. The new IDbDriver interface defines a HandleStringsAsUnicode property which is set to true by default but allows explicit control over whether strings are sent to the database as ASCII or Unicode (the default).

If you use MS SQL Server and the columns are NVarChar, HandleStringsAsUnicode should be left as true (the default value). If they are VarChar, HandleStringsAsUnicode should be set to false.

For other databases, refer to the documentation and if in doubt, leave it set to true.

If you need to, you can set HandleStringsAsUnicode to false when the session factory is created:

var sessionFactory = Configure.Fluently()
    .ForMsSqlConnection("Connection")
    .CreateSessionFacory();

// Only change this if you are sure you need to.
sessionFactory.DbDriver.HandleStringsAsUnicode = false;

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);