How can I write an HQL to fetch this?

Question

I have a class called Continent and Country.

Continent class has Country collection:

    private ISet<Country> _countries;
    public virtual ISet<Country> Countries
    {
        get { return _countries; }
        set { _countries = value; }
    }

I want to write an HQL query that will get all the continents that have countries at least one country with CountryName = "A"

My country class has property:

    public virtual string CountryName { get; set; }

And I have my .hbm.xml files have the Db relation information for both the Continent and Country objects.

How should I write my HQL here:

public IList<Continent> GetContinentsWithCountriesStartingWithLetter(char letter)
        {

            string query = ":letter"; //The query to be used
            return
                _session
                    .CreateQuery(query)
                    .SetString("letter", letter.ToString())
                    .List<Continent>();
        }

Thanks!

Answer 1

return _session
    .CreateQuery("from continent in Continent inner join continent.Countries country where country.Name like :letter")
    .SetString("letter", letter)
    .List<Continent>();

HTH,
Kent