Business.Goal goalAlias = null;
var departments = DepartmentService
.CreateCriteria(typeof(Business.Department)) // need to specify the first criteria as Business.Department
.CreateCriteria<Business.Department>(d => d.Goals, () => goalAlias)
.Add<Business.Goal>( g => g.Company.Id == companyId )
.Add<Business.Goal>( g => g.Program.Id == programId )
.List<Business.Department>();
Look for "Create Criteria Association With Alias" in NHibernate Lambda Extensions (V1.0.0.0) - Documentation
EDIT:
You can actually write this more efficiently as:
// no alias necessary
var departments = DepartmentService
.CreateCriteria<Business.Department>()
.CreateCriteria<Business.Department>(d => d.Goals)
.Add<Business.Goal>( g => g.Company.Id == companyId )
.Add<Business.Goal>( g => g.Program.Id == programId )
.List<Business.Department>();
given these requirements there would be no difference, the generated SQL is the same:
for mappings:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Project" table="Project">
<id name="Id" type="Int32" unsaved-value="0">
<column name="Id" sql-type="int" not-null="true" unique="true"/>
<generator class="native" />
</id>
<many-to-one name="Job" column="FK_JobId" cascade="save-update" not-null="true" />
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Job" table="Job">
<id name="Id" type="Int32" unsaved-value="0">
<column name="Id" sql-type="int" not-null="true" unique="true"/>
<generator class="native" />
</id>
<property name="Name" type="String">
<column name="Name" sql-type="nvarchar" length="50" not-null="true"/>
</property>
</class>
</hibernate-mapping>
and classes
public class Project
{
public Project() { }
public virtual int Id { get; set; }
public virtual Job Job { get; set; }
}
public class Job
{
public Job() { }
public virtual int Id { get; set; }
public virtual String Name { get; set; }
}
these criteria definitions
ICriteria criteriacrit = session
.CreateCriteria(typeof (Project))
.CreateCriteria("Job", "job")
.Add(Restrictions.Eq("job.Name", "sometextA"));
ICriteria aliascrit = session
.CreateCriteria(typeof (Project))
.CreateAlias("Job", "job")
.Add(Restrictions.Eq("job.Name", "sometextB"));
generate the same SQL
SELECT
this_.Id as Id2_1_,
this_.FK_JobId as FK2_2_1_,
job1_.Id as Id1_0_,
job1_.Name as Name1_0_
FROM
Project this_
inner join Job job1_
on this_.FK_JobId=job1_.Id
WHERE job1_.Name = @p0; @p0 = 'sometextA'
SELECT
this_.Id as Id2_1_,
this_.FK_JobId as FK2_2_1_,
job1_.Id as Id1_0_,
job1_.Name as Name1_0_
FROM
Project this_
inner join Job job1_
on this_.FK_JobId=job1_.Id
WHERE job1_.Name = @p0; @p0 = 'sometextB'
note however that the CreateAlias
relies on the mappings to generate associations whereas the CreateCriteria
call allows to specify JoinType
.
so, these calls
ICriteria criteriacrit = session
.CreateCriteria(typeof(Project))
.CreateCriteria("Job",JoinType.LeftOuterJoin)
.Add(Restrictions.Eq("Name", "sometextA"));
ICriteria aliascrit = session
.CreateCriteria(typeof (Project))
.CreateAlias("Job", "job")
.Add(Restrictions.Eq("job.Name", "sometextB"));
generate these SQL statements
SELECT
this_.Id as Id2_1_,
this_.FK_JobId as FK2_2_1_,
job1_.Id as Id1_0_,
job1_.Name as Name1_0_
FROM
Project this_
**left outer** join Job job1_
on this_.FK_JobId=job1_.Id
WHERE job1_.Name = @p0; @p0 = 'sometextA'
SELECT
this_.Id as Id2_1_,
this_.FK_JobId as FK2_2_1_,
job1_.Id as Id1_0_,
job1_.Name as Name1_0_
FROM Project this_
**inner join** Job job1_
on this_.FK_JobId=job1_.Id
WHERE job1_.Name = @p0; @p0 = 'sometextB'
Best Answer
I believe that the order of your statements causes the query to generate the not desired SQL you are getting (hence no results). Here is how it should be:
Furthermore by your description it is not clear from which association you want to get the MainProfileImage and MediumThumbnail data from. As you are using it in your Criteria statement you are asking the data to come from the main Person object whose friends you are getting as you are using the
friendsAlias
. I have changed it to use thepersonAlias
instead as I believe this is where you mean to associate the data from.