Strongly typed ad-hoc SQL queries using anonymous types

Jimmy Bogard wrote a nice post last week in which he used NHibernate to load data into objects without requring a mapping – very nice. The only thing I thought was a bit of a pain was having to define the objects – could I get NHibernate to map data to anonymous objects, defined inline? Yup!

I created an extension method .SqlSelect for the ISession interface.

public static IEnumerable<T> SqlSelect<T>(this NHibernate.ISession session, T t, string tableSql){...}

Here it is in use, selecting all the users with a suspended status.

   1: var users = session.SqlSelect(new {Name = null as string, Email = null as string, id = 0 as int}, "users where status = 'SUSPENDED'");

The properties of the anonymous type (string Name, int? object_id) are found using reflection and used to form the parameters for the select list. The tableSql variable is used to build up everything on the right hand side of the select query. The whole query looks like 'SELECT  {property names from anonymous type} FROM {tableSql}.'

The code for the extension method can be found at http://gist.github.com/464251. It is just a proof of concept, so do whatever you like with it. There is also an method for doing this without NHibernate, just passing a connection string.