sql – 跨多个表的NHibernate查询

我正在使用NHibernate,并试图找出如何编写查询,搜索我的实体的所有名称,
并列出结果.举个简单的例子,我有以下几个对象;

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”,OR宠物的名称包含“ted”.

这是我想要执行的sql的示例：

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

当我使用Criteria这样做时：

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

生成以下查询：

SELECT TOP 10 d.*,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？

谢谢你的帮助.

解决方法

试试这个,它可能会奏效.

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

sql – 跨多个表的NHibernate查询

解决方法

猜你在找的MsSQL相关文章