Using a Property mapping with a Formula in NHIbernate

Question

I am trying to map a property to an arbitrary column of another table. The docs say that the formula can be arbitrary SQL and the examples I see show similar.

However, the SQL NHibernate generates is not even valid. The entire SQL statement from the formula is being injected into the middle of the SELECT statement.

        Property(x => x.Content, map =>
            {
                map.Column("Content");
                map.Formula("select 'simple stuff' as 'Content'");
            });

Answer 1

This is the way Formula is designed, it is supposed to work that way. You need to wrap your SQL statement in parens so that valid SQL can be generated.

Also, you cannot specify Column and Formula together. You must provide the whole SQL statement. Any non prefixed/escaped columns ('id' in the example below) will be treated as columns of the table of the owning entity.

Property(x => x.Content, map =>
{
    map.Formula("(select 'simple stuff' as 'Content')");
});

// or what you probably want

Property(x => x.Content, map =>
{
    map.Formula("(select t.Content FROM AnotherTable t WHERE t.Some_id = id)");
});