使用 QueryOver 进行子查询

发布于 2024-11-15 06:59:44 字数 545 浏览 2 评论 0原文

我在使用子查询和 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);

，我有一个人的表，一个人有多个地址。

所以人 id、名称、类型

和地址将有 PersonId 和城市等。

所以想按姓名和类型以及地址表中的城市搜索人员

原文

I have a issue in using subquery with queryover.

This is what I have

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

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

So
Person
id, name, type

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清君侧 2024-11-22 06:59:44

尝试这样的操作：

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 元素，这些查询最终将链接到您的主查询中。

这与执行以下操作相同

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

Try something like this:

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

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.

This is the same as doing something like the following

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

回复收藏 0 原文

~没有更多了~