NHibernate Query across multiple tables

Question

I am using NHibernate, and am trying to figure out how to write a query, that searchs all the names of my entities, and lists the results. As a simple example, I have the following objects;

public class Cat {
public string name {get; set;}
}

public class Dog {
    public string name {get; set;}
}

public class Owner {
    public string firstname {get; set;}
    public string lastname {get; set;}
}

Eventaully I want to create a query , say for example, which and returns all the pet owners with an name containing "ted", OR pets with a name containing "ted".

Here is an example of the SQL I want to execute:

SELECT TOP 10 d.*, c.*, o.* FROM owners AS o
INNER JOIN dogs AS d ON o.id = d.ownerId 
INNER JOIN cats AS c ON o.id = c.ownerId
WHERE o.lastname like '%ted%' 
OR o.firstname like '%ted%' 
OR c.name like '%ted%' 
OR d.name like '%ted%' 

When I do it using Criteria like this:

    var criteria = session.CreateCriteria<Owner>()
        .Add(
        Restrictions.Disjunction()
            .Add(Restrictions.Like("FirstName", keyword, MatchMode.Anywhere))
            .Add(Restrictions.Like("LastName", keyword, MatchMode.Anywhere))
        )
        .CreateCriteria("Dog").Add(Restrictions.Like("Name", keyword, MatchMode.Anywhere))
        .CreateCriteria("Cat").Add(Restrictions.Like("Name", keyword, MatchMode.Anywhere));
        return criteria.List<Owner>();

The following query is generated:

   SELECT TOP 10 d.*, c.*, o.* FROM owners AS o
   INNER JOIN dogs AS d ON o.id = d.ownerId 
   INNER JOIN cats AS c ON o.id = c.ownerId 
   WHERE o.lastname like '%ted%' 
   OR o.firstname like '%ted%' 
   AND d.name like '%ted%'
   AND c.name like '%ted%'

How can I adjust my query so that the .CreateCriteria("Dog") and .CreateCriteria("Cat") generate an OR instead of the AND?

thanks for your help.

Gilemette

Answer 1

Try this, it might work.

var criteria = session.CreateCriteria<Owner>()
            .CreateAlias("Dog", "d")
            .CreateAlias("Cat", "c")
            .Add(
            Restrictions.Disjunction()
                .Add(Restrictions.Like("FirstName", keyword, MatchMode.Anywhere))
                .Add(Restrictions.Like("LastName", keyword, MatchMode.Anywhere))
                .Add(Restrictions.Like("c.Name", keyword, MatchMode.Anywhere))
                .Add(Restrictions.Like("d.Name", keyword, MatchMode.Anywhere))
            );

Radian

Thanks, that seemed to do it. I found a post here: mattthr.blogspot.com/2010/02/… and was going to try that – Gilemette

Answer 2

You need to combine the two criteria using Expression.Or(criteria1, criteria2)

More here: http://devlicio.us/blogs/derik_whittaker/archive/2009/04/21/creating-a-nested-or-statement-with-nhibernate-using-the-criteria-convention.aspx

Hmm I think it would look like this (borrowed a bit from BuggyDigger's code)

var criteria = session.CreateCriteria<Owner>()
    .CreateAlias("Dog", "d")
    .CreateAlias("Cat", "c")
    .Add(Expression.Or(Expression.Like("c.Name", keyword, MatchMode.Anywhere)
            , Expression.Like("d.Name", keyword, MatchMode.Anywhere))
        );

But I didn't notice that you wanted to OR everything. In that case adding these criteria to the disjunction, as BuggyDigger showed, is probably the way to go.

Bleeder

Thanks fo rthe reply Alex, could you provide an example. I tried what you did, but I get "can not convert ICriteria to Icriterion" – Gilemette

Clarified in my post, but since I read the question wrong it may not matter ;) – Bleeder