how to write LINQ to get a single object with an assoicate object instance?

Question

Suppose I have employee and department table, employee has foreign key departmentID that is primary of department table.

I use following code to get a single instance of an entity based on Linq to SQL: db.Employee.SingleOrDefault(e => e.empid == id); but I want to get the instance of department at the same time. How to write linq for this requirement?

Answer 1

If the foreign key relationship is defined in the database, then it should be automatically added to the Linq object. Hence, the Employee object returned will have a fully populated Department property on it.

Update: I don't get to fix something that Jon Skeet wrote often, so here's me chance:

 var query = from employee in db.Employee
             where employee.empid == id
              select new { 
                  Employee = employee, 
                  Department = employee.department 
              };

Linq2Sql will automatically do the join!