代码之家 › 专栏 › 技术社区 › Joseph

你能用NH3进行复杂的聚合操作吗?

nhibernate-criteria linq-to-nhibernate hql nhibernate

1

Joseph · 技术社区 · 15 年前

我需要执行一个类似于T-SQL中的分组聚合:

select b.Name as [Grouped Name],
COUNT(distinct a.ID) as [Grouped Count], 
COUNT(distinct c1.ID) as [Second Group Count], 
COUNT(distinct c2.ID) as [Third Group Count]
from TableA a
left join TableB b on a.ID = b.TableAID
left join TableC c1 on a.ID = c1.TableAID and c1.SomeCondition = 1
left join TableC c2 on a.ID = c2.TableAID and c2.SomeCondition = 2
group by b.Name
order by b.Name

我曾多次尝试构建一个对象模型,然后使用NH3映射它,然后构建一个将构建此SQL语法的查询,但收效甚微。

这是用NH3可以做到的吗?如果是的话,在林肯有办法做到吗?或者标准API?还是HQL?

到目前为止我试过的关键是:

//act
var query = from a in session.Query<TableA>()
    orderby a.TableB.Name
    select
        new
        {
            Grouped Name = a.TableB.Name,
            GroupedCount = a.Count(),
            SecondGroupCount = a.TableC.Count(c => c.SomeCondition == 1),
            ThirdGroupCount = a.TableC.Count(c => c.SomeCondition == 2),
        };

var results1 = query.ToList();

我也试过和一组人一起使用,但是不管我做什么,NH3总是抛出一个异常,如果我在超过聚合。如果只有一个聚合,则执行查询。如果我试着用两个集合来做,我会得到一个例外。

1 回复 | 直到 15 年前

1

Diego Mijelshon 15 年前

HQL可能是一条路要走。Linq太漏,标准太冗长。

select b.Name as [Grouped Name],
COUNT(distinct a.id) as [Grouped Count], 
COUNT(distinct c1.id) as [Second Group Count], 
COUNT(distinct c2.id) as [Third Group Count]
from TableA a
left join a.TableB b
left join a.TableC c1 with c1.SomeCondition = 1
left join a.TableC c2 with c2.SomeCondition = 2
group by b.Name
order by b.Name

这几乎与SQL相同,只是在连接中做了一些小调整。我不完全确定TableC的双重连接是否有效,但尝试一下也没什么坏处。