NHibernate how to join to table twice in HQL

public class Case
{
    public int Id { get; set; }
    public string Number { get; set; }
   开发者_开发问答 public Employee Employee { get; set; }
}

public class Employee
{
    public int Id { get; set; }
    public string EmployerIdentifier { get; set; }
    public Case Case { get; set; }
}

There is a one to one releationship between Case and Employee. However there are multiple records in the DB that represent the same Employee. To find those records we look at the EmployerIdentifier. So if I have only the Employee.Id how can I write an NHibernate query that returns all the cases for that Employee. In SQL I would do it by joining to the employee table twice (see example below).

Declare @TargetEmployeeID bigint
set @TargetEmployeeID = 246834
select * from Cases C
inner join Employees E on E.EmployeeID = C.EmployeeID
inner join Employees EST on EST.EmployerIdentifier = E.EmployerIdentifier
where EST.EmployeeID = @TargetEmployeeID

How would I do this using HQL?

You could use the following logic.

Use an in memory query - IQueryable.

Your first query will be to identify the list of Employees that are common between Employee and EmployeeIdentifier entities. This will be an in memory query.

var caseList = new IList<Case>();

IQueryable<EmployeeIdentifier> empIdenList =  Persister.Session.Query<EmployeeIdentifier>().Where(ei => ei.EmployeeId = 246834);

caseList = Persister.Session.Query<Case>().Where(e => empIdenList.Contains(e.Employee)).ToList<Case>();

Note - We do the ToList() call only in the 2nd query and this is when the query actually gets generated. You could use NHIbernate Profiler to see the actual SQL that gets generated back.

Refer to NHibernate.Linq DLL to find more about Query().

This is actually very simple in HQL. Try this:

SELECT Case c INNER JOIN FETCH c.Employee WHERE c.Employee.EmployerIdentifier=:employerId

If I remember right, though, this will bypass lazy loading if that's something you're concerned with.