Aggregation with NHibernate.FlowQuery

Aggregation is quite easy in NHibernate.FlowQuery (FlowQuery) but in can be a bit unclear how to do it. This post is supposed to make it a bit clearer by giving a couple of examples.

With FlowQuery all aggregation is done by using the Aggregate-helper, like:

session.FlowQuery<User>()
    .Select(x => new
    {
        Average = Aggregate.Average(x.Id),
        Max = Aggregate.Max(x.Id),
        Min = Aggregate.Min(x.Id)
    });

This will return a FlowQuerySelection<Anonymous> containing one item that holds the selected aggregations.

Quite easy, right?

To group by a certain property, you also have to select that certain property in a GroupBy-aggregation, like:

session.FlowQuery<User>()
    .Select(x => new
    {
        Average = Aggregate.Average(x.Id),
        Max = Aggregate.Max(x.Id),
        Min = Aggregate.Min(x.Id),
        GroupedBy = Aggregate.GroupBy(x.Firstname)
    });

Now the aggregation will be grouped by Firstname and the returned FlowQuerySelection<Anonymous> will no longer contain only one item but instead one item for each unique Firstname in the database table.

Enjoy! (:

/Niklas Källander

Selecting distinctly never was easier

Hi all,

Do you often select data distinctly using the NHibernate ICriteria API? If so, aren’t you sick of all the garbage you need to type in order to do that? Well, it has never been easier now that NHibernate.FlowQuery (FlowQuery) has come to your rescue.

It wasn’t a long time ago I had to write code similar to this:

var users = session.CreateCriteria<User>()
    .SetProjection
    (
        Projections.Distinct
        (
            Projections.ProjectionList()
                .Add(Projections.Property("Id"), "Id")
                .Add(Projections.Property("Username"), "Username")
                .Add(Projections.Property("Email"), "Email")
        )
    )
    .SetResultTransformer(Transformers.AliasToBean<User>())
    .List<User>();

Note that you have to specify “<User>” three times and each property name two times, ewk.

Or in this style which obviously came back and hit me in the face (performance-wise):

var users = session.CreateCriteria<User>()
    .SetProjection
    (
        Projections.ProjectionList()
            .Add(Projections.Property("Id"))
            .Add(Projections.Property("Username"))
            .Add(Projections.Property("Email"))
    )
    .SetResultTransformer(Transformers.DistinctRootEntity)
    .List<User>();

Note that you still have to specify “<User>” two times but you only need to specify the property names once, a little improvement syntax-wise, right? It is still awful performance-wise, beware!

Now, when I have FlowQuery, I only have to type this:

var users = session.FlowQuery<User>()
    .Distinct()
    .Select
    (
        x => x.Id, 
        x => x.Username, 
        x => x.Email
    );

Simply says: Construct the users for me but only populate these properties.

Or this:

var users = session.FlowQuery<User>()
    .Distinct()
    .Select(x => new User(x.Username)
    {
        Id = x.Id,
        Email = x.Email
    });

Says: Fetch this data and use this constructor and member initializer.

Or to an anonymous type, this:

var users = session.FlowQuery<User>()
    .Distinct()
    .Select(x => new
    {
        x.Id,
        x.Username,
        x.Email
    });

Says something like: Hey, give me these properties, type? I dunno, just give it to me.

Or if I want or need to, this:

var users = session.FlowQuery<User>()
    .Distinct()
    .Select<User>()
        .For(x => x.Id).Use(x => x.Id)
        .For(x => x.Username).Use(x => x.Username)
        .For(x => x.Email).Use(x => x.Email)
        .Select();

Very handy if some of the properties of the destination type have hidden setters. That extra “<User>” is not necessary in the next version.

What I want to demonstrate here is that with FlowQuery you have a couple of more choices of which all are type-safe, refactoring-friendly and of which all builds the better version of the ICriteria Queries I presented earlier. The best thing yet is that it is as easy as taking a dump in the morning or making a sandwich.

Enjoy! (:

/Niklas Källander

Introducing NHibernate.FlowQuery

Hi all NHibernate users,

Today I finally decided to publish my very close-to-heart wrapper for the NHibernate ICriteria API. NHibernate.FlowQuery (FlowQuery) is based on expression trees and hence provides you with a refactoring friendly interface that can be completely free from “magic strings” even if you have mapped NHibernate to private or protected members in your entities.

FlowQuery provides you with an easy-to-use API for revealing hidden members by configurable conventions that can be altered on the fly. The API also contains auto-mapping functionality to map your query directly into a view-model instead of having you map it yourself.

Examples:

A simple get-all query:

var users = session.FlowQuery<User>().Select();

A simple get-all query which auto-maps public properties with the same name and type into the view-model:

var users = session.FlowQuery<User>().Select<UserViewModel>();

Restrictions:

var users = session.FlowQuery<User>()
    .Where(u => u.Firstname == "Niklas"
             && u.Lastname.StartsWith("K"))
    .Select();

The above query uses the method StartsWith which is part of the .NET String-class. FlowQuery converts this method-call to Restrictions.Like(“Lastname”, “K”, MatchMode.Start). The API also have support for Contains, EndsWith and, depending on database provider, Substring.

Joining:

UserRoleLink link = null;

var users = session.FlowQuery<User>()
    .Inner.Join(u => u.Roles, () => link)
    .Where(u => link.Role == Role.Administrator)
    .Select();

Selects only Id and Email into an anonymous type:

var users = session.FlowQuery<User>()
    .Select(u => new
    {
        u.Id,
        u.Email
    });

Selects only Id and Email but to the same type:

var users = session.FlowQuery<User>()
    .Select(u => u.Id, u => u.Email);

Selects only Firstname, Lastname and Email but by specifying type, constructor and member initializer:

var users = session.FlowQuery<User>()
    .Select(u => new UserViewModel(u.Firstname, u.Lastname)
    {
        Email = u.Email
    });

Revealing hidden members is done using either the Reveal-helper or by instantiating a custom revealer. The Reveal-helper, by default, uses the “m_”-convention.

As an example:

string name = Reveal.ByConvention(() => link.Role);

name would be “m_Role”.

The convention used by the Reveal-helper can be set using Reveal.SetConvention() which takes either a Func<string, string> or an IRevealConvention.

You can also use a custom revealer like:

IRevealer revealer = new Revealer(new UnderscoreConvention());
string name = revealer.Reveal(() => link.Role);

name would be “_Role”.

Revealing in a query:

var users = session.FlowQuery<User>()
    .Inner.Join(Reveal.ByConvention<User>(u => u.Roles), () => link)
    .Where(u => link.Role == Role.Administrator)
    .Select();

The above query reveals the hidden collection m_Roles and joins it into the query.

I think those examples will be sufficient for now. If you like what you see and/or want more information about FlowQuery leave a reply!

The API can be downloaded as a dll at: http://sourceforge.net/projects/nhflowquery/

Enjoy! (:

/Niklas Källander