Query on Self Join functionality

Hi,

     I have a situation to perform a self join opertation on a table and was not able to find a suitable method/LS query to accomplish the same. Can anyone guide me on this.

Here is the scenario

EMPLOYEE -->Table Name

EmployeePkId  --> Primary Key

SupervisorId --> ForeignKey referencing EmployeePkId  <which has a nullable relation>

--

Vasanth

This is represented as a normal one-to-many association.  In the designer, draw a OneToManyAssociation arrow from Employee to itself and set its Is Nullable to true and BackreferenceName to Supervisor.  Also, since your identity column isn't named Id, make sure that Employee's Identity Column Name is set to EmployeePkId.

You can then perform queries on the entity as normal.

Thanks for the response Ivan, I had set the similar settings in my model and the query i tried was something like this

QueryExpression query = Entity<Employee>.Attribute("SupervisorId") == Entity<Employee>.Attribute("Id");
return UnitOfWorkScope.Current.Find<Employee>(query).Select();

In this the query it generates is something like

select * from employee where employee.SupervisorId = employee.EmployeePkId which wont work. I need a query like

select distinct A.<allattributeName> from employee as A join employee as B on (A.EmployeePkId = B.SupervisorId)

This is most easily done with LINQ:

from c in UnitOfWork.Employees
join c2 in UnitOfWork.Employees on c.SupervisorId equals c2.Id
select c;

You can do it with the core query API (see Query.Join) but LINQ is much easier!

Thanks for the guidance Ivan.