CODEWITHSUNDEEP
nhibernatenhibernate-criteriaqueryover
Tiago B
Tiago B

Reputation: 2065

"OR" restriction in a JoinQueryOver using NHibernate.Criterion?

How do I code an OR restriction between two WhereRestrictionOn?

sessao.QueryOver<Usuario>(() => usuarioAlias)
                    .WhereRestrictionOn(usr => usr.Name).IsLike(search, MatchMode.Anywhere)
                    .Inner.JoinQueryOver(() => usuarioAlias.Funcao)
                    .WhereRestrictionOn(funcao => funcao.Name).IsLike("xpto", MatchMode.Anywhere)
                    //I want the OR here
                    .WhereRestrictionOn(funcao => funcao.Name).IsLike("abcd", MatchMode.Anywhere)
                    .List();

Upvotes: 1

Views: 2082

Answers (1)

mickfold
mickfold

Reputation: 2003

There are a few questions which already provide the answer to this question.

One way is to use the approach from the accepted answer to this question :

query.Where(Restrictions.On<Type>(x=> x.Foo).IsLike("xpto", MatchMode.Anywhere) ||
        Restrictions.On<Type>(x=> x.Foo).IsLike("abcd", MatchMode.Anywhere))

Another approach is to do the following:

query.RootCriteria.Add(Restrictions.Or(
  Restrictions.On<ObjectModel.Order>(x=> x.Foo).IsLike("xpto", MatchMode.Anywhere),
  Restrictions.On<ObjectModel.Order>(x=> x.Foo).IsLike("abcd", MatchMode.Anywhere)));

UPDATE

Instead of using Inner.JoinQueryOver try using JoinAlias instead:

Usuario usuarioAlias = null;
Funcao funcaoAlias = null;
var results = sessao.QueryOver<Usuario>(() => usuarioAlias)
      .JoinAlias(x => x.funcao, () => funcaoAlias)
      .WhereRestrictionOn(usr => usr.Name).IsLike(search, MatchMode.Anywhere)
      .Where(
          Restrictions.On<Funcao>(x => funcaoAlias.Name)
                                       .IsLike("xpto", MatchMode.Anywhere) ||
          Restrictions.On<Funcao>(x => funcaoAlias.Name)
                                       .IsLike("abcd", MatchMode.Anywhere))
       .List();

In my test project this produced the SQL statement:

SELECT this_.Id as Id0_1_, this_.Name as Name0_1_, funcaoalia1_.Id as Id1_0_, 
       funcaoalia1_.Name as Name1_0_, funcaoalia1_.usuario_id as usuario3_1_0_ 
FROM [Usuario] this_ inner join [Funcao] funcaoalia1_ on this_.Id=funcaoalia1_.Id 
WHERE this_.Name like @p0 
 and (funcaoalia1_.Name like @p1 or funcaoalia1_.Name like @p2);
@p0 = '%test%' [Type: String (4000)], 
@p1 = '%xpto%' [Type: String (4000)], 
@p2 = '%abcd%' [Type: String (4000)]

Upvotes: 2

Related Questions