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'");
            });

Pinup

And the generated SQL is... ? – Smoke

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)");
});

Doctor

So looks like I am missing the left and right parens for one. Does Id represent the Id mapping in the table or is the the column name? – Pinup

column name since it is "arbitrary sql" – Doctor

ok. So then how would I do something like this SELECT Field FROM MyTable WHERE MyTable.MyFK={TheFKValue} AND MyTable.Type={TheType}? The stuff in the curly braces being values that should be replaced with values supplied from the entity. Is there even a way? – Pinup

like i said in my answer, all columns without prefixes are treated as columns of the parent table – Doctor