如何检查数据库中是否有一个人正好有两个(或多个)特定地址?(QueryOver)

Question

我想检查数据库中是否有一个人有两个特定的地址。

我的正确SQL查询：

SELECT name, id FROM person 
WHERE person.name = 'Kxxx' AND person.firstname = 'Axxx'
AND EXISTS (
SELECT address.id FROM address INNER JOIN city 
WHERE address.person_id = person.id AND city.name = 'Hxxx' AND address.h_number = '4')
AND EXISTS (
SELECT address.id FROM address INNER JOIN city 
WHERE address.person_id = person.id AND city.name = 'Dxxx' AND address.h_number = '5')
;

我正在尝试创建(C#和QueryOver(，但没有适当的效果：

Person personAlias = null;
Address addressAlias = null;
City cityAlias = null;
var query = session.QueryOver(() => personAlias)
.Where(() => personAlias.Name == name)
.Where(() => personAlias.FirstName == firstName)
;
foreach (var addr in addresses)
{
var subQuery = QueryOver.Of(() => addressAlias)
.Where(() => addressAlias.HouseNumber == addr.HouseNumber)
.JoinAlias(() => addressAlias.CityRef, () => cityAlias)
.Where(() => cityAlias.Name == addr.CityName)
;

subQuery.Select(x => x.Id);
query.WithSubquery.WhereExists(subQuery);
}

Answer 1

这可能是一个愚蠢的问题，但没有理由不能执行COUNT((吗