NHibernate 2.0.1.4000
SQL Server 2005
I am having some trouble getting the exact syntax to get the SQL that I need, I have tried searching but with not much luck. I guess I am doing something wrong but can't figure it out.
Here is the SQL that I would like to be generated:
Code:
SELECT *
FROM
ActivityWhereAbouts this_
left outer join Location location2_ on this_.Locationid=location2_.Id
inner join Person person3_ on this_.PersonId=person3_.Id
inner join ACTIVITYTYPE activityty4_ on this_.ActivityTypeId=activityty4_.Id
But using these mappings:
Code:
<many-to-one name="Location" column="Locationid" not-null="true" fetch="join"/>
<many-to-one name="Person" column="PersonId" not-null="true" fetch="join"/>
<many-to-one name="ActivityType" column="ActivityTypeId" not-null="true" fetch="join"/>
And this code:
Code:
public override IList<T> GetAllEntities<T>()
{
using (ISession session = OpenSession())
{
return session.CreateCriteria(typeof(T)).List<T>();
}
}
I get this generated SQL:
Code:
SELECT *
FROM
ActiveActivities this_
inner join Location location2_ on this_.Locationid=location2_.Id
inner join ActivePersons P on this_.PersonId=P.Id
inner join ActiveActivityTypes [AT] on this_.ActivityTypeId=[AT].Id
Which is as I would expect. In an effort to achieve my initial goal I have tried running this code:
Code:
public IList<T> GetAllEntities33<T>()
{
var criteria1 = DetachedCriteria.For<T>()
.CreateCriteria("Location", JoinType.LeftOuterJoin);
var criteria2 = DetachedCriteria.For<T>()
.CreateCriteria("Person", JoinType.InnerJoin);
var criteria3 = DetachedCriteria.For<T>()
.CreateCriteria("ActivityType", JoinType.InnerJoin);
var result = OpenSession().CreateMultiCriteria()
.Add(criteria1)
.Add(criteria2)
.List();
IList<T> list = (IList<T>)result;
return list;
}
Which has given me the following generated SQL:
Code:
SELECT *
FROM ActivityWhereAbouts this_
left outer join Location location1_ on this_.Locationid=location1_.Id
left outer join ActivityWhereAbouts activitywh4_ on location1_.Id=activitywh4_.Locationid
left outer join Person person5_ on activitywh4_.PersonId=person5_.Id
left outer join ACTIVITYTYPE activityty6_ on activitywh4_.ActivityTypeId=activityty6_.Id;
SELECT *
FROM ActivityWhereAbouts this_
inner join Location location3_ on this_.Locationid=location3_.Id
inner join Person person1_ on this_.PersonId=person1_.Id
left outer join ActivityWhereAbouts activitywh5_ on person1_.Id=activitywh5_.PersonId
left outer join ACTIVITYTYPE activityty6_ on activitywh5_.ActivityTypeId=activityty6_.Id;
So it looks like I am on the right path but not quite there yet. Can anyone shed some light on what I am doing wrong?
Thanks.
Login
FAQ
Search