<charliedigital /> Programming, Politics, and uhh…pineapples

FluentNHibernate And NHibernate.Linq

Just a little blurb on FluentNHibernate and NHibernate.Linq.

I've been working through the samples for FNH and decided to try out some different query scenarios to see how the queries would be generated. I stumbled a bit on the first rather simple scenario: selecting an item based on the total count of related items. In this case, from the FNH demo, I wanted to select "all stores with more than 2 employees". Seems like a simple enough query, right?

You can see from the full code that this should return "Bargain Basement". However, in going through the documentation, it wasn't exactly apparent how this could be done; it seemed a lot more convoluted than necessary using criteria queries and it simply wasn't working. Via Google, I came across a blog post that mentioned using either DetachedCriteria or HQL. Quite frankly, neither was very appealing.

So I figured I'd download NHibernate.Linq and see if it would be better. Not knowing what I would get, SQL-wise, I wrote the following query:

using (session.BeginTransaction())
{
    IQueryable<Store> stores = from s in session.Linq<Store>()
                               where s.Staff.Count > 2
                               select s;

    foreach (Store store in stores)
    {
        Console.WriteLine("STORE: {0}", store.Name);
    }
}

Of course, the interesting question is whether the the Staff list would be loaded to perform the count and to my pleasant surprise, it was not. Here's the query in profiler (reformatted for legibility):

exec sp_executesql N'
    SELECT 
        this_.Id as Id1_0_, 
        this_.Name as Name1_0_ 
    FROM 
        [Store] this_ 
    WHERE 
        @p0 <   (
                SELECT 
                    count(staff1_.Id) as y0_ 
                FROM [Store] this_0_ 
                    left outer join [Employee] staff1_ 
                        on this_0_.Id=staff1_.Store_id 
                WHERE 
                    this_.Id = this_0_.Id
                )',N'@p0 int',@p0=2

Sweet! The framework correctly builds a sub-select query to count the staff members.

I guess I'm just easy to impress but I'm digging it.

I think what I like about FluentNHibernate the most is that I can stop building database applications. What I mean by this is an application built from the database up. The main issue this raises is complexity with regards to mapping to a data layer and of course continuously having to synchronize your DDL and SQL with your class files. The following code configures the database, including dropping and creating the tables based on mapping classes in my assemblies:

private static ISessionFactory CreateSessionFactory()
{
    return Fluently.Configure()
        .Database(
        MsSqlConfiguration.MsSql2008.ConnectionString(
            @"Data Source=SCOOBY;Initial Catalog=FluentNHDemo;
            Integrated Security=SSPI;Application Name='FNHDemo'"))
        .Mappings(m =>
                  m.FluentMappings.AddFromAssemblyOf<Store>())
        .ExposeConfiguration(BuildSchema)
        .BuildSessionFactory();
}

private static void BuildSchema(Configuration config)
{
    // this NHibernate tool takes a configuration (with mapping info in)
    // and exports a database schema from it
    SchemaExport schema = new SchemaExport(config);

    schema.Drop(false, true); // Drops the tables only.
    schema.Create(false, true);
}

With no mapping files to speak of, persistence plumbing becomes trivial and I can work entirely within Visual Studio. Of course, for complex queries and queries that need to be highly performant, you may still be better off writing stored procedures (as I've advocated in the past), but the productivity gains to be had can't be ignored and I find the compile-time validation of the queries eases some of my indifference towards dynamic SQL.

About Charles Chen

I'm a senior consultant with Paragon Computer Professionals. In a previous life, I was the lead developer at a startup building what became CSC's FirstPoint.