How to Join in LINQ using Lambda expressions and get the result in DTO?

Question

Hi I have a query as -

var query=from e in DataContext.Employees
          join d in DataContext.Dept
          on e.DeptId  equals d.Id
          join o in DataContext.OtherInfo
          on e.Id equals o.EmployeeId 
          where e.EmployeeId==4 
          select new Employee_Dept//DTO
          {
              EmployeeName=e.Name,
              DeptName=d.Name
              EmployeeId=e.ID
              DeptId=d.Id
              ContactNo=o.ContactNo
          }

I want to write it using Lambda expressions this is could write -

var query = DataContext.Employees.Join(Dept,e=>e.DeptId,d=>d.Id,(e,d)).Where(e=>e.EmployeeId=4)

can anyone help me complete this query. Thanks for your help.

Answer 1

It's usually wrong to use join in LINQ to SQL. Just write:

var query=from e in DataContext.Employees
          where e.EmployeeId==4 
          select new Employee_Dept//DTO
          {
              EmployeeName=e.Name,
              DeptName=d.Dept.Name
              EmployeeId=e.ID
              DeptId=d.Dept.Id
          }

Answer 2

I agree with Craig Stuntz that it is wrong usage of Join, but you can express the same LINQ query using extension methods in following way:

return DataContext.Employees
         .Join(DataContext.Dept, e => e.DeptId, d => d.Id, (e,d) => new { Employee = e, Department = d })
         .Join(DataContext.OtherInfo, s => s.Employee.Id, o => o.EmployeeId, (s, o) => new { Employee => s.Employee, Department = s.Department, OtherInfo = o })
         .Where(e => e.Employee.Id == 4)
         .Select(e => select new Employee_Dept//DTO
          {
              EmployeeName=e.Employee.Name,
              DeptName=e.Department.Name
              EmployeeId=e.Employee.ID
              DeptId=e.Department.Id
              ContactNo=e.OtherInfo.ContactNo
          }