Using complex where clause in NHibernate mapping layer
- by JLevett
I've used where clauses previously in the mapping layer to prevent certain records from ever getting into my application at the lowest level possible. (Mainly to prevent having to re-write lots of lines of code to filter out the unwanted records)
These have been simple, one column queries, like so
this.Where("Invisible = 0");
However a scenario has appeared which requires the use of an exists sql query.
exists (select ep_.Id from [Warehouse].[dbo].EventPart ep_ where Id = ep_.EventId and ep_.DataType = 4
In the above case I would usually reference the parent table Event with a short name, i.e. event_.Id however as Nhibernate generates these short names dynamically it's impossible to know what it's going to be.
So instead I tried using just Id, from above ep_ where Id = ep_.EventId
When the code is run, because of the dynamic short names the EventPart table short name ep_ is has another short name prefixed to it, event0_.ep_ where event0_ refers to the parent table.
This causes an SQL error because of the . in between event0_ and ep_
So in my EventMap I have the following
this.Where("(exists (select ep_.Id from [isnapshot.Warehouse].[dbo].EventPart ep_ where Id = ep_.EventId and ep_.DataType = 4)");
but when it's generated it creates this
select cast(count(*) as INT) as col_0_0_
from [isnapshot.Warehouse].[dbo].Event event0_
where (exists (select ep_.Id from [isnapshot.Warehouse].[dbo].EventPart event0_.ep_ where event0_.Id = ep_.EventId and ep_.DataType = 4)
It has correctly added the event0_ to the Id
Was the mapping layer where clause built to handle this and if so where am I going wrong?
