Using a Property mapping with a Formula in NHIbernate
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)");
});
Brian
I am a professional software engineer that believes software should be intuitive and easy to use without ever needing to pick up a manual. #SOreadytohelp
Updated on June 13, 2022Comments
-
Brian almost 2 years
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'"); });
-
Brian over 11 yearsSo 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?
-
Firo over 11 yearscolumn name since it is "arbitrary sql"
-
Brian over 11 yearsok. 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?
-
Firo over 11 yearslike i said in my answer, all columns without prefixes are treated as columns of the parent table