MicroLite 2.0 Dynamic Projections

The new dynamic projections in MicroLite make it easier to show ad-hoc data. Prior to version of MicroLite 2.0, the main way to project data was to create a view which you mapped a class to.

In MicroLite 2.0, as long as you are using the .net 4.0 build, you can simplify things by using the ISession.Advanced.Projection method. This method will accept an SqlQuery and return an IList<dynamic>. Each dynamic in the list will correspond to a single row in the results and each property on the object will map to a column in the results.

Here are a couple of examples to give you an idea of how you could use this new feature.

If we only want to show a couple of columns from a table, we can just read them instead of reading entire objects.

using (var session = sessionFactory.OpenSession())
{
    // Create an ad-hoc query, this could select a number of columns accross multiple tables if desired.
    var query = new SqlQuery(&amp;quot;SELECT Name, DoB FROM Customers&amp;quot;);

    // The results of the projection will be an IList&amp;lt;dynamic&amp;gt;
    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.Name);
        Console.WriteLine(item.DoB);
    }
}

If we wanted a summary which included a calculated value, we could do something like this:

Note that in order for this to work properly, it is essential to alias the calculated column in order for it to be used as a property value.

using (var session = sessionFactory.OpenSession())
{
    var query = new SqlQuery(@&amp;quot;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&amp;quot;);

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

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

It is important to note that the property names are case sensitive when matched to column names.

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

Advertisements

2 thoughts on “MicroLite 2.0 Dynamic Projections

  1. Pingback: MicroLite 4.0 – Changes to Projections and Dynamics | MicroLite ORM

  2. Pingback: MicroLite 5.2 upcoming features | 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