NHibernate Like restriction on nested string properties

Question

For simplicity, let's guess there are two entities:

public class Entity
{
    public string Value { get; set; }

    public ChildEntity Child { get; set; }
}

public class ChildEntity
{
    public string Value { get; set; }
}

I need to find all entities where either Value or Child.Value are insensitive like specified string query.

That's what I have by now:

Entity entity = null;
ChildEntity child = null;

var nhibernateQuery = session
    .QueryOver(() => entity)
    .JoinAlias(() => entity.Child, () => child);

if (!string.IsNullOrWhiteSpace(query))
{
    nhibernateQuery = nhibernateQuery
        .Where(
            Restrictions.Or(
                Restrictions.On(() => entity).IsInsensitiveLike(query),
                Restrictions.On(() => child).IsInsensitiveLike(query)
            )
        );
}

return nhibernateQuery.List().ToArray();

I get the NullReferenceException - it seems like Restrictions.On does not handle alias correctly.

Another approach that I have tried is .JoinQueryOver() which is suggested by this post:

return session
   .QueryOver<Entity>()
       .Where(Restrictions.InsensitiveLike("Value", query))
   .JoinQueryOver(e => e.Child)
       .Where(Restrictions.InsensitiveLike("Value", query));

This thing works except for one thing: it returns all items where both Value and Child.Value are like query. I need the same thing, but with or logic.

What should be done to make it work? I would like to use .QueryOver(), either with or without aliases, but without .CreateCriteria(), but will appreciate if you help me with any working solution.

@WillyDavidJr Could you please explain what do you mean? `MatchMode` just affects how is string compared, isn't it? There is no more related code than in this minimal example. — Yeldar Kurmangaliyev, Aug 03 '17 at 04:59

score 0 · Accepted Answer · answered Aug 03 '17 at 09:10

The problem has been solved by using NHibernate LINQ .Query<>().
It can resolve all joins and relations by itself.
At the same time, .Contains() method is translated into case-insensitive LIKE statement for MS SQL which suits me needs.

var nhibernateQuery = session
    .Query<Entity>();

if (!string.IsNullOrWhiteSpace(query))
{
    nhibernateQuery = nhibernateQuery
        .Where(e => e.Value.Contains(query) || e.Child.Value.Contains(query));
}

return nhibernateQuery.ToArray();

NHibernate Like restriction on nested string properties

1 Answers1