What is the Linq Query of the following SQL

Question

I am new to nHibernate. I have following SQL inner join query,

SELECT e.name, 
       d.deptname 
FROM   demo_employee AS e 
       INNER JOIN demo_department AS d 
               ON e.departmentid = d.deptid

What is the linq expression using Query Over of the following sql query.

I have written the following query but it is qiving me error at following place "c.Department.DeptId".

 var Query =
    Session.QueryOver<Employee>()
        .JoinQueryOver<Department>(c => c.Department.DeptId)
            .Where(k => k.Name == k.DeptId);

That should be fairly simple, but you'll learn a lot more if you try to solve it for yourself first: what have you tried, and what happened? Please update your question with the LINQ query you've already tried, and any errors you received etc. — Jon Skeet, Jan 06 '14 at 06:07
@Undefiend No need to update. Your question put on hold. Try with a better question next time — Subin Jacob, Jan 06 '14 at 06:09
@SubinJacob: No, the point of putting questions on hold is that they can be edited and improved, then reopened. There's no need for the OP to ask a *new* question when this question can be salvaged. — Jon Skeet, Jan 06 '14 at 06:11
...before your question will be reopened, The query you've tried won't work. The ORM world works a bit differently. You do **not** have to express the **JOIN**. It is done by your mapping. So just use: `.JoinQueryOver(c => c.Department)` to get both table joined. The call `Where()` is saying: Department.Name == Department.DeptId... which is most likely not what you want — Radim Köhler, Jan 06 '14 at 06:14
BTW, if you would like to see the power of QueryOver, check this Q and A: http://stackoverflow.com/q/20528760/1679310 — Radim Köhler, Jan 06 '14 at 06:18

score 2 · Accepted Answer · answered Jan 06 '14 at 09:02

Here is the QueryOver version.

We are using aliasing, ie: Declaration of the null reference Employee employee = null to be used for fully-typed access to all properties. NHibernate Expression parser will convert them into strings (column names) later based on mapping.

Also we could get references to FROM parts of the QueryOver. The query represents Employee, and the joined query represents the Department (depQuery), which we can directly filter.

Finally we can use List() to get (SELECT) all mapped properties, or do some projection: .Select() or .SelectList(). With projection, we should be working with some DTO.

// aliasing, see the Projections of the SELECT clause
Employee employee = null;
Department department = null;

// the Employee query, with alias
var query = session.QueryOver<Employee>(() => employee);

// this way we can have reference to department query, if needed
var depQuery = query.JoinQueryOver<Department>(() => employee.Department, () => department);

// WHERE
// filtering the Employee
query.Where(e => e.Name == "Undefined");

// the department filtering
depQuery.Where(d => d.DeptName == "Management");


// paging, if needed
query.Skip(100);
query.Take(10);

1) select all properties

var list = query.List<Employee>();

var employeeName = list.ElementAt(0).Name;
var departmentName = list.ElementAt(0).Department.DeptName;

2) projection

// The DTO class to be projected into
public class MyDTO
{
    public virtual string EmployeeName { get; set; }
    public virtual string DepartmentName { get; set; }
}


// Select with projection of just two columns
MyDTO dto = null;

// SELECT
// projection, explicit property/column to be selected only
query.SelectList(l => l
    // the full power of aliasing
    .Select(() => employee.Name).WithAlias(() => dto.EmployeeName)
    .Select(() => department.DeptName).WithAlias(() => dto.DepartmentName)
    );

var list = query
    .TransformUsing(Transformers.AliasToBean<MyDTO>())
    .List<MyDTO>();

What is the Linq Query of the following SQL

1 Answers1