Fluent NHibernate：如何将此查询编写为条件？

Question

数据结构如下： 一座房子有很多房间。每个房间都有很多人。

我想做的就是让所有人都买房子。在普通 SQL 中，我会编写以下内容：

SELECT * FROM Person WHERE Room_id
IN
(SELECT Id FROM Room WHERE House_id = 1)

How can I write that in Fluent NHibernate'ish code?

对于此示例，我们可以假设实体和映射如下所示：

House 实体

public virtual int Id { get; set; }
public virtual string Name { get; set; }
public virtual IEnumerable<Room> Rooms { get; set; }

House 映射

Id(x => x.Id);
Map(x => x.Name);
HasMany(x => x.Rooms);

Room 实体

public virtual int Id { get; set; }
public virtual string Name { get; set; }
public virtual House House { get; set; }
public virtual IEnumerable<Person> Persons { get; set; }

Room 映射

Id(x => x.Id);
Map(x => x.Name);
References(x => x.House);
HasMany(x => x.Persons);

Person 实体

public virtual int Id { get; set; }
public virtual string Name { get; set; }
public virtual Room Room { get; set; }

Person 映射

Id(x => x.Id);
Map(x => x.Name);
References(x => x.Room);

Answer 1

要获得接近您的 SQL 查询，您可以使用以下条件：

var subCriteria = DetachedCriteria.For<Room>(); // subquery
subCriteria.Add(Expression.Eq("House", house)); // where clause in subquery
subCriteria.SetProjection(Projections.Id()); // DetachedCriteria needs to have a projection, id of Room is projected here

var criteria = session.CreateCriteria<Person>();
criteria.Add(Subqueries.PropertyIn("Room", subCriteria)); // in operator to search in detached criteria
var result = criteria.List<Person>();

这会产生类似这样的结果：

SELECT this_.Id as Id4_0_, this_.Name as Name4_0_, this_.RoomId as RoomId4_0_
FROM [Person] this_
WHERE this_.RoomId in (SELECT this_0_.Id as y0_ FROM [Room] this_0_ WHERE this_0_.HouseId = @p0)',N'@p0 int',@p0=1

我在 FNH1.2 和 NH3.1 中测试了它，但它在 NH2.1 中也应该运行良好。

编辑：
UpTheCreek 是对的。 Linq 比 Criteria API 更清晰。例如：

var query = session.Query<Person>().Where(x => x.Room.House == house);
var linqResult = query.ToList<Person>();

产生不同的 SQL 查询，但结果集相同：

select person0_.Id as Id4_, person0_.Name as Name4_, person0_.Room_id as Room3_4_
from [Person] person0_, [Room] room1_
where person0_.Room_id=room1_.Id and room1_.House_id=2