使用 Linq (nHibernate) 执行带有 Counts 的 Case 语句

发布于 2024-10-26 10:35:44 字数 366 浏览 1 评论 0原文

我确信这个房子已经做过几次了，但我从未找到解决方案...

那么是否可以使用 nHibernate 3 最好使用 Linq 来做这样的事情：

SELECT   
COUNT(CASE WHEN IsWithdrawn = 1 THEN 1 END) AS WithdrawnCount, 
COUNT(CASE WHEN IsWithdrawn = 0 THEN 1 END) AS ViewAllCount
FROM Tutorials

我很确定这不是'不可能，最好的解决方案是在这种情况下只选择 sql...但也许 nHibernate 3.1 中有一些新的东西可以做到这一点，甚至使用 queryover ？

谢谢

原文

I'm sure this is one has done the houses a few times but i've never found a solution...

So is it possible to do something like this using nHibernate 3 with preferably Linq:

SELECT   
COUNT(CASE WHEN IsWithdrawn = 1 THEN 1 END) AS WithdrawnCount, 
COUNT(CASE WHEN IsWithdrawn = 0 THEN 1 END) AS ViewAllCount
FROM Tutorials

I'm pretty sure that is isn't possible and that the best solution is to opt just for sql in this case... but maybe there is something new in nHibernate 3.1 that can do this with, even using queryover?

thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

兔小萌 2024-11-02 10:35:44

您可以使用 HQL 来完成此操作，这与 SQL 几乎相同：（

SELECT   
SUM(CASE WHEN IsWithdrawn = 1 THEN 1 ELSE 0 END) AS WithdrawnCount, 
SUM(CASE WHEN IsWithdrawn = 0 THEN 1 ELSE 0 END) AS ViewAllCount
FROM Tutorials

我不确定 COUNT 是否可以工作，我确信 SUM 可以）

这是一个也应该可以工作的 LINQ 版本：

session.Query<Tutorial>()
       .GroupBy(x => x.IsWithdrawn)
       .Select(x => new { x.Key, Count = x.Count() })

您可以使用 Projections .Conditional 使用 Criteria 或 QueryOver，但需要更多工作。

You can do it with HQL, which is almost the same as SQL:

SELECT   
SUM(CASE WHEN IsWithdrawn = 1 THEN 1 ELSE 0 END) AS WithdrawnCount, 
SUM(CASE WHEN IsWithdrawn = 0 THEN 1 ELSE 0 END) AS ViewAllCount
FROM Tutorials

(I'm not sure if the COUNT would work, I'm sure SUM does)

Here's a LINQ version that should work too:

session.Query<Tutorial>()
       .GroupBy(x => x.IsWithdrawn)
       .Select(x => new { x.Key, Count = x.Count() })

You can use Projections.Conditional with Criteria or QueryOver, but it's more work.

回复收藏 0 原文

酒儿 2024-11-02 10:35:44

您可以使用 QueryOver 获得所需的结果，尽管由于子查询而速度会较慢。

var sums = repo.Session.QueryOver<Tutorials>()
    .SelectList(list => list
        .SelectSubQuery<Tutorials>(NHibernate.Criterion.QueryOver.Of<Tutorials>()
            .Where(t => t.IsWithdrawn)
            .ToRowCountQuery())
        .SelectSubQuery<Tutorials>(NHibernate.Criterion.QueryOver.Of<Tutorials>()
            .ToRowCountQuery())
    )
    .Take(1) // we want only one row in our result. In SQL I would use " from dummy".
    .List<object[]>();

说明：

我使用两个独立的 QueryOver。第一个对教程中 IsWithdrawn = true 的行进行计数，第二个对所有行进行计数。然后，这两个分离的 QueryOver 用作带有投影 (SelectList) 的普通 QueryOver 中的子查询。

这是生成的 SQL：

SELECT TOP (1) 
(SELECT count(*) as y0_ FROM [Tutorials] this_0_ 
WHERE this_0_.IsWithdrawn = True) as y0_, 
(SELECT count(*) as y0_ FROM [Tutorials] this_0_) as y1_ 
FROM [Tutorials] this_;

You can get the desired result with QueryOver, although it will be slower due to the subqueries.

var sums = repo.Session.QueryOver<Tutorials>()
    .SelectList(list => list
        .SelectSubQuery<Tutorials>(NHibernate.Criterion.QueryOver.Of<Tutorials>()
            .Where(t => t.IsWithdrawn)
            .ToRowCountQuery())
        .SelectSubQuery<Tutorials>(NHibernate.Criterion.QueryOver.Of<Tutorials>()
            .ToRowCountQuery())
    )
    .Take(1) // we want only one row in our result. In SQL I would use " from dummy".
    .List<object[]>();

Explanation:

I use two detached QueryOvers. The first one counts the rows in Tutorials where IsWithdrawn = true, the second once counts all rows. The two detached QueryOvers are then used as SubQueries in a normal QueryOver with a Projection (SelectList).

Here is the generated SQL:

SELECT TOP (1) 
(SELECT count(*) as y0_ FROM [Tutorials] this_0_ 
WHERE this_0_.IsWithdrawn = True) as y0_, 
(SELECT count(*) as y0_ FROM [Tutorials] this_0_) as y1_ 
FROM [Tutorials] this_;

回复收藏 0 原文

~没有更多了~