如何在 NHibernate 中编写以下 SQL 查询

发布于 2024-10-31 14:14:24 字数 391 浏览 4 评论 0原文

嘿 - 我正在努力弄清楚如何使用 NHibernate ICriteria （多条件？）来编写以下内容：（

这是一个查询，用于获取最后一天表中按受欢迎程度排序的名字列表）

select firstname,count(firstname) as occurances from registrants
where timestamp between DateAdd(day,-1, GetDate()) and getdate()
group by firstname
order by count(firstname) desc

另外，鉴于这只是表中的几列，不包括 ID，并且 NHibernate 需要 ID 作为其对象，那么“伪造”ID 以便我可以获得结果的最简单方法是什么？

原文

Hey - I'm battling to figure out how to write the following using NHibernate ICriteria (Multi criteria?) for the following:

(It's a query to get a list of first names ordered by popularity in the table in the last day)

select firstname,count(firstname) as occurances from registrants
where timestamp between DateAdd(day,-1, GetDate()) and getdate()
group by firstname
order by count(firstname) desc

Also, given this is just a couple of columns from a table, excluding the ID, and NHibernate needs ID's for it's objects, what's the easiest way to "fake" an ID so I can just get the results?

分享到QQ

分享到微博

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

发布评论

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

走野 2024-11-07 14:14:24

您需要使用投影和变压器来做到这一点。这是一些背景信息 http://nhibernate.info/doc/nh/en /index.html#querycriteria-projection

var criteria = Session.CreateCriteria<Registrant>()
   .Add(Restrictions.Between("Timestamp", DateTime.Now.AddDays(-1), DateTime.Now)
   .AddOrder(Order.Desc(Projections.Count("FirstName")))
   .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("FirstName"), "FirstName")
        .Add(Projections.Count("FirstName"), "Occurances")
   .SetResultTransformer(Transformers.AliasToBean<FirstNameOccurance>());

criteria.List<FirstNameOccurance>();

您需要创建一个名为 FirstNameOccurance 的类，该类具有 2 个名为 FirstName 和 Occurances 的属性。

You need to use projections and a transformer to do this. Here's some background info http://nhibernate.info/doc/nh/en/index.html#querycriteria-projection

var criteria = Session.CreateCriteria<Registrant>()
   .Add(Restrictions.Between("Timestamp", DateTime.Now.AddDays(-1), DateTime.Now)
   .AddOrder(Order.Desc(Projections.Count("FirstName")))
   .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("FirstName"), "FirstName")
        .Add(Projections.Count("FirstName"), "Occurances")
   .SetResultTransformer(Transformers.AliasToBean<FirstNameOccurance>());

criteria.List<FirstNameOccurance>();

You'll need to create a class called FirstNameOccurance that has 2 properties called FirstName and Occurances.

回复收藏 0 原文

~没有更多了~