Building truly dynamic queries

Question

I have an existing plain SQL based query and I want to translate it into one or more nHibernate expression. The SQl expression looks like as follows:

string SQL = "SELECT DISTINCT id FROM basetable ";
List<string> conditions...
if( expression_1 ) {
  SQL += "JOIN table_1 ON ...";
  SqlParameters.Add("fooId", _fooId);
}
...
if( expression_n ) {
  SQL += "JOIN table1 ON ...";
  SqlParameters.Add("barId", _barId);
  conditions.Add("field = @barId");
}
...
SQL += "WHERE " + string.Join(" AND ", conditions.ToArray());

The result gonna be a huge SQL expression. Is it any opportunity to translate this kind of code to nHibernate expression? The performance doesn't make sense in this case.

Answer 1

It really depends on how dynamic you want the query to be, but to give you an idea, the following is fairly easy and can be expanded to meet much more complex requirements:

EntityA aAlias = null;
EntityB bAlias = null;
// Build the base query, joining all the tables that you may need
var query = session.QueryOver<EntityA>(() => aAlias)
    .JoinAlias(() => aAlias.ListOfB, () => bAlias);

// Add conditions depending on your requirements, e.g. filter criteria passed from an external source
if (expression1) 
{
    query = query.Where(aAlias.SomeId == someId);
}
if (expression2) 
{
    query = query.WhereRestrictionOn(() => bAlias.SomeOtherId).IsIn(listOfIds)
}
// and so on...
// at the end, just execute the query to get a list of strings or whatever you want
var result = query
    .Select(x => aAlias.Id)
    .List<string>();

Depending on your scenario, you could put the construction of the base query (i.e., the joined tables) into derived classes and the code for shared criteria into a base class. You just need to remember to use the same variable names for the Aliases when passing the query as a parameter to other methods.

We use something like that to build queries based on dynamic search criteria, including date ranges, sub queries, and paging.