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.

In this case, much more easier would be to return just BigIntProperty as some int/long property, and create the other property converting that value **anyhow** in C#... — Radim Köhler, Nov 09 '14 at 18:51
Yes, this is the way I currently overcome this (using Linq on the List that is returned), but I'd like to know if there is a 'proper' way to do it. — lalibi, Nov 09 '14 at 20:21

score 4 · Accepted Answer · answered Nov 10 '14 at 14:08

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>();

This is what I was trying to build myself, but I couldn't get it right. I'll include another solution I came up with just for reference. Thanks. — lalibi, Nov 10 '14 at 18:23

lalibi · Answer 2 · 2014-11-10T19:17:07.920

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

Use of Cast Projection inside a Concat Projection

2 Answers2