流畅的NHibernate:如何将这个查询写成一个标准?

The data structure is as follows: A house has many rooms. Each room has many persons.

数据结构如下:房子有很多房间。每个房间都有许多人。

What I want to do is to get all persons for a house. In plain SQL I would write the following:

我想做的是把所有的人都买一所房子。在简单的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?

我怎么能用流畅的NHibernate代码来写呢?

For this example, we can assume that the entities and mappings look like this:

对于这个例子，我们可以假设实体和映射是这样的:

House entity

房子的实体

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

House mapping

房子的映射

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

Room entity

空间实体

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 mapping

空间映射

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

Person entity

人实体

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

Person mapping

人的映射

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

1 个解决方案

#1

To get SQL query close to yours you can use these criterias:

要使SQL查询接近您的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>();

This produces something like this:

这就产生了这样的东西:

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

I tested it in FNH1.2 and NH3.1 but it should work well in NH2.1 as well.

我在FNH1.2和NH3.1中测试过，但在NH2.1中也应该有效。

EDIT: UpTheCreek is right. Linq is more clear than Criteria API. For example:

编辑:UpTheCreek是正确的。Linq比Criteria API更清晰。例如:

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

which produces different SQL query but result set is the same:

产生不同的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

#1