CODEWITHSUNDEEP
c#sqlnhibernatenhibernate-mapping
JConstantine
JConstantine

Reputation: 3931

Using complex where clause in NHibernate mapping layer

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?

Upvotes: 4

Views: 620

Answers (3)

TWith2Sugars
TWith2Sugars

Reputation: 3434

Try putting square brackets around the alias like this:

exists (select ep_.Id from [Warehouse].[dbo].EventPart [ep_] where Id = ep_.EventId and ep_.DataType = 4

Upvotes: 1

Martin Ernst
Martin Ernst

Reputation: 5679

The NHibernate.SqlCommand.Template class has a method (RenderWhereStringTemplate) that modifies the Where clause. Looking at the code, I think the problem comes in that it thinks that ep_ is an identifier (basically a property of the mapped class) so it prefixes it with the table's prefix. The code to determine whether it's an identifier or not is quite simple - it basically checks if it's quoted, or if it starts with a-z and doesn't contain .

I think the simplest solution is to change your alias from ep_ to _ep - I typically use 2 _'s to make sure it doesn't conflict with a prefix that NHibernate generates.

Upvotes: 0

Oskar Berggren
Oskar Berggren

Reputation: 5629

Try using the "as" keyword when defining the ep_ alias.

Upvotes: 0

Related Questions