I have 2 tables, Table1 and Table2. They have a relastionship and that works fine. But I want to get an IList of all the records in Table1 that have a record in Table2?


T-SQL would be:
select Table1.* from Table1
where ID in (select distinct Table1ID from Table2)

Thanks for the replay, but would this give me a IList of all the ID's and not the Table1 objects?



Config files:

[Table1]
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly=".." namespace="..">
<class name="Person" table="Person">

<id name="ID" column="ID" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<property column="UserID" type="String" name="UserID" length="255" />
<property column="Associate_No" type="String" name="Associate_No" length="35" />
<property column="Name" type="String" name="Name" not-null="true" length="255" />
<property column="Password" type="String" name="Password" length="255" />
<property column="OldPassword" type="String" name="OldPassword" length="4000" />
<property column="BusSysLogon" type="String" name="BusSysLogon" length="50" />
<property column="Comments" type="String" name="Comments" length="1073741823" />
<property column="SouthUser" type="Boolean" name="SouthUser" not-null="true" />
<property column="UpdatedBy" type="String" name="UpdatedBy" not-null="true" length="255" />
<property column="UpdatedOn" type="DateTime" name="UpdatedOn" not-null="true" />

</class>
</hibernate-mapping>

[Table2 linkes on PersonID to Table1.ID]
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly=".." namespace="..">
<class name="Asset" table="Asset">

<id name="ID" column="ID" type="Int32" unsaved-value="0">
<generator class="native"/>
</id>
<many-to-one name="Personid" column="PersonID" class="Person" />
<property column="AssetNum" type="String" name="AssetNum" length="255" />
<property column="SerialNum" type="String" name="SerialNum" length="255" />
<property column="Price" type="Decimal" name="Price" />
<property column="Comments" type="String" name="Comments" length="1073741823" />
<property column="UpdatedBy" type="String" name="UpdatedBy" not-null="true" length="255" />
<property column="UpdatedOn" type="DateTime" name="UpdatedOn" not-null="true" />
<property column="PCName" type="String" name="PCName" length="50" />
<many-to-one name="Cpu" column="CPU" class="Assetcpu" />
<many-to-one name="Ram" column="Ram" class="AssetMem" />
<many-to-one name="HdSize" column="HDSize" class="AssethdSize" />
<many-to-one name="AssetList" column="AssetList" class="AssetList" />
<property column="WarExpDate" type="DateTime" name="WarExpDate" />

</class>
</hibernate-mapping>

I have also tried the following but can not get it to work.



NHibernate.ICriteria criteria = session.CreateCriteria(typeof(Person));
criteria = criteria.CreateCriteria("Asset", "a", NHibernate.SqlCommand.JoinType.InnerJoin);
lbPerson.DataSource = criteria.List();

the query i posted should return the instances, not just the ID's... i thought that was what you wanted based on the query you posted in your first post

Sorry, it does return the object not the ID. But the distinct does not work:

session.CreateQuery("select distinct t2.PersonID from Asset t2 order by t2.PersonID.Name").List()

System.Data.SqlClient.SqlException: The text, ntext, or image data type cannot be selected as DISTINCT

The Comments field is a sql TEXT field. Is there another way of doing this just using the 2 fields I need, ID and Name?

how about this:



btw, for the sake of readability, i'd rename the Personid many-to-one in Asset to Person. In that case, the query would be:

from Person p where p in (select distinct Person from Asset)

Thank you, that does work. But an ICriteria would fit the program better.

Is there any way to get something like this to work or convert IQuery to ICriteria?

NHibernate.ICriteria criteria = session.CreateCriteria(typeof(NHibernate.Ronco.Inventory3.Person), "p");
if(cbHasAsset.Checked)
{
criteria.Add(
NHibernate.Expression.Expression.In("p",
session.CreateQuery("select distinct a.PersonID from Asset a")
) );
}

if(tbUserID.Text != "")
{
criteria.Add(NHibernate.Expression.Expression.Like("UserID", tbUserID.Text + "%"));
}

criteria.AddOrder(NHibernate.Expression.Order.Asc("Name"));
lbPerson.DataSource = criteria.List();

I think you can add a projection to your criteria to make it distinct using something like



Cheers,

Symon.