score:2

Accepted answer

To resolve changing the department of the employee: select d should be select e to get the employee and update the employee's Department ID, not the Department record's ID.

my department table contains department_name so if I want to update my department_name then I will be using select d am I correct?

If you want to actually change the name of that department, then you can select the department entity and change its name. However, that depends on whether that's really what you want to do. If an employee is pointing at a Department ID = 1, name = "Department A", do you want to change that Department's name to "Department B" or does a Department B already exist with a different ID? (I.e. 2) If you point the Employee's DepartmentID to "2", then the associated department details would be coming from Department B, this is typically what you'd want to occur. If you wanted to change the department's name (and the name that appears for all employees associated to Department ID 1) then you can select that department and update it's name.

Looking at your original code:

var result = (from e in DSE.employees
              join d in DSE.departments on e.department_id equals d.department_id
              join ws in DSE.workingshifts on e.shift_id equals ws.shift_id
              where d.department_id == 1
              select d).FirstOrDefault();

if (result != null)
{
    result.department_id = 2;
    DSE.SaveChanges();
}

The Joins are essentially unnecessary since you aren't doing anything with the department or shifts. This could be simplified to:

var employee = DSE.employees.Where(e => e.department_id == 1)
                 .FirstOrDefault();

if (employee != null)
{
    employee.department_id = 2;
    DSE.SaveChanges();
}

When using methods like FirstOrDefault you should always include an Order By type clause to ensure you get a predictable order to get repeatable results.

If you actually want to update related data, such as the department's name:

var department = DSE.departments.Single(d => d.department_id == 1);
department.name = "New Name";
DSE.SaveChanges();

Here because we only expect one, and only 1 department to have an ID of 1, we should use Single rather than FirstOrDefault. If no department is found, or more than 1 department is found it will throw an exception. Better this exception telling us zero or more rows were found than returning a "OrDefault" method and tripping over a NullReferenceException down the road.

My examples use the Fluent methods offered by EF rather than the linq QL syntax, but the same behaviour can be implemented that way. I just find the fluent methods easier to structure and chain together.

With EF, the real power comes from mapping relationships via navigation properties so you don't need to expose FK properties or manually map out Join expressions like you would in SQL. EF can manage all of this behind the scenes. You can load entities and either eager or lazy load their related entities, such as looking to update data, or simply select fields from the entity and its related details and let EF build the suitable SQL.


Related Articles