NHibernate, extend IQueryable with SQL method by extending DefaultLinqToHqlGeneratorsRegistry

Question

I use fluent NHibernate, and I need to randomnize the result from a query, what I want is something like this:

select * from table order by newid()

The way, should be extending the NHibernate IQueryable generator to use a method like QueryableExtension.RandomOrder<T>(this IQueryable<T> list)

By the blog here: http://fabiomaulo.blogspot.dk/2010/07/nhibernate-linq-provider-extension.html and this: Extending LINQ to Nhibernate provider, in combination with Dynamic LINQ problem

I wrote this code:

public class RandomOrderGenerator : BaseHqlGeneratorForMethod
{
    public RandomOrderGenerator()
    {
        SupportedMethods = new[]
        {
            ReflectionHelper.GetMethod(() => Enumerable.Empty<object>().AsQueryable().RandomOrder()),
            ReflectionHelper.GetMethod(() => Enumerable.Empty<long>().AsQueryable().RandomOrder()),
        };
    }

    public override HqlTreeNode BuildHql(MethodInfo method, System.Linq.Expressions.Expression targetObject, ReadOnlyCollection<System.Linq.Expressions.Expression> arguments,
        HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
    {
        throw new NotImplementedException();
    }
}

public class MyLinqToHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
{
    public MyLinqToHqlGeneratorsRegistry()
    {
        RegisterGenerator(ReflectionHelper.GetMethod(() => Enumerable.Empty<object>().AsQueryable().RandomOrder()), new RandomOrderGenerator());
    }
}

I have configured to use the MyLinqToHqlGeneratorsRegistry, it gets created, my RandomOrderGenerator gets created, but the BuildHql method is never called.

The use of the extension:

repository.Query<Table>().Take(10).RandomOrder().Select(x => x.Id);

The SupportMethods and RegisterGenerator method definitions should be the the same, but why can't I get it to generate the HQL?

Answer 1

Now with the solution.

I could not get the RandomOrder extention to work but then I read this: https://nhibernate.jira.com/browse/NH-3386

Here there was the LinqExtensionMethod, that would call the method automatic in sql. The problem here is when the expression isnt depended on anything specific from the database, it gets compiled locally, to get by this is passing something database specific to the method.

repository.Query<Table>().Take(10).OrderBy(x => OrderType.Random(x.Id))...

LinqExtensionMethods gets handled by the DefaultLinqToHqlGeneratorsRegistry and also maps arguments to the SQL method, pretty neat just not what we want. Now the sql looks like this: select....newid(table.id)

To fix this we need to map this ourselves so we can ignore the argument, so away with Attribute and create a hql generator:

public class RandomOrderHqlGenerator : BaseHqlGeneratorForMethod
{
    private readonly string _name;
    public RandomOrderHqlGenerator()
    {
        _name = "NewId";
    }
    public override HqlTreeNode BuildHql(MethodInfo method, System.Linq.Expressions.Expression targetObject, ReadOnlyCollection<System.Linq.Expressions.Expression> arguments,
        HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
    {
        return treeBuilder.MethodCall(_name);
    }
}

RegisterGenerator(ReflectionHelper.GetMethodDefinition(() => OrderType.Random(null)), new RandomOrderHqlGenerator());