NHibernate: how to select a sorted parent/child and retrieve only specific row_numbers

Question

I have 2 tables: Parent and Child which have the following relation: Parent has many Childs.

public class Parent
{
    public  DateTime Timestamp;
    public  IList<Child> Child;
}
public Child
{
    public string Name;
}

I want to select both Parent and Child, Sorted by Timestamp and get only rows between index x to y.

public IList<Parent> Get(DateTime from, DateTime to, int startRow, int count)
{
    QueryOver<Parent>().Where(row => row.Timestamp >= from)
    .And(row => row.Timestamp <= to).OrderBy(row => row.Timestamp).Asc.List();
}

I don't know how to get only the required rows.

Should I do it with QueryOver? or its better doing it in HQL?

Thanks

Are you saying you want to EAGER load children along with their parents? — Rippo
Yes and I also want to filter the result to retrieve only range of rows — Haimon
When you say "range of rows" do you mean all parents between rows x and y, i.e. something like select * from Parents where RowNum between :x and :y? — mickfold

Haimon Haimon · Accepted Answer · 2013-04-21T09:10:50

I changed the relation and instead of having Parent and Child I use only one table:

public class Info
{
    public  DateTime Timestamp;
    public string Name;
}

In order to get all records between dates, sorted and get them from index startRow to startRow + count I used the following:

public IList<Info> GetInfo (DateTime fromDate, DateTime toDate, int startRow, int count)
{
    IList<Info> result = 
    QueryOver<Info>()
    .Where(row => row.Timestamp >= fromDate)
    .And(row => row.Timestamp <= toDate)
    .OrderBy(row => row.Timestamp).Asc
    .Skip(startRow).Take(count).List();
    return result;
}

The resulted SQL is:

SELECT * FROM Info WHERE timestamp >= :fromDate AND timestamp <= :toDate 
ORDER BY timestamp ASC OFFSET :startRow ROWS FETCH NEXT :count ROWS ONLY

NHibernate: how to select a sorted parent/child and retrieve only specific row_numbers

1 Answers