QueryOver的子查询 [英] Subqueries with QueryOver

问题描述

在将子查询与queryover一起使用时出现问题.

I have a issue in using subquery with queryover.

这就是我所拥有的

      var address = QueryOver.Of<Address>()
            .Where(x => x.City.IsLike("%" + city + "%")).Select(x => x.Person.Id);

        var result = Session.QueryOver<Person>()
            .Where(x => x.Type.IsLike(type + "%"))
            .And(x => x.Name.IsLike("%" + name + "%"))
            .WithSubquery.WhereExists(address);

我有一个用于Person的表，并且一个人有多个地址.

I have a table for Person and a person has multiple addreses.

所以 人 ID，名称，类型

So Person id, name, type

和地址将有 PersonId和城市等.

and Address will have PersonId and city etc.

因此要按名称和类型以及地址表中的城市搜索人

So want to search a person by name and type as well as City which is in Address table

推荐答案

尝试如下操作:

Address address = null;
Person person = null;
var addressSubQuery = QueryOver.Of<Address>(() => address)
    .Where(Restrictions.EqProperty(Projections.Property(() => address.Person.Id), Projections.Property(() => person.Id)))
    .Where(() => address.City.IsLike("%" + city + "%"));

    var result = Session.QueryOver<Person>(() => person)
        .Where(x => x.Type.IsLike(type + "%"))
        .And(x => x.Name.IsLike("%" + name + "%"))
        .WithSubquery.WhereExists(addressSubQuery);

您需要使用QueryOver的别名版本.这样，您可以引用其他查询中的Person元素，这些查询最终将链接到您的主查询中.

You need to use QueryOver's version of aliasing. This way you can reference the Person element from other queries which you will eventually link into your main query.

这与执行以下操作相同

Select * from Person
Where 
    Type like '%foo%'
    and Name like '%bar%'
    and exists ( select Id from Address 
                 where 
                      Address.PersonId = Person.Id
                      and Address.City like '%bar%' )

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