C# WebAPI Cross update inner join data with multiple tables

Question

As from the previous question from here

I am willing update these data as:

employeeTable: FirstName, LastName departmentTable: departmentName workingshift: duration

However, now I try to modify my department_id it comes out error said:

The property 'department_id' is part of the object's key information and cannot be modified.

Here is my query for joining data:

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

Secondly, here is my coding for trying to modify the department_id

        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();
        }

Requirement:

• My ServicesEntities is DSE
• I am using Entities Framework
• My department_id is the joined object, same goes to my shift_id
• My `department_id' is not an auto increment id

Database Attributes:

Employee Table has employee_id, FirstName, LastName, Gender, Salary

Department Table has department_id, department_name

WorkingShift Table has shift_id, duration

Question:

Lets say I would like to update data as follows:

Employee FirstName = Stack, LastName = Overflow,

Department department id = 4, Department Name = Web,

Shift Shift_id = 2,

What coding should I perform as above session?

Answer 1

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.