Dynamic SQL with NHibernate

The purpose of this exercise is to query data from a database using "real" (dynamic) objects without doing any manual Table to Entity mapping. Helping with this we are using NHibernate framework for actual queries and database datatype to .Net datatype mapping and we are just wrapping over it our dynamic result set.

I'm using sample data from Microsoft's sample database Adventure Works 2008 that you can download from here.

I'm also using the Visual Studio 2010 RC that you can download here. Most likely the code will work on beta 2, but I haven't tested it with it.

Also, we are specifically using Fluent NHibernate 1.0 for connection strings and basic NHibernate configuration. Fluent NHibernate's main purpose is to use code to replace NHibernate's hbm-xml mapping files. It's very elegant solution and if you're already using NHibernate, check it out. Say "No more!" to xml.

-==- Querying with NHibernate -==-

You can create custom queries with NHibernate without mapping simply using ISession's CreateSQLQuery function.

Creating a session factory for MSSQL 2008 with Fluent NHibernate:

private static ISessionFactory CreateSessionFactory(string connectionString) {
  return Fluently.Configure().Database(
    FluentNHibernate.Cfg.Db.MsSqlConfiguration.MsSql2008.ConnectionString(connectionString)
    )
    .BuildSessionFactory();
}

You can of course use any database supported by NHibernate, like MySQL.

Connecting to the database and making an SQL query: (Note. I'm assuming the database is in localhost.)

var sessionFactory = CreateSessionFactory("Server=localhost;Database=AdventureWorks;Trusted_Connection=True;");

string query = "SELECT [DepartmentID], [Name], [GroupName], [ModifiedDate] FROM [AdventureWorks].[HumanResources].[Department]";

using (var session = sessionFactory.OpenSession()) {

  var query = session.CreateSQLQuery(query);

  // returns a list of object arrays (object[]). The objects in object array are the "true" formats of the column, converted by NHibernate.
  // for example, ModifiedDate is a DateTime column and it's converted to .Net DateTime object, 
  // as you can see from below formatting of {3:dd.MM.yyyy} --> result[3].
  var results = q.List<object>();
  foreach (var result in results) {
    System.Console.Writeline("{0} {1} {2} {3:dd.MM.yyyy}", result[0], result[1], result[2], result[3]);

  }
} 

Unfortunately, without mapping, we get only a list object arrays. Next, we'll check out how we can use dynamic keyword to our advantage.

-==- Columns -==-

NHibernate doesn't provide metadata on the resulting columns, so we have to either deduce them from the query or provide them ourselves. This limits our automation options as, for example, "select * from table" is not sufficient for automatic mapping.

I wrote a simple column deducer using regular expressions, but as they say, when using them to solve a problem, now instead you've got two problems :) It works on simple queries but full SQL-clause parsing (for all database engines supported by NHibernate) was beyond the scope of this exercise. You can see what I did in class ColumnDeducer that's include in source. Anyway, what we need from it are two things: 1) the original column name and 2) "code friendly" column name. "Code friendly" columns are used in dynamic objects when accessing values in rows, because column naming limits are not the same in databases then they are in code.

-==- Dynamic queries -==-

Now, lets turn our previous query example to dynamic:

var sessionFactory = CreateSessionFactory("Server=localhost;Database=AdventureWorks;Trusted_Connection=True;");

string query = "SELECT [DepartmentID], [Name], [GroupName], [ModifiedDate] FROM [AdventureWorks].[HumanResources].[Department]";

List<string> columns = new List<string>() { "DepartmentID", "Name", "GroupName", "ModifiedDate" };

using (var session = sessionFactory.OpenSession()) {

  var query = from dynamic d in session.CreateSQLQuery(query).List<dynamic>()
  // convert the result to DynamicRow entities
  select (dynamic)new DynamicRow(columns, q);

  foreach (var result in query) 
  {
    System.Console.Writeline("{0} {1} {2} {3:dd.MM.yyyy}", result.DepartmentID, result.Name, result.GroupName, result.ModifiedDate);
  }
}

Here we provided manually the column names, but in a query like this where the columns are clearly provided, we can simply say:

columns = new ColumnDeducer().DeduceColumns(query);

And as an alternative to using properties on DynamicRow, you can also use the indexer:

System.Console.WriteLine("{0}", result["DepartmentID"]);

This also works when using queries that has column names that are not valid properties, like

