Equivalent ICriteria construction of this JOIN query

Equivalent ICriteria construction of this JOIN query

I'm using NHibernate 3.2 and MySQL 5.5.
I would like to use ICriteria (or equivalent API) to construct a query, as
its easier to build queries using functions than it is with text when
parts of the query are constructed conditionally.
I've tried using CreateAlias but it creates a cartesian prodct, and
SetProjects(Projects.GroupProperty("U.Id")) gives the an exception that
the SQL is not available, whatever that might be. It should be fairly
simple but I am overwhelmed with all possibilities, like detached query?
query over? subquery? CreateAlias? I am lost and need a push into the
right direction.
I have two objects, Unit and UnitPart, all in one mapping like so
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="MastiLineProject"
namespace="MastiLineProject.Models">
<class name="Unit" table="Units" lazy="false">
<id name="Id">
<generator class="native"/>
</id>
<property name="CreationDate" unique="true" not-null="true"/>
<property name="OEMId" not-null="true" />
<property name="SerialNumber" unique="true" not-null="true"/>
<property name="QualityControlState" not-null="true"/>
<property name="Remarks" not-null="true"/>
<map name="Parts" table="UnitParts" lazy="false">
<key column="UnitId"/>
<index column="TypeOfPart" type="integer"/>
<composite-element class="UnitPart">
<property name="PartInfo"/>
<property name="Remarks"/>
</composite-element>
</map>
</class>
</hibernate-mapping>
Now I want the result of this query, constructed with ICriteria
SELECT
`Units`.*, `UnitParts`.`PartInfo`
FROM
`Units`
JOIN
`UnitParts` ON `Units`.`Id` = `UnitParts`.`UnitId`
WHERE
`UnitParts`.`PartInfo` LIKE '%0004A3FFFE69DB%'
GROUP BY `Units`.`Id`
This is the essential part of the code where the query must be constructed
using (ISession Session = ORM.NHibernateORM.SessionFactory.OpenSession())
{
using (ITransaction Transaction = Session.BeginTransaction())
{
ICriteria crit = Session.CreateCriteria<Unit>("U");
//Implement filter code here
if(Filter != default(FilterUnits))
{
//Add period constraint
if (Filter.From < Filter.To)
{
crit.Add(Restrictions.Between("CreationDate", Filter.From,
Filter.To));
}
List<ICriterion> QueryOrCriteria = new List<ICriterion>();
//Match the serial partially
if (Filter.Serial != null && Filter.Serial != String.Empty)
{
QueryOrCriteria.Add(Restrictions.Like("SerialNumber",
Filter.Serial, MatchMode.Anywhere));
}
if (Filter.MAC != null && Filter.MAC != String.Empty)
{
/* When the MAC address was still in the Unit table,
instead being in UnitParts
* QueryOrCriteria.Add(Restrictions.Like("MacAddress",
Filter.MAC, MatchMode.Anywhere));
*/
/* This is essentially what the query should be looking like
SELECT
`Units`.*, `UnitParts`.`PartInfo`
FROM
`Units`
JOIN
`UnitParts` ON `Units`.`Id` = `UnitParts`.`UnitId`
WHERE
`UnitParts`.`PartInfo` LIKE '%0004A3FFFE69DB%'
GROUP BY `Units`.`Id`
*/
QueryOrCriteria.Add(Restrictions.Like("part.PartInfo",
Filter.MAC, MatchMode.Anywhere));
}
if(QueryOrCriteria.Count > 0)
{
Disjunction disjunction = Restrictions.Disjunction();
foreach(ICriterion restriction in QueryOrCriteria)
{
disjunction.Add(restriction);
}
crit.Add(disjunction);
}
if (Filter.Remarks != null && Filter.Remarks != String.Empty)
{
String[] RemarkChunks = Filter.Remarks.Split('|');
if(RemarkChunks != null & RemarkChunks.Length > 0)
{
foreach(String SearchChunk in RemarkChunks)
{
crit.Add(Restrictions.Like("Remarks", SearchChunk,
MatchMode.Anywhere));
}
}
}
}