I'm using Castle ActiveRecord which uses NHibernate underneath and I've added a property with a formula as follows to one of my entities:
[Property(Formula = "CAST((select count(*) from [User] as u where u.Email = FriendEmail) as bit)")]
public bool FriendRegistered { get; set; }
The problem is that now any query for this entity fails because NHibernate adds this_. before User in the formula. This results in the following SQL:
CAST((select count(*) from this_.[User] as u where u.Email = this_.FriendEmail) as bit) as formula0_7_
I tried using a different table name just for testing and that worked fine so I guess it only affects tables that share a reserved word, such as "User".
I've tried using backticks to escape it, also tried removing the space between "from" and "[User]", and tried adding the schema (dbo.) all without success.
This sounds like a bug in NHibernate and I found this bug report which is similar: https://nhibernate.jira.com/browse/NH-1617
I guess my question is: Is there a workaround to this or a setting somewhere or even a way to handle this that I don't know about?
We're using NH 2.1.2 and the MsSql2008Dialect Dialect.
Thanks in advance
Andy
As a workaround for this I ended up creating a View called AllUsers which just did a select * from [User] and then updated my formula to you AllUsers instead of [User].
A bit of a hack I know, but this might help others that did something working quickly.