Hi,

I have Region and City entities like this,

namespace HelloNHibernate
{
[Serializable]
public class Region
{
public int id;
public string name;
public IList<City> cities { get; set; }
}

[Serializable]
public class City
{
public int cityid;
public string cityname;
}
}

My hbm.xml file look like,

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" >

<class name="HelloNHibernate.Region, HelloNHibernate" lazy="false">
<id name="id" access="field">
<generator class="native"/>
</id>
<property name="name" access="field" column="name"/>
<list name="cities" access="property" cascade="All" inverse="true" lazy="false">
<key>
<column name="parentid" not-null="true"/>
</key>
<index column="cityid"/>
<one-to-many class="HelloNHibernate.City, HelloNHibernate"/>
</list>
</class>


<class name="HelloNHibernate.City, HelloNHibernate" lazy="false">
<id name="cityid" access="field">
<generator class="native"/>
</id>
<property name="cityname" access="field" column="cityname"/>
</class>

</hibernate-mapping>

In region table I have following data,
Id Name
1 Tamil Nadu
2 Karnataka

In City Table I have following data,
ID Name ParentId
1 Chennai 1
2 Trichy 1
3 Devakottai 1
4 Madurai 1
5 Bangalore 2
6 Mysore 2

I want the city name starts with 'Ma%',

ICriteria regionCriteria = session.CreateCriteria(typeof(Region))
.CreateCriteria("cities").Add(Restrictions.Like("cityname", "%nn%"));
IList list = regionCriteria.List();

Expecting result : Region object contain Tamil Nadu and cities contain Madurai.
But I am getting Tamil Nadu with all cities in Tamil Nadu.

I tracked in the sql query profiler. First the crteria query is running,
exec sp_executesql N'SELECT this_.id as id1_1_, this_.name as name1_1_, city1_.cityid as cityid2_0_, city1_.cityname as cityname2_0_ FROM Region this_ inner join City city1_ on this_.id=city1_.parentid WHERE (city1_.cityname = @p0) and (this_.name like @p1)',N'@p0 nvarchar(4000),@p1 nvarchar(4000)',@p0=N'Madurai',@p1=N'Tamil%'
This will return the expected result.

But after this query again the folowing query is running and I am getting wrong result.
exec sp_executesql N'SELECT cities0_.parentid as parentid1_, cities0_.cityid as cityid1_, cities0_.cityid as cityid2_0_, cities0_.cityname as cityname2_0_ FROM City cities0_ WHERE cities0_.parentid=@p0',N'@p0 int',@p0=1


Please help me to get correct result.
Thanks in Advance.... :)