本文介绍了NHibernate:根据列获取不同的结果,但检索所有列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含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:根据列获取不同的结果,但检索所有列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!