nhibernate 根据限制选择列进行分组

发布于 2024-12-21 00:26:05 字数 573 浏览 2 评论 0原文

我有一个看起来相当简单的问题，但我现在无法弄清楚。

我有一个像这样的表：

id | type | value | comment | date
1      1      22     test     dec 2nd
2      1      23     foo      dec 4th
3      2      2      bar      dec 1st

根据模型，

class MyClass
  public virtual long Id { get; set;}
  public virtual long Type { get; set;}
  public virtual long Value { get; set;}
  public virtual string comment { get; set;}
  public virtual DateTime Date { get; set;}

我需要按类型分组并选择具有最新日期的行。（即，获取 ID 为 2 和 ID 3 的行）。

有人可以提供一个标准来解释如何做到这一点吗？

原文

I have what seems to be a rather simple problem, that I cannot figure out right now.

I have a table like so:

id | type | value | comment | date
1      1      22     test     dec 2nd
2      1      23     foo      dec 4th
3      2      2      bar      dec 1st

Based on the model

class MyClass
  public virtual long Id { get; set;}
  public virtual long Type { get; set;}
  public virtual long Value { get; set;}
  public virtual string comment { get; set;}
  public virtual DateTime Date { get; set;}

I need to group by type and select the row having the most recent date.
(That is, fetch rows with ID 2 and ID 3).

Can someone provide a Criteria with explanation of how to do this ?

分享到QQ

分享到微博

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

发布评论

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

苏辞 2024-12-28 00:26:05

两次往返，1 次获取所需行的类型/日期，1 次组合获取每一行。 FutureValue<> 会将循环中的每个查询合并为一个往返，并且选择将 FutureValues 转换为实际值。

class TypeDate
{
    public long Type { get; set; }
    public DateTime Date { get; set; }
}

var groups = session.CreateCriteria<MyClass>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("Type"), "Type")
        .Add(Projections.Max("Date"), "Date"))
    .SetResultTransForm(Transformers.AliasToBean<TypeDate>());
    .List<TypeDate>();

List<IFutureValue<MyClass>> futures = new List<IFutureValue<MyClass>>(groups.Count);
foreach (var group in groups)
{
    futures.Add(session.CreateCriteria<MyClass>()
        .Add(Restrictions.Eq("Type", group.Type))
        .Add(Restrictions.Eq("Date", group.Date))
        .FutureValue<MyClass>());
}

IEnumerable<MyClass> results = futures.Select(future => future.Value).ToList();

two roundtrips, 1 to get the type/date of the desired rows and 1 combined to get each row. FutureValue<> will combine each Query in the loop to one roundtrip and the select converts the FutureValues to the actual values.

class TypeDate
{
    public long Type { get; set; }
    public DateTime Date { get; set; }
}

var groups = session.CreateCriteria<MyClass>()
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.GroupProperty("Type"), "Type")
        .Add(Projections.Max("Date"), "Date"))
    .SetResultTransForm(Transformers.AliasToBean<TypeDate>());
    .List<TypeDate>();

List<IFutureValue<MyClass>> futures = new List<IFutureValue<MyClass>>(groups.Count);
foreach (var group in groups)
{
    futures.Add(session.CreateCriteria<MyClass>()
        .Add(Restrictions.Eq("Type", group.Type))
        .Add(Restrictions.Eq("Date", group.Date))
        .FutureValue<MyClass>());
}

IEnumerable<MyClass> results = futures.Select(future => future.Value).ToList();

回复收藏 0 原文

~没有更多了~