nhibernate:基于列获取不同的结果,但检索所有列

尽管nhibernate没有办法从子查询的结果列中排除glcode,但是仍然可以创建一个执行此任务的查询。使用相关的exists子查询而不是in。我们要拍摄的SQL如下:

select query.*
from GL query
where exists (
    select
        min(subquery.GLId) AS GLId,
        subquery.GLCode
    from GL subquery
    group by subquery.GLCode
    having min(subquery.GLId) = query.GLId);

下面是nhibernate查询:

var min = Projections.Min("GLId");

var subquery = DetachedCriteria.For<GL>("subquery")
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("GLCode"), "GLCode")
        .Add(min, "GLId"))
    .Add(Restrictions.EqProperty(min, "query.GLId"));

return session.CreateCriteria<GL>("query")
    .Add(Subqueries.Exists(subquery))
    .List<GL>();