SELECT [DepartmentID] as [#departmentid] FROM [AdventureWorks].[HumanResources].[Department]

System.Console.WriteLine("{0}", result["#DepartmentID"]);

-==- Extension methods -==-

To make querying as easy as possible, I wanted to provided a simpler way then the one described above. Using .Net extension methods, we can do this:

public static IEnumerable<dynamic> DynamicQuery(this ISession session, string query)
{
  List<ColumnName> list = null;
  return session.DynamicQuery(query, ref list);
}

public static IEnumerable<dynamic> DynamicQuery(this ISession session, string query, ref List<ColumnName> columnNames)
{
  if (columnNames == null)
  {
    columnNames = new ColumnDeducer().DeduceColumns(query);
  }

  List<ColumnName> tmpList = columnNames;
  var baseQuery = session.CreateSQLQuery(query).List<dynamic>();

  return from dynamic q in baseQuery
         select (dynamic)new DynamicRow(tmpList, q);

}

And then we can simply use it like this:

var results = session.DynamicQuery(query);

-==- Parameters -==-

Now, one thing we haven't touched here are query arguments. Normally you would use IQuery interface returned by session.CreateSQLQuery to add arguments.

string query = "SELECT [DepartmentID], [Name], [GroupName], [ModifiedDate] FROM [AdventureWorks].[HumanResources].[Department] where [DepartmentID] = :id"; 

var query = session.CreateSQLQuery(query)
                   .SetInt32("id", 1);

To help with arguments, I added another extension method that would convert an existing (and parametrized) IQuery to a dynamic result:

public static IEnumerable<dynamic> AsDynamicQuery(this IQuery query, ISession session)
{
  List<ColumnName> columnNames = new ColumnDeducer().DeduceColumns(query.QueryString);

  var baseQuery = query.List<dynamic>();

  return from dynamic q in baseQuery
         select (dynamic)new DynamicRow(columnNames, q);
}

So then you can convert the previous query to:

string query = "SELECT [DepartmentID], [Name], [GroupName], [ModifiedDate] FROM [AdventureWorks].[HumanResources].[Department] where [DepartmentID] = :id"; 

var query = session.CreateSQLQuery(query)
                   .SetInt32("id", 1)
                   .AsDynamicQuery(session);

Not the most elegant solution, but IQuery didn't provide information on the session so we have to use it twice on this occasion.

-==- DynamicRow -==-

The actual DynamicRow class I've mentioned above is a very simple construct. We provide for it the column names and data and implement DynamicObject's TryGetMember and TryGetIndex methods. If you haven't read my previous "dynamic" post(s), TryGetMember is used when dynamic object's property is being accessed:

dynamic d = ...
string value = d.Property;

And similarly, TryGetIndex when using the indexer:

dynamic d = ...
string value1 = d[0];
string value2 = d["hello"];

So, the DynamicRow class itself:

public class DynamicRow : DynamicObject
{
  protected object[] Items { get; set; }
  protected List<ColumnName> Columns { get; set; }

  public DynamicRow(List<ColumnName> columns, object row)
  {
    Columns = columns;

    // when NHibernate select returns a single result, like select 'hello' from table, it is returned as object. 
    // Multiple objects are in object array.
    if (row is object[])
    {
      Items = row as object[];
    }
    else
    {
      // wrap single object in a object array
      Items = new object[] { row };
    }
  } 

... 

} 

TryGetMember:

public override bool TryGetMember(GetMemberBinder binder, out object result)
{
  // binder.Name is the called property getter's name.
  result = GetValueByName(binder.Name);
  return true;
}

protected object GetValueByName(string name)
{
  // find the column index by the "code friendly" name.
  int foundIndex = Columns.FindIndex(s => s.CodeFriendlyColumnName == name);

  if (foundIndex != -1)
  {
    return GetValueByIndex(foundIndex);
  }
  else
  {
    throw new IndexOutOfRangeException();
  }
}

protected object GetValueByIndex(long index)
{
  return Items[index];
}

Using the indexer is a bit more complicated as you can provide either a numeric value or a string value for the indexer:

public override bool TryGetIndex(GetIndexBinder binder, object[] indexes, out object result)
{
  if (indexes != null)
  {
    if (indexes.Length != 1) throw new IndexOutOfRangeException("Only 1 indexer supported.");

    if (indexes[0] is int || indexes[0] is long)
    {
      result = GetValueByIndex((long)indexes[0]);
      return true;
    }
    else
    {
      result = GetValueByName(indexes[0].ToString());
      return true;
    }
  }
  result = null;
  return false;
}

After this, you can wrap a result to dynamic:

List<ColumnName> columns = new List<ColumnName>() { 
  new ColumnName { CodeFriendlyColumnName = "FirstName" },
  new ColumnName { CodeFriendlyColumnName = "TimeNow" }
};

object[] row = new object[] { "Jack", DateTime.Now };

dynamic dynamicRow = new DynamicRow(columns, row);

System.Console.WriteLine("Hello {0}, time is now {1:dd.MM.yyyy}", dynamicRow.FirstName, dynamicRow.TimeNow);

-==- Caveats -==-

Using NHibernate's CreateSQLQuery will always return the full result, thus when using "select * from table" could end up using quite a bit of memory, so be careful with your queries and memory usage and remember to dispose the results after using them.

So, instead of using Linq group by operators on full data sets, you might want to use native SQL group by operators.

-==- Source -==-

Source code can be found here.