Using formula matching with 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 that I see are similar.

However, NHibernate SQL is generated, even invalid. The entire SQL statement from the formula is inserted in the middle of the SELECT .

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

Answer 1

This is the Formula way, it should work this way. You need to collapse the SQL statement in parens so that you can create valid SQL.

In addition, you cannot specify Column and Formula together. You must provide the entire SQL statement. Any columns without prefix / escaping ("id" in the example below) will be considered as columns of the table of the owner object.

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