NHibernate getting collection within another collection during query

Question

Suppose I have the following class structure:

public class State
{
    public int Id { get; set; }
    public DateTime StatehoodDate { get; set; }
    public IList<City> CityList { get; set; }
}

public class City
{
   public int Id { get; set; }
   public string CityName { get; set; }
}

Tied to the following tables in NHibernate:

CREATE TABLE tblState (
    StateId int,
    StatehoodDate datetime,
)

CREATE TABLE tblCity {
    CityId int,
    StateId int,
    CityName varchar(1000)
}

*Note: Assume for this example, there are only 5-10 cities per state. This is an obfuscated example. *

I want to get all the states with a StatehoodDate after 1900. I also want a list of the cities in that state loaded automatically, so no lazy loading. How do I query this in NHibernate in a single query? (HQL, Criteria, etc. does not matter - I'll use whatever works.)

I'm hoping there's some way to get all the data back in one query and then do an equivalent of "GROUP BY" to get a list of only the states with a StatehoodDate after 1900. In my example, I will always display the cities every time this query is ran.

Lazy loading is obviously one option, but there is a performance hit, as there would be the initial query (on date) and then one query

Answer 1

session.CreateCriteria<State>()
    .SetFetchMode("CityList", NHibernate.FetchMode.Join)
    .Add(Restrictions.Gt("StatehoodDate", new DateTime(1900,1,1)))
    .SetResultTransformer(new DistinctRootEntityResultTransformer())
    .List<State>();

This will get you all those states with city list pre-loaded.

If you don't want state entities and just want to display state & city data then you can simply project those values onto a dto object and display that...

class CityView
{
    public string StateName { get; set; }
    public string CityName { get; set; }
}

session.CreateCriteria<State>()
    .CreateAlias("CityList", "cl")
    .Add(Restrictions.Gt("StatehoodDate", new DateTime(1900,1,1)))
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Property("StateName"), "StateName")
        .Add(Projections.Property("cl.CityName"), "CityName"))
    .SetResultTransformer(Transformers.AliasToBean<CityView>())
    .List<CityView>();