How to return joined objects as result of criteria?

Question

Hi, I'm wondering how I can represent the following in the criteria API

return DataContext.Session.CreateQuery("
    select
        ss.PracticeArea 
    from Subsection as ss
    where ss.Location = :Location
    ")
    .SetEntity("Location", location)
    .List<PracticeArea>();

The where clause is straight forward enough the bit I'm tripping over is how to get the joined object as the result ?

DataContext.Session.CreateCriteria<Subsection>()
    .Add(Restrictions.Eq("Location", location))
    .List<PracticeArea>();

This is my attempt which doesnt work since it returns the wrong type.

Answer 1

Try this:

DataContext.Session
    .CreateCriteria<Subsection>()
    .CreateCriteria("Subsecion", "ss")
    // the "select" clause is called "projection"
    .SetProjection(Projections.Property("ss.PracticeArea"))
    .Add(Restrictions.Eq("Location", location))
    .List<PracticeArea>();

Answer 2

I've got the following to work, I don't know if its going to perform better or worse than using a projection?

DetachedCriteria subsections = DetachedCriteria.For<Subsection>()
    .SetProjection(Projections.Property("PracticeArea.Id"))
    .Add(Restrictions.Eq("Location", location));

return DataContext.Session
    .CreateCriteria<PracticeArea>()
    .Add(Subqueries.PropertyIn("Id",subsections))
    .List<PracticeArea>();