通过使用NHibernate的子查询性能只选择最高不节组 [英] selecting only max clause without group by properties in subquery using Nhibernate

问题描述

我有SQL查询是这样的：

在

  SELECT * FROM dbo.table1其中Id
（
    选择MAX（ID）从dbo.table1组ID进行为prop1，prop2，prop3
）
 

我要创建NHibernate的查询，这能为我做到这一点。我试图用 QueryOver ，但它不工作。你有什么建议，如何做到这一点？

解决方案

NHibernate的支持，即使这种查询。请参阅更多的文档： 15.8。独立查询和子查询的。我们只需要分割查询的（在你的SQL代码段）的分为两部分：

• 内选择


• 选择与IN子句

假设，即 dbo.table1 在Questin被映射到 myEntity所。
要建立内部的选择，让我们使用的DetachedCriteria

编辑（扩展与本集团通过 SqlGroupProjection ）

有是 SqlGroupProjection 法的摘录：

  

一个分组SQL投影，通过同时指定SELECT子句和组
  第片段

  //内选择
的DetachedCriteria innerSelect =的DetachedCriteria
    。对于（typeof运算（myEntity所））
    .SetProjection（
        Projections.ProjectionList（）
         。加（
            Projections.SqlGroupProjection（
              MAX（ID），//只能选择... MAX（ID）
              为prop1，Prop2，Prop3，// GROUP BY ... property1，P2 ...
              新的String [] {ID} //可能是空的，而不是用于
              新ITYPE [] {NHibernate.NHibernateUtil.Int32} //改造
            ）
         ）
    ;
 

请注意：我甚至提供的最后两个paramters，但在这种情况下，他们可能是空的：新的String []，新ITYPE [] {} 。这些仅用于转换（物化从数据转换成实体）。这是不是这样，我们只是建立内部的选择...

  //选择与IN子句
VAR的结果= session.CreateCriteria（typeof运算（myEntity所））
    。新增（Subqueries.PropertyIn（ID，innerSelect））
    .LIST＆LT;＆myEntity所GT;（）;
 

也关系可能是 15.7。预测，聚集和分组

I have SQL query like this:

select * from dbo.table1 where Id in
(
    select max(id) as id from dbo.table1 group by prop1, prop2, prop3
)

I want to create NHibernate query which is be able to do this for me. I tried to use QueryOver but it doesn't work. Do you have any suggestions how to do it?

解决方案

NHibernate supports even this kind of queries. Please, see more in documentation: 15.8. Detached queries and subqueries. We just have to split the query (as in your SQL snippet) into two parts:

• inner select
• the select with the IN clause

Let's assume, that the dbo.table1 in the Questin is mapped into MyEntity. To create inner select, let's use the DetachedCriteria

EDIT (extended with the Group by, SqlGroupProjection)

There is an extract of the SqlGroupProjection method:

A grouping SQL projection, specifying both select clause and group by clause fragments

// inner select
DetachedCriteria innerSelect = DetachedCriteria
    .For(typeof(MyEntity))
    .SetProjection(
        Projections.ProjectionList()
         .Add(
            Projections.SqlGroupProjection(
              " MAX(ID) ",               // SELECT ... max(ID) only
              " Prop1, Prop2, Prop3",    // GROUP BY ... property1, p2...
              new string[] {"ID"},       // could be empty, while not used for  
              new IType[] { NHibernate.NHibernateUtil.Int32 } // transformation
            )
         )
    ;

Note: I've provided even the last two paramters, but in this case they could be empty: new string[], new IType[] {}. These are used only for Transformation (materialization from data into entity). And this is not the case, we are just building inner select...

// the select with IN clause
var result = session.CreateCriteria(typeof(MyEntity))
    .Add(Subqueries.PropertyIn("ID", innerSelect))
    .List<MyEntity>();

Also related could be 15.7. Projections, aggregation and grouping

这篇关于通过使用NHibernate的子查询性能只选择最高不节组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！