NHibernate JoinAlias ​​null test request not working

Question

I get some unexpected behavior using QueryOver JoinAlias ​​in NHibernate.

My essence is as follows:

public class Field { public virtual long Id { get; protected set; } public virtual Field Parent { get; protected set; } public virtual FieldType Type { get; protected set; } public virtual string Value { get; protected set; } ...(Ctors etc } 

My mapping is as follows:

 public class FieldMap : ClassMap<Field> { public FieldMap() { Id(x => x.Id) .GeneratedBy.Native(); References(x => x.Type) .Column("FieldTypeId") .LazyLoad() .Cascade.All() ; Map(x => x.Value); References(x => x.Parent) .Column("ParentFieldId") .Nullable() .LazyLoad() .Cascade.All() ; } 

My request:

  Field fieldAlias = null; string typeAlias = null; Field parentFieldAlias = null; var query = getSession().QueryOver<Field>(() => fieldAlias) .JoinAlias(() => fieldAlias.Type, () => typeAlias) .Where(() => typeAlias.Name == type) .JoinAlias(() => fieldAlias.Parent, () => parentFieldAlias) .Where(() => (parentFieldAlias.Value == parentValue) || (parentFieldAlias == null)) ; 

As far as I know, this should give me some SQL:

... WHERE (a.ParentFieldId == NULL) OR (a.ParentFieldId = c.FieldId AND c.Value = parentValue)

But I get an exception for null reference. (I assume when the alias is resolved and the parent is null).

Exclusion Details:

 System.NullReferenceException occurred HResult=-2147467261 Message=Object reference not set to an instance of an object. Source=NHibernate StackTrace: at NHibernate.Criterion.ConstantProjection..ctor(Object value) at NHibernate.Criterion.Projections.Constant(Object obj) at NHibernate.Impl.ExpressionProcessor.FindMemberProjection(Expression expression) at NHibernate.Impl.ExpressionProcessor.ProcessSimpleExpression(Expression left, Expression right, ExpressionType nodeType) at NHibernate.Impl.ExpressionProcessor.ProcessSimpleExpression(BinaryExpression be) at NHibernate.Impl.ExpressionProcessor.ProcessBinaryExpression(BinaryExpression expression) at NHibernate.Impl.ExpressionProcessor.ProcessExpression(Expression expression) at NHibernate.Impl.ExpressionProcessor.ProcessOrExpression(BinaryExpression expression) at NHibernate.Impl.ExpressionProcessor.ProcessBinaryExpression(BinaryExpression expression) at NHibernate.Impl.ExpressionProcessor.ProcessExpression(Expression expression) at NHibernate.Impl.ExpressionProcessor.ProcessLambdaExpression(LambdaExpression expression) at NHibernate.Impl.ExpressionProcessor.ProcessExpression(Expression`1 expression) at NHibernate.Criterion.QueryOver`2.Add(Expression`1 expression) at NHibernate.Criterion.QueryOver`2.Where(Expression`1 expression) at NHibernate.Criterion.QueryOver`2.NHibernate.IQueryOver<TRoot,TSubType>.Where(Expression`1 expression) at Ismoos.Director.FieldOptionsQuery.Execute(Service service, String type, String parentValue) in D:\Work\Ismoos\Ismoos\Director\Ismoos.Director\FieldOptionsQuery.cs:line 31 InnerException: 

I tried several different ways, including:

  .JoinAlias(() => fieldAlias.Parent, () => parentFieldAlias) .Where(Restrictions.Or(Restrictions.On(() => fieldAlias.Parent).IsNotNull, Restrictions.On(() => parentFieldAlias.Value).IsLike(parentValue)))) 

but none of these features work.

I have a workaround, leaving a restriction on the parent value of the field in the query and executing the LINQ query after returning QueryOver, for example:

  Field fieldAlias = null; string typeAlias = null; Field parentFieldAlias = null; var query = getSession().QueryOver<Field>(() => fieldAlias) .JoinAlias(() => fieldAlias.Type, () => typeAlias) .Where(() => typeAlias.Name == type) ; var list = query .List<Field>() ; return list .Where(x => (x.Parent == null) || (x.Parent.Value == parentValue)) .ToList(); 

but this is not as optimal as in QueryOver.

Any suggestions?

Answer 1

The second constrained solution will do the job. There are two questions. We need a LEFT JOIN for the parent, and I see a typo: IsNotNull must be IsNull to correctly evaluate the OR operator:

Broken IsNotNull solution (and most likely an internal join)

 .JoinAlias(() => fieldAlias.Parent, () => parentFieldAlias) .Where ( Restrictions.Or( Restrictions.On(() => fieldAlias.Parent).IsNotNull, // here Restrictions.On(() => parentFieldAlias.Value).IsLike(parentValue))) ) 

OR work with IsNull and LEFT JOIN:

 .JoinAlias(() => fieldAlias.Parent, () => parentFieldAlias , NHibernate.SqlCommand.JoinType.LeftOuterJoin)) // left join for NULL .Where ( Restrictions.Or( Restrictions.On(() => fieldAlias.Parent).IsNull, // this is what we need Restrictions.On(() => parentFieldAlias.Value).IsLike(parentValue)) ); 

The problem with the first solution is that we cannot evaluate the null value for the floating / virtual object: parentFieldAlias

.Where (() => (parentFieldAlias.Value == parentValue) || (parentFieldAlias ​​== null));

We need to check the owner property: fieldAlias.Parent

Answer 2

I had a similar problem and extra brackets between conditions

like

 .Where(() => (parentFieldAlias.Value == parentValue) || (parentFieldAlias == null)); 

earn everything for me.