NHibernate 中的 SELECT MAX() 和 JOIN

发布于 2024-09-04 06:12:15 字数 1258 浏览 11 评论 0原文

我有一个供应商。每个供应商都有多个预订，上面有一个预订日期。

我想要一份今天尚未进行预订的供应商列表。

在 SQL 中我会做这样的事情：

SELECT v.Id, MAX(r.ReservationDate) AS MaxDate FROM Vendor v 
INNER JOIN DailyReservation r ON v.Id = r.Vendor_Id
GROUP BY v.Id
HAVING MAX(r.ReservationDate) <> '2010-06-04'

我正在尝试在 NHibernate 中这样做：

session.CreateCriteria<Vendor>()
                    .CreateAlias("Reservations", "r")
                    .SetProjection(Projections.Alias(Projections.Max("r.ReservationDate"), "MaxDate"))
                    .Add(Restrictions.Not(Restrictions.Eq("MaxDate", DateTime.Today)))
                    .List<Vendor>();

这显然不起作用。我做错了什么？

编辑！我又玩了一些，到了这一点，效果更好：

var c = Session.CreateCriteria<Vendor>();
c.CreateAlias("Reservations", "r");

ProjectionList projections = Projections.ProjectionList();
projections.Add(Projections.Max("r.ReservationDate"), "MaxDate");
projections.Add(Projections.GroupProperty("Id"));
c.SetProjection(projections);
c.Add(Restrictions.Not(Restrictions.Eq("MaxDate", DateTime.Today)));

return c.List<Vendor>();

为了回答评论，我收到错误“NHibernate.QueryException：无法解析属性：MaxDate of ：小贩”

原文

I have a Vendor. Every Vendor has several Reservations, with a ReservationDate on it.

I want a list of Vendors that have not made a reservation yet today.

In SQL I would do something like this:

SELECT v.Id, MAX(r.ReservationDate) AS MaxDate FROM Vendor v 
INNER JOIN DailyReservation r ON v.Id = r.Vendor_Id
GROUP BY v.Id
HAVING MAX(r.ReservationDate) <> '2010-06-04'

I'm trying do it it in NHibernate like this:

session.CreateCriteria<Vendor>()
                    .CreateAlias("Reservations", "r")
                    .SetProjection(Projections.Alias(Projections.Max("r.ReservationDate"), "MaxDate"))
                    .Add(Restrictions.Not(Restrictions.Eq("MaxDate", DateTime.Today)))
                    .List<Vendor>();

This clearly isn't working. What am I doing wrong?

EDIT! I played around some more, and got to this point, which is working better:

var c = Session.CreateCriteria<Vendor>();
c.CreateAlias("Reservations", "r");

ProjectionList projections = Projections.ProjectionList();
projections.Add(Projections.Max("r.ReservationDate"), "MaxDate");
projections.Add(Projections.GroupProperty("Id"));
c.SetProjection(projections);
c.Add(Restrictions.Not(Restrictions.Eq("MaxDate", DateTime.Today)));

return c.List<Vendor>();

And to answer the comment, I'm getting the error "NHibernate.QueryException: could not resolve property: MaxDate of: Vendor"

分享到QQ

分享到微博

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

发布评论

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

挽心 2024-09-11 06:12:15

正如 Mauricio 提到的，HQL 更适合解决这个问题。

这是您的查询：

session.CreateQuery(
  @"
  SELECT v.Id, MAX(r.ReservationDate)
  FROM Vendor v
  JOIN v.Reservations r
  GROUP BY v.Id
  HAVING MAX(r.ReservationDate) <> :MaxDate
  ")
  .SetParameter("MaxDate", DateTime.Today)
  .List();

如您所见，它与 SQL 没有太大区别，除了 NH 已经知道关系这一事实，因此您不必为 Join 指定字段。

不过，需要考虑的一件事是此查询不会返回 Vendor 列表。它返回一个 object[] 列表，其中每行包含两个元素：Id 和 MAX 投影。

As Mauricio mentioned, HQL is a better fit for this problem.

Here's your query:

session.CreateQuery(
  @"
  SELECT v.Id, MAX(r.ReservationDate)
  FROM Vendor v
  JOIN v.Reservations r
  GROUP BY v.Id
  HAVING MAX(r.ReservationDate) <> :MaxDate
  ")
  .SetParameter("MaxDate", DateTime.Today)
  .List();

As you see, it's not much different from the SQL, except for the fact that NH already knows about the relationships, so you don't have to specify the field for the Join.

One thing to consider, though, is that this query does not return a List of Vendor. It returns a List of object[], where each row contains two elements: the Id and the MAX projection.

回复收藏 0 原文

~没有更多了~