NHibernate:根据列获取不同的结果，但检索所有列 [英] NHibernate: Get distinct results based on a column, but retrieve all columns

问题描述

我有一个包含GLCode的表GL.我需要获取唯一的GLCode列表，但需要获取所有其他列.以下SQL产生我想要的结果.

I have a table GL that contains GLCode. I need to get a list of unique GLCodes, but get all the other columns. The following SQL produces the results I want.

select * from GL where GLId in (select Min(GLId) from GL group by GLCode )

是否可以使用Criteria API做到这一点?

Is there a way to do this using the Criteria API?

这是我的最佳尝试:

        var subQuery = DetachedCriteria.For<GL>();
        subQuery
            .SetProjection(Projections.Property("GLCode"))                
            .SetResultTransformer(new DistinctRootEntityResultTransformer());

        return (List<GL>)currentSession
            .CreateCriteria(typeof(GL))
            .Add(Subqueries.PropertyIn("GLCode", subQuery))
            .List<GL>();   

推荐答案

即使NHibernate无法从子查询的结果列中排除GLCode，也仍然可以创建一个执行此任务的查询.使用相关的EXISTS子查询代替IN.我们正在拍摄的SQL是这样的:

Even though NHibernate doesn't have a way to exclude GLCode from the subquery's result columns, it is still possible to create a query that does the job. Use a correlated EXISTS subquery instead of IN. The SQL we're shooting for is like this:

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查询:

And here's the NHibernate query:

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>();

这篇关于NHibernate:根据列获取不同的结果，但检索所有列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！