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:
|
1 2 3 4 5 6 7 8 9 10 11 |
<span style="font-family: Lucida Console;"><span style="color: #0000ff;">using </span><span style="color: #000000;">(session.BeginTransaction())</span> <span style="color: #000000;">{</span> <span style="color: #000000;">IQueryable<Store> stores = <span style="color: #0000ff;">from</span> s </span><span style="color: #0000ff;">in </span><span style="color: #000000;">session.Linq<Store>()</span> <strong><span style="color: #000000;"><span style="color: #0000ff;">where</span> s.Staff.Count > </span><span style="color: #800080;">2</span></strong> <span style="color: #000000;"><span style="color: #0000ff;">select</span> s;</span></span> <span style="color: #0000ff;">foreach </span><span style="color: #000000;">(Store store </span><span style="color: #0000ff;">in </span><span style="color: #000000;">stores)</span> <span style="font-family: Lucida Console;"> <span style="color: #000000;">{</span> <span style="color: #808000;">Console</span><span style="color: #000000;">.WriteLine(</span><span style="color: #ff00ff;">"STORE: {0}"</span><span style="color: #000000;">, store.Name);</span> <span style="color: #000000;">}</span> <span style="color: #000000;">}</span></span> |
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):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<span style="font-family: Lucida Console;"><span style="color: #000000;">exec sp_executesql N</span><span style="color: #ff00ff;">'</span> <span style="color: #ff00ff;"> SELECT </span> <span style="color: #ff00ff;"> this_.Id as Id1_0_, </span> <span style="color: #ff00ff;"> this_.Name as Name1_0_ </span> <span style="color: #ff00ff;"> FROM </span> <span style="color: #ff00ff;"> [Store] this_ </span> <span style="color: #ff00ff;"> WHERE </span> <span style="color: #ff00ff;"> @p0 < (</span> <span style="color: #ff00ff;"> SELECT </span> <span style="color: #ff00ff;"> count(staff1_.Id) as y0_ </span> <span style="color: #ff00ff;"> FROM [Store] this_0_ </span> <span style="color: #ff00ff;"> left outer join [Employee] staff1_ </span> <span style="color: #ff00ff;"> on this_0_.Id=staff1_.Store_id </span> <span style="color: #ff00ff;"> WHERE </span> <span style="color: #ff00ff;"> this_.Id = this_0_.Id</span> <span style="color: #ff00ff;"> )'</span><span style="color: #000000;">,N</span><span style="color: #ff00ff;">'@p0 int'</span><span style="color: #000000;">,@p0=2</span></span> |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<span style="font-family: Lucida Console;"><span style="color: #0000ff;">private static </span><span style="color: #000000;">ISessionFactory CreateSessionFactory</span><span style="color: #5e82fd;">()</span> <span style="color: #5e82fd;">{</span> <span style="color: #0000ff;">return </span><span style="color: #000000;">Fluently</span><span style="color: #5e82fd;">.</span><span style="color: #ff8040;">Configure</span><span style="color: #5e82fd;">()</span> <span style="color: #5e82fd;">.</span><span style="color: #800080;">Database</span><span style="color: #5e82fd;">(</span> <span style="color: #000000;">MsSqlConfiguration</span><span style="color: #5e82fd;">.</span><span style="color: #000000;">MsSql2008</span><span style="color: #5e82fd;">.</span><span style="color: #800080;">ConnectionString</span><span style="color: #5e82fd;">(</span> <span style="color: #000000;">@</span><span style="color: #ff00ff;">"Data Source=SCOOBY;Initial Catalog=FluentNHDemo;</span> <span style="color: #ff00ff;"> Integrated Security=SSPI;Application Name='FNHDemo'"</span><span style="color: #5e82fd;">))</span> <span style="color: #5e82fd;">.</span><span style="color: #000000;">Mappings</span><span style="color: #5e82fd;">(</span><span style="color: #000000;">m </span><span style="color: #5e82fd;">=></span> <span style="color: #000000;">m</span><span style="color: #5e82fd;">.</span><span style="color: #000000;">FluentMappings</span><span style="color: #5e82fd;">.</span><span style="color: #000000;">AddFromAssemblyOf</span><span style="color: #5e82fd;"><</span><span style="color: #ff8040;">Store</span><span style="color: #5e82fd;">>())</span> <span style="color: #5e82fd;">.</span><span style="color: #000000;">ExposeConfiguration</span><span style="color: #5e82fd;">(</span><span style="color: #000000;">BuildSchema</span><span style="color: #5e82fd;">)</span> <span style="color: #5e82fd;">.</span><span style="color: #000000;">BuildSessionFactory</span><span style="color: #5e82fd;">();</span> <span style="color: #5e82fd;">}</span></span> <span style="font-family: Lucida Console;"><span style="color: #0000ff;">private static void </span><span style="color: #000000;">BuildSchema</span><span style="color: #5e82fd;">(</span><span style="color: #008080;">Configuration </span><span style="color: #000000;">config</span><span style="color: #5e82fd;">)</span> <span style="color: #5e82fd;">{</span> <span style="color: #808080;">// this NHibernate tool takes a configuration (with mapping info in)</span> <span style="color: #808080;">// and exports a database schema from it</span> <span style="color: #000000;">SchemaExport schema </span><span style="color: #5e82fd;">= </span><span style="color: #0000ff;">new </span><span style="color: #000000;">SchemaExport</span><span style="color: #5e82fd;">(</span><span style="color: #000000;">config</span><span style="color: #5e82fd;">);</span></span> <span style="font-family: Lucida Console;"> <span style="color: #000000;">schema</span><span style="color: #5e82fd;">.</span><span style="color: #800080;">Drop</span><span style="color: #5e82fd;">(</span><span style="color: #0000ff;">false</span><span style="color: #5e82fd;">, </span><span style="color: #0000ff;">true</span><span style="color: #5e82fd;">); </span><span style="color: #808080;">// Drops the tables only.</span> <span style="color: #000000;">schema</span><span style="color: #5e82fd;">.</span><span style="color: #ff8040;">Create</span><span style="color: #5e82fd;">(</span><span style="color: #0000ff;">false</span><span style="color: #5e82fd;">, </span><span style="color: #0000ff;">true</span><span style="color: #5e82fd;">);</span> <span style="color: #5e82fd;">}</span></span> |
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.
our copy is on my screen in the font my IE8 says I want.. Not so with the code. The code is in a much smalled fait font. If you (anybody) emails me and asks for "for Charlie", I will send a screenshot showing the difference. gfmueden@verizon.net