nHibernate unable to cast Boolean to String

Question

I have the following query:

var query = from item in Session.Query<FactuurItem>()
    where item.EnergieType == etype
    && (item.DienstType == null || item.DienstType == DienstType.Onbekend || item.DienstType == dtype)
    && item.IsActive == true
    orderby item.Naam
    select item;

Which is converted to the following SQL:

select * from [FactuurItem] factuurite0_ 
where 
    factuurite0_.EnergieType=? 
    and (factuurite0_.DienstType is null or factuurite0_.DienstType=? or factuurite0_.DienstType=?) 
    and case when factuurite0_.IsActive=1 then 'true' else 'false' end=case when ?='true' then 'true' else 'false' end 
order by factuurite0_.Naam asc

Which results in the Exception:

{"Unable to cast object of type 'System.Boolean' to type 'System.String'."}

Now for my question: why??

The original query looks ok to me. The SQL, however, does not. Where do the two case-statements originate from? Apparently it tries to convert the property IsActive to a string in SQL, which it fails to do.

EDIT

Ok, found the solution. Nothing wrong with mapping etc., just with how the LINQ query is translated to SQL. In particular, how this line is translated:

&& item.IsActive == true

Somehow, this gets translated into the complex CASE-statement which ultimately results in the exception message. However, the == true-part isn't really necessary. By removing it, the translator no longer gets confused and provides the proper SQL:

factuurite0_.IsActive=1

No more CASE-statement and no more exception.

Please show us the mapping for FactuurItem. The mapping defines which SQL type each field will be mapped to. — escargot agile
– escargot agile, Commented May 26, 2011 at 12:50
Since you have found a solution, you can answer your own question by providing an answer in the box below. Subsequentially, you can accept it by clicking the tick icon (but not right away). — Sandor Drieënhuizen
– Sandor Drieënhuizen, Commented May 26, 2011 at 13:50

Pieter · Accepted Answer · 2011-05-27 09:02:11Z

2

Ok, found the solution. Nothing wrong with mapping etc., just with how the LINQ query is translated to SQL. In particular, how this line is translated:

&& item.IsActive == true

Somehow, this gets translated into the complex CASE-statement which ultimately results in the exception message. However, the == true-part isn't really necessary. By removing it, the translator no longer gets confused and provides the proper SQL:

factuurite0_.IsActive=1

No more CASE-statement and no more exception.

answered May 27, 2011 at 9:02

Pieter

516 bronze badges

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

Comments

Jeremy Harning · Accepted Answer · 2015-05-01 16:42:15Z

1

Using Log4Net at the debug level? In some version of Hibernate and Log4Net there is an incompatibility when turn on logging at the DEBUG level. All you get is this error about 'unable to execute sql cannot cast boolean to string'. Try turning up your logging level to INFO and the problem should go away.

answered May 1, 2015 at 16:42

Jeremy Harning

756 bronze badges

Collectives™ on Stack Overflow

nHibernate unable to cast Boolean to String

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related