问题描述

我在使用带有 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 的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！