Inner join between same tables in Nhibernate query API

I have two Domain Objects mapped right : Emails and ActionPlans

the table structure is something like:

create table emails(id int identity(1,1), subject nvarchar(512), parentEmailId int, rootEmailId int)

create table actionPlans(id int identity(1,1), dueDate datetime, emailId int)

insert into emails(subject, parentEmailId, rootEmailId)
values('First email', null,1)

insert into emails(subject, parentEmailId, rootEmailId)
values('RE: FirstEmail', 1, 1)

insert into emails(subject, parentEmailId, rootEmailId)
values('RE: RE: FirstEmail', 2, 1)

insert into actionPlans(dueDate, emailId)
values('2011-01-01', 2)

parentEmailId is a hierarchy and rootEmailId is always the first message (initiation email). At each level of hierarchy I can create an ActionPlan (only one per all hierarchy)

Now I'm getting the actionPlan using rootEmailId

SomeThing Like:

 return
            this.CreateCriteria(typeof(ActionPlan), "ap")
            .CreateAlias("Message", "m")
            .Add(Restrictions.Eq("m.RootMessage.Id", rootMessageId))
            .UniqueResult<ActionPlan>();

it generate something like:

select a.* FROM actionPlans a
inner Join emails e on a.emailId = e.id
where e.rootEmailId = 1

Works fine: but this make a lot of headaches, as I need to get the email from db and get rootEmailid

Now I want to send emailId instead of rootEmailId So how to translate the following TSQL in Nhibernare query API?

select a.* FROM actionPlans a
inner join emails rootEmail on rootEmail.id = a.emailId
inner join emails e on e.rootEmailId = rootEmail.rootEmailId
where e.id = 2

Now I can send 2, 1 or 3 and will obtain same ActionPlan

Thanks

ansaurus

tags:

views:

answers:

Inner join between same tables in Nhibernate query API

related questions