QueryOver：从子查询中选择列 [英] QueryOver: select columns from subquery

问题描述

我如何选择/从一个不同的表子查询的项目值到我的主查询？

How can I select / project values from a subquery from a different table into my main query?

我有一个NH-模式是这样的：

I have an NH-model like this:

[Serializable]
public class MyModel
{
    public virtual int Id {get; set;}
    //more mapped values
    ....
    //unmapped values
    public virtual string ValueFromOtherTable {get;set;}
}

和我要填写ValueFromOtherTable与左连接是这样的：

And I want to fill ValueFromOtherTable with a left join like this:

Select mt.*, ..., ot.ValueFromOtherTable from MyModelTable mt left 
join OtherTable ot ON (somecondition)

在这里MyModelTable是表映射到为MyModel级。我想，以填补ValueFromOtherTable（无NH-映射）从MT选择所有值（以填补NH-映射列），然后用OtherTable我想，以填补ValueFromOtherTable。

where MyModelTable is the table mapped to MyModel-class. I want to fill ValueFromOtherTable (no NH-mapping) by selecting all values from mt (to fill the NH-mapped columns) and then by using OtherTable I want to fill ValueFromOtherTable.

我不能加入通过 QueryOver 这两个表的存在在模型中没有直接父子关系，所以 JoinAlias​​ 或 JoinQueryOver 不需额外的工作。我的 MainQueryOver 查询 MyModelTable 。

I can´t join both tables via QueryOver as there exists no direct parent-child correlation in the model, so JoinAlias or JoinQueryOver won´t work. My MainQueryOver queries MyModelTable.

选择：

另一种方法是首先从MyModelTable所有的值，然后使用属性那里查询OtherTable。然而，这将导致 SELECT N + 1 的问题（每个型号从为MyModel 选择一些OtherTable ...）也使得code非常复杂。

The alternative is to first get all values from MyModelTable and then using the properties there to query OtherTable. However this will result in an SELECT N+1 problem (for each model from MyModel select some OtherTable...) and also makes the code very complicated.

是否有解决这个问题的好办法或者是通过使用所描述的替代，以填补为MyModel的唯一方式 ？

Is there a good way to solve this problem or is the only way to fill MyModel by using described alternative ?

推荐答案

一种方法是使用投影，子查询和DTO。所以我们可以说，我们有DTO（几乎相同，为MyModel，但随着新的extern属性......如计数）。然后我们可以做到这一点是这样的：

One way would be to use Projections, Subquery and DTO. So let's say, that we have DTO (almost the same as MyModel, but with new extern property ... e.g. Count). Then we can do it like this:

MyModel main = null;
MyModelDTO dto = null;

// the main query
var query = session.QueryOver<MyModel>(() => main);

// the subquery used for projection
var subquery = QueryOver.Of<OtherModel>()
    // select something, e.g. count of the ID
    .SelectList(selectGroup => selectGroup.SelectCount(o => o.ID))
    // some condition
    // kind of JOIN inside of the subquery
    .Where(o => o.xxx == main.yyy); // just example

// now select the properties from main MyModel and one from the subquery
query.SelectList(sl => sl
      .SelectSubQuery(subquery)
         .WithAlias(() => dto.Count)
      .Select(() => main.ID)
        .WithAlias(() => dto .ID)
      ....
    );

// we have to use transformer
query.TransformUsing(Transformers.AliasToBean<MyModelDTO >())

// and we can get a list of DTO
var list = query.List<MyModelDTO>();

这篇关于QueryOver：从子查询中选择列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！