跨多个表的NHibernate查询 [英] NHibernate Query across multiple tables

问题描述

我正在使用NHibernate，并试图弄清楚如何编写查询，该查询搜索我实体的所有名称， 并列出结果.作为一个简单的例子，我有以下对象；

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;}
}

最终，我想创建一个查询，例如，该查询返回所有名称包含"ted"的宠物主人，或返回名称包含"ted"的宠物.

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".

这是我要执行的SQL的示例:

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

将生成以下查询:

   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%'

如何调整查询，以便.CreateCriteria("Dog")和.CreateCriteria("Cat")生成OR(而不是AND)?

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

感谢您的帮助.

推荐答案

尝试一下，它可能会起作用.

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

这篇关于跨多个表的NHibernate查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！