CODEWITHSUNDEEP
nhibernategroup-by
Thomas
Thomas

Reputation: 67

nhibernate group by select column based on restrictions

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 ?

Upvotes: 2

Views: 549

Answers (1)

Firo
Firo

Reputation: 30813

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();

Upvotes: 2

Related Questions