Nikhil Ghuse Nikhil Ghuse - 4 months ago 25
SQL Question

How to write following sql query in NHibernate

I want to write following sql query in NHibernate.
please help me

SELECT
Department.Dept_ID as y0_,
Department.DeptName as y1_,
(SELECT
TOP(1) Employee.EmpName as y0_
FROM
Employee
WHERE
Employee.Emp_ID = Department.Dept_ID
ORDER BY
Employee.Emp_ID asc) as y2_
FROM
Department

Answer

you can write the above sql query in NHibernate as follows:

DeptartmentEntity DeptartmentEntityAlias = null;
            var results = NHSession.QueryOver<DeptartmentEntity>(() => DeptartmentEntityAlias)
                            .SelectList(list => list
                                .Select(u => u.DeptId)
                                .Select(u => u.DeptName)
                                .SelectSubQuery(
                                    QueryOver.Of<EmployeeEntity>()
                                        // Creates a correlated subquery
                                        .Where(us => us.Department.DeptId == DeptartmentEntityAlias.DeptId)
                                        .OrderBy(us => us.EmpId).Asc
                                        .Select(tx => tx.EmployeeName)
                                        .Take(1)
                                    )
                            )
                            .List<object[]>();