NHibernate Query across multiple tables
- by Dai Bok
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.
