Using complex where clause in NHibernate mapping layer

Question

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?

Which NHibernate version is this?

Oskar Berggren
– Oskar Berggren

2012-09-03 22:13:48 +00:00
Commented Sep 3, 2012 at 22:13 — Oskar Berggren
– Oskar Berggren, Commented Sep 3, 2012 at 22:13
@Oskar Currently using 3.3.1.4000

JConstantine
– JConstantine

2012-09-04 07:41:26 +00:00
Commented Sep 4, 2012 at 7:41 — JConstantine
– JConstantine, Commented Sep 4, 2012 at 7:41

TWith2Sugars · Accepted Answer · 2012-09-06 07:50:49Z

1

+50

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

answered Sep 6, 2012 at 7:50

TWith2Sugars

3,4442 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JConstantine Over a year ago

Wow, that's surprisingly simple.

Oskar Berggren · Accepted Answer · 2012-09-03 22:14:44Z

0

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

answered Sep 3, 2012 at 22:14

Oskar Berggren

5,6561 gold badge21 silver badges37 bronze badges

1 Comment

JConstantine Over a year ago

Same problem unfortunately. It still adds the event0_ to the beginning.

Martin Ernst · Accepted Answer · 2012-09-05 17:49:46Z

0

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.

answered Sep 5, 2012 at 17:49

Martin Ernst

5,6992 gold badges20 silver badges14 bronze badges

Collectives™ on Stack Overflow

Using complex where clause in NHibernate mapping layer

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related