MicroLite 2.0 Enhancements

MicroLite 2.0 has been released with a number of enhancements:

  • Includes
  • Dynamic Projections
  • Updates to SqlBuilder
  • Updates to PagedResult

Includes

Includes allow additional results to be returned with a single database connection.

Example: Get a Customer record and all his associated Invoice records.

In MicroLite 1.2.2 and earlier, you would have to call Single and Fetch which would result in 2 separate queries to the database.

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

var invoices = session.Fetch<Invoice>(new SqlQuery("SELECT * FROM Invoices WHERE CustomerId = @p0", 12345));

In MicroLite 2.0 the queries can be combined:

// The values to include are all specified first via the ISession.Include property.
var includeInvoices = session.Include.Many<Invoice>(new SqlQuery("SELECT * FROM Invoices WHERE CustomerId = @p0", 12345));

// Calling ISession.Single, ISession.Fetch or ISession.Paged will result in all queries being executed.
var customer = session.Single<Customer>(12345);

// The returned invoices are available under the Values property of the include.
var invoices = includeInvoices.Values;

Dynamic Projections

Sometimes we simply want to show some data to a user which may consist of a number of columns across some joined tables.

In MicroLite 1.2.2 and earlier, the easiest way to do this was to create a view which projected the relevant columns and create a class which mapped to the view. (If you are using the .net 3.5 build of MicroLite 2.0 this is still the case as dynamic types were not introduced until .net 4.0).

As of MicroLite 2.0 in the .net 4.0 build, a new method has been added to the ISession.Advanced options to perform a dynamic projection.

var query = new SqlQuery(@"SELECT Customer.Name AS CustomerName, SUM(Invoices.InvoiceTotal) AS InvoiceTotal
FROM Customers
INNER JOIN Invoices ON Invoices.CustomerID = Customers.CustomerID
GROUP BY Customers.Name
ORDER BY InvoiceTotal DESC");

var results = session.Advanced.Projection(query);

foreach (var item in results)
{
    // The property names of each dynamic result will match (including case) the column names specified in the query.
    Console.WriteLine(item.CustomerName);
    Console.WriteLine(item.InvoiceTotal);
}

Updates to SqlBuilder

There are a number of new methods on SqlBuilder to support functions and grouping, there is also a small (although breaking) change too.

SqlBuilder.SelectFrom(typeof(Customer))..

is now

SqlBuilder.Select("*").From(typeof(Customer))...

The following SQL functions are supported:

  • Average
  • Count
  • Min
  • Max
  • Sum

The functions can be used as follows-

var sqlQuery = SqlBuilder
     .Select()
     .Average("Total")
     .From(typeof(Invoice))
     .Where("CustomerId = @p0", 1022)
     .ToSqlQuery();

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

Updates to PagedResult

The PagedResult class how has 3 extra properties, MoreResultsAvailable, TotalResults and TotalPages.

The total results are calculated by re-writing the original query to perform a SELECT COUNT(*) in addition to the actual query (this is actually done as an Include so there is only one connection and one command used).

The total pages are calculated as total results divided by the requested results per page.

The next few blog posts will go into these in further detail.

There is also a compiled help which can be downloaded from the MicroLite Downloads on the GitHub site.

Advertisements

2 thoughts on “MicroLite 2.0 Enhancements

  1. Pingback: Introducing the SqlBuilder | MicroLite ORM

  2. Pingback: SqlBuilder Updates | MicroLite ORM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s