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

Question

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();

score 2 · Accepted Answer · edited May 23 '17 at 12:29

2

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)]

edited May 23 '17 at 12:29

Community

1
1

answered Apr 09 '13 at 13:58

mickfold

2,003
1
14
20

I saw that answer, but I could't add the Join. I added my entire situation inside my question, and I don't know how do I adapt it to your answer. – Tiago B Apr 09 '13 at 14:23
@TiagoBrenck I've updated the answer to my question. Please let me know if this works for you. – mickfold Apr 09 '13 at 14:47
Ok, now the Restrictions.On(() => funcaoAlias.Name) is broke. How do my lambda will get the Funcao.Name property? This code shows me the error:'Delegate 'System.Func<...Funcao,object>' does not take 0 arguments' – Tiago B Apr 09 '13 at 15:23
Sorry I made an error in the `JoinAlias` statement. I've updated the answer. – mickfold Apr 09 '13 at 15:27
I did that, but the problem now is on Restrictions.On(() => funcao.Name), it returns Delegate 'System.Func<...Funcao,object>' does not take 0 arguments' at (() => funcao.Name) code. – Tiago B Apr 09 '13 at 16:38
@TiagoBrenck Wrote a test to double check and updated my answer with the results. – mickfold Apr 09 '13 at 19:16
It worked now! I was using the JoinAlias wrong, and the restriction lambda needs to access the property with the alias. – Tiago B Apr 09 '13 at 19:24

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

1 Answers1

UPDATE