使用集合获取分页实体 [英] Fetching Paginated Entity with Collection
问题描述
考虑两个具有one-to-many集合Vehicles
public class Person
{
public IList<Vehicle> Vehicles { get; set;}
}
public class Vehicle
{
public string Name { get; set;}
public Person Owner { get; set; }
}
我显示一个拥有车辆的人员网格，并在网格中显示第一个车辆的名称.网格是分页的.我使用以下条件来获取数据
I display a grid of Persons having vehicle and show the name of the first vehicle in the grid. The grid is paginated. I use the following criteria to fetch the data
我有一个为网格视图加载数据的条件，
I have a criteria for loading data for a grid view as
var criteria = DetachedCriteria.For<Person>()
.CreateAlias("Vehicles","vehicle", JoinType.InnerJoin)
.SetResultTransformer(new DistinctRootEntityResultTransformer())
.SetMaxResults(pageSize)
.SetFirstResult((page - 1) * pageSize)
criteria.Add(Restrictions.Eq("vehicle.Name", "super"));
其中page和pageSize是计算位.
问题在于，因为最大结果和第一个结果是在数据库中计算的，并且不同的根是在外部完成的，所以行数不匹配.
The problem is since max results and first results are calculated in the database and distinct root is done outside, the number of rows do not match.
有没有解决此问题的方法?
Is there a way to resolve this issue ?
推荐答案
这种查询应始终使用subquery而不是任何类型的 JOIN .这也意味着，colleciton项目引用了父(在我们的例子中是).
This kind of queries should always use subquery instead of any type of JOIN. That also means, that the colleciton item has reference to parent (as in our case).
因此，这里我们为Vehicle创建内部选择:
So, here we create the inner select for Vehicle:
var vehicles = DetachedCriteria.For<Vehicle>();
// add any amount or kind of WHERE parts
vehicles.Add(Restrictions.Eq("vehicle.Name", "super"))
// and essential SELECT Person ID
vehicles.SetProjection( Projections.Property("Owner.ID"));
现在，我们可以调整上述查询，使其仅在根/父级上起作用:
Now, we can adjust the above query, to work only on a root/parent level:
var criteria = DetachedCriteria.For<Person>()
// instead of this
// .CreateAlias("Vehicles","vehicle", JoinType.InnerJoin)
// we will use subquery
.Add(Subqueries.PropertyIn("ID", vehicles));
// Wrong to use this approach at all
//.SetResultTransformer(new DistinctRootEntityResultTransformer())
.SetMaxResults(pageSize)
.SetFirstResult((page - 1) * pageSize)
这将创建如下所示的SELECT:
That will create SELECT like this:
SELECT p....
FROM Person AS p
WHERE p.ID IN (
SELECT v.OwnerId
FROM Vehcile AS v
WHERE v.Name = 'super' ...
)
另请参阅:
- Query on HasMany reference
- In NHibernate, using a Disjunction gives double results
以及如何获取Vehicles 的集合(直到现在仅用于过滤)?最好的(如果不是唯一的话)方法是使用1 + 1 SELECT语句.简便的内置解决方案是batch-size设置.只需使用此设置(例如batch-size="25")标记Vehicles的集合，然后再使用少量SELECT语句就可以有效地加载所有数据.参见:
And how to fetch the collection of Vehicles (until now just used for filtering)? The best (if not only) way is to use 1+1 SELECT statements. The easy and built-in solution is batch-size setting. Just mark the collection of Vehicles with this setting (e.g. batch-size="25") and with few more SELECT statements all data will be effectively loaded. See:
- 19.1.5. Using batch fetching
- How to Eager Load Associations without duplication in NHibernate?
这篇关于使用集合获取分页实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
