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

2
votes

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.

2
linqjoinlambda
why? join extension methods are ugly--use the query syntax you already have. - David Fox
My Boss's wish is my command unfortunately....ya but i feel that too.. - Vishal
Agree with David. Extension syntax might look "cooler" to person coding it, but it will get changed to query syntax by the next developer that sees it because it's a pain to comprehend. - Mike M.
please don't take this as "go challenge your boss", but does he or she have a valid reason for this? the only real winner here is query syntax because it's easier to read. - David Fox

2 Answers

6
votes

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
          }
4
votes

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
          }