Use of Cast Projection inside a Concat Projection

Question

I have the following query:

var result = _session.QueryOver<Entity>()
    .Where(e => e.Property == value)
    .SelectList(list => list
        .Select(f => Projections.Concat("prefix-", e.BigIntProperty)).WithAlias(() => alias.Whatever)
        ...
    )
    .TransformUsing(Transformers.AliasToBean<Model>())
    .Future<Model>();

The problem is that Projections.Concat() accepts only strings and since e.BigIntProperty is not, the above doesn't compile. Is there a way to cast e.BigIntProperty to string?

I tried something like the following, which doesn't work either:

.Select(f => Projections.Concat("prefix-", Projection.Cast(NHibernateUtil.String, e.BigIntProperty))).WithAlias(() => alias.Whatever)

, since Projections.Cast returns an IProjection and not a string.

Answer 1

Projections.Cast seems terribly limited in that it can't take arbitrary Projections. Luckily you can easily create your own custom projection that enables you to do that:

public static class CustomProjections
{
    public static IProjection Concat(params IProjection[] projections)
    {
        return Projections.SqlFunction(
            "concat",
            NHibernateUtil.String,
            projections);
    }
}

Then, you'll be able to use your CustomProjections class like this:

var result = _session.QueryOver<Entity>()
    .Where(e => e.Property == value)
    .SelectList(list => list
        .Select(CustomProjections.Concat(
            Projections.Constant("prefix-"),
            Projections.Cast(
                NHibernateUtil.String,
                Projections.Property<Entity>(e => e.BigIntProperty))))
            .WithAlias(() => alias.Whatever)
        ...
    )
    .TransformUsing(Transformers.AliasToBean<Model>())
    .Future<Model>();

Answer 2

I've already accepted Andrew's answer, but just for reference, you could use Projections.SqlFunction("concat", ...) directly which solves the whole issue since it can take IProjection's as arguments and not only string.

var result = _session.QueryOver<Entity>()
    .Where(e => e.Property == value)
    .SelectList(list => list
        .Select(Projections.SqlFunction("concat",
            NHibernateUtil.String,
            Projections.Constant("prefix-"),
            Projections.Cast(NHibernateUtil.String, Projections.Property<Entity>(e => e.BigIntProperty))))               
        .WithAlias(() => alias.Whatever)
        ...
     )
    .TransformUsing(Transformers.AliasToBean<Model>())
    .Future<Model>();

NOTE: It seems that when calling either Projections.Concat(...) or Projections.SqlFunction("concat", ...), the query that is produced actually uses the + operator, e.g.:

SELECT (a + b) as foo FROM table

instead of:

SELECT concat(a, b) as foo FROM table

Of course, CONCAT is only available from MS SQL Server versions 2012 and above, so this is correct. Possibly the MsSQl2012Dialect could make use of the CONCAT, since CONCAT doesn't require that the arguments are varchar, they might as well be integers.

Unfortunately MsSQl2012Dialect doesn't do that, but it is very easy to build a custom Dialect:

public class CustomMsSql2012Dialect : MsSql2012Dialect
{
    protected override void RegisterFunctions()
    {
        base.RegisterFunctions();
        base.RegisterFunction("concat", new VarArgsSQLFunction(NHibernateUtil.String, "concat(", ",", ")"));
    }
}

So, if you use version 2012 or above and you declare the above as your Dialect, you can ditch the Projections.Cast(...) part