在NHibernate的加入子查询（加入上多列） [英] Join sub query in NHibernate (join on multiple columns)

问题描述

如何表达查询

    Select * from
        (select name, Max(date) as date from items group by name) as latest
    inner join items as i
        on i.name=latest.name and i.date = latest.date

在NHibernate的QueryOver语法？

in NHibernate's QueryOver syntax?

因此，我希望得到最大的日期从每个名称记录。项目表

As a result I expect to get records with maximum dates for each name from items table.

在表中的每个项目对应 ItemEnity 类：

Each item in table correspond to the ItemEnity class:

[Class(Table = "items")]
public class ItemEnity
{
    [Id(Name = "Id")]
    [Generator(1, Class = "native")]
    public virtual long Id { get; set; }

    [Property(NotNull = true)]
    public virtual string Name { get; set; }

    [Property(NotNull = true)]
    public virtual DateTime Date { get; set; }

    // other columns ... 
}

我已经成功在一个强类型的形式来表达子查询：

I've managed to express subquery in a strongly typed form:

public class ItemLatest
{
    public string Name { get; set; }
    public DateTime Date { get; set; }
}

// ...

ItemLatest latest = null;

var subquery = QueryOver.Of<ItemEntity>().SelectList(list => list
    .SelectGroup(i => i.Name).WithAlias(() => latest.Name)
    .SelectMax(i => i.Date).WithAlias(() => latest.Date))
    .TransformUsing(Transformers.AliasToBean<ItemLatest>());

var result = subquery.GetExecutableQueryOver(session).List<ItemLatest>();

但我不知道怎么写，因为加入我无法找到如何使用 JoinQueryOver（）来表示（在我的情况ItemLatest和ItemEnity）两个实体之间的连接有没有关系属性从一个到另一个引用。

But I have no clue how to write join because I can't find out how to use JoinQueryOver() to express connection between two entities (ItemLatest and ItemEnity in my case) that has no relation property referencing from one to another.

推荐答案

连接是在ORM世界的（包括NHibernate的）的通过映射定义。所以，如果你想使用标准和加入，它必须在引用属性。

The JOIN is in the ORM world (including NHibernate) defined by mapping. So, if you would like to use criteria and JOIN, it must be over the Referenced property.

我们能为例子做的，就是如创建新对象 ItemEnityMaxDate 并映射它来查看类似于你的SELECT MAX .. GROUP BY。我们可以这样进行扩展，例如：

What we could for example do, is to create a new object e.g. ItemEnityMaxDate and map it to view similar to your SELECT MAX .. GROUP BY. We can extend it like this for example:

SELECT main.Id, main.Name, main.Date
  FROM items  main
    INNER JOIN 
    (
      SELECT i.Name, Max(i.Date) AS Date FROM items i GROUP BY i.Name 
    ) AS latest
    ON main.Name = latest.Name AND main.Date = latest.Date

C＃的 ItemEnityMaxDate 将如 ItemEntity （当然，我们将只需要身份证），映射到上述观点。
这时我们就可以把它作为一个子查询

The C# ItemEnityMaxDate would be similar as ItemEntity (well we will need only Id), mapped to the above view. Then we can use it as a subquery

// inner select
var subquery = DetachedCriteria.For<ItemEnityMaxDate>()
    .SetProjection(Projections.Property("Id"));

// the ItemEntity
var query = session.CreateCriteria<ItemEntity>()
    .Add(Subqueries.PropertyIn("Id", subquery));
var list = query.List<ItemEntity>();

这可能不是唯一的方法。不过这样一来，其表示为对象的特殊查询......将工作

This is probably not the only way. But this way, having your "special query" represented as an object ... would work

这篇关于在NHibernate的加入子查询（加入上多列）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！