How to use if condition for nullable boolean in linq for NHibernate
I have a class Person
whose property Sex
is NULL.
public class Person
{
public bool? Sex {get; set;}
}
And I have a linq query to NHibernate.
var q = SessionInstance.Query<Person>();
if (dto.Sex != null)
q = q.Where(p => p.Sex == dto.Sex);
return q.ToList();
Now if dto.Sex is equal true
, the result will be correct.
But if the dto.Sex value is equal false
, the result is wrong because the result contains people that Sex is false or null.
I checked out the profiler for this request:
select * from Person_Person person0_
where case
when person0_.Sex = 1 then 1
else 0
end = case
when 0 /* @p0 */ = 1 then 1
else 0
end
Why?
source to share
I don't know why this LINQ query generates a case statement, which is really weird ...
But I personally don't like the zero boolean approach.
The boolean value has 2 values: true or false. The moment you make it null, you give it the 3rd possible value.
In a scenario like this, I find it better to use an Enum.
I am assuming that "Sex" is for "men" / "women", in which case I think the best approach to the problem is to enumerate like:
public enum SexEnum
{
Unspecified = 0,
Male = 1,
Female = 2
}
This makes your code clearer:
var males = session.Query<Person>().Where(x => x.Sex == SexEnum.Male);
source to share
This is a bug in linux vendor nhibernate and the workaround for it would be as follows:
if (dto.Sex != null)
q = q.Where(p => p.Sex == dto.Sex && p.Sex != null);
The trick is to filter out nullable values from a set that should contain false values or true values (true values have no problem, so this is mostly for false values)
source to share