This is from a post I made on
StackOverflow<http://stackoverflow.com/questions/12200531/using-complex-where-clause-in-nhibernate-mapping-layer>,
it's probably best suited here.
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_.Idhowever 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?
---------------------
I'm also considering that the table alias is being prefixed with event0_
due to a bug in nHibernate, but this is only a hunch.
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/nhusers/-/bgx8DWRO-ZUJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
