Problem with mapping int column to a bool property in nhibernate

Question

I've got a int column mapped as Boolean type in domain objects. Everything was looking good until today where we noticed when doing a QueryOver on the bool property the actual SQL generated is not to the liking of SqlServer.

The queryOver looks like below:

.Where(Restrictions.On<OrderLine>(ol => _orderLineAlias.Approved).IsLike(true));

And the SQL for that is,

select * from Orderline where Approved like 'true'

Hope you can see the problem here, as the column's data type is int, this wont return me anything. Changing the DB data type or that of the Entity class is not an option.

time to go back and change your db model.. why would you keep them different — Baz1nga
– Baz1nga, Commented Aug 10, 2011 at 9:56
heh..good idea..but not going to work as there is a lot of legacy logic dependent on this . — Illuminati
– Illuminati, Commented Aug 10, 2011 at 10:12
@Stefan, i was modifying this code someone else has written and figured it was done all wrong. Thanks for pointing that out :) — Illuminati
– Illuminati, Commented Aug 10, 2011 at 13:54

Thilak Nathen · Accepted Answer · 2011-08-10 12:45:47Z

3

Have you tried:

    QueryOver<OrderLine>().Where(x => x.Approved == true)

?

answered Aug 10, 2011 at 12:45

Thilak Nathen

1,3431 gold badge8 silver badges13 bronze badges

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

Comments

Vijay Gill · Accepted Answer · 2011-08-10 09:50:19Z

0

Did you try something on the lines of the following in the configuration file?

<property name="hibernate.query.substitutions">true 1, false 0</property>

Link to NH documentation on this topic

answered Aug 10, 2011 at 9:50

Vijay Gill

1,5181 gold badge14 silver badges16 bronze badges

4 Comments

Illuminati Over a year ago

thanks, but it does not make any difference. have or not the same sql syntax is generated. Probably that only works with LinqToHibernate. I'm using QueryOver in this instance.

Vijay Gill Over a year ago

I have been using it with HQL (call me dinosaur :) ) and it works fine. As another suggestion I would create another property which just acts as a convertor for bool<->int where int property/field is mapped to the underlying column whereas the bool property is used by UI/anything else.

Vijay Gill Over a year ago

Also found something that matches your problem stackoverflow.com/questions/5063541/…

Illuminati Over a year ago

Thanks Vijay..i tried adding a different property as well but same result. The issue is, the expression tree is built before the property reaches its' internals. So if we refer the Bool value in the QueryOver the sql is still going to be the same. As a temporary solution i'm gonna have two properties (int , bool) mapped to the same column and use Int one inside the QueryOver. I believe this has not been reported as a issue with QueryOver in jira. I wil try to do that.

Collectives™ on Stack Overflow

Problem with mapping int column to a bool property in nhibernate

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related