Update inner join data

Question

As brought forwarded from the previous question here.

I am using the DTO method to inner join data. Now is it possible to update data at my joined result? How the data will be updated back to the origin table where it should be possible be?

Requirement:

• I am using Entity Framework, C#, ASP.NET Web API
• I am using SQL Server

Currently I successfully joined employee and department table based on their ID. I joined employee and workingshifts based on their shift_id.

The inner join query was here:

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

So the data are here:

[{"FirstName":"gg","LastName":"wp","Gender":"NoGender","Salary":8,"Department_id":1, 
   "Department_Name":"RND","Shift_id":"B","Duration":"afternoon"}]

Now I would like to update the information as follows:

FirstName: good game
LastName: well played
Gender: IGender
Salary: 8888
Shift_id: A
Duration: Morning

May I know that what code that should be done in my C# and Linq? Will it update back to my database tables? Moreover, I heard about this solution by using SQL stored procedure, you may show me this approach as well

Answer 1

Well, as per your comment. The SQL Server for updating a value/ values based on a join would be

UPDATE e
    SET e.FirstName = 'good game',
    e.LastName = 'well played'
FROM employees e
    INNER JOIN departments d ON d.department_id = e.department_id
    INNER JOIN workingshifts ws ON e.shift_id equals ws.shift_id
WHERE e.FirstName = 'gg'
    AND e.LastName = 'wp'

Without knowing the structure of the tables, I have simply used an INNER JOIN.

The Entity Framework to do this may be found:

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 e.FirstName == "gg"
              && e.LastName == "wp"
              select e).FirstOrDefault()    // As it seems like we are just wanting employees however if you are expecting to return more than one employee back, you could use .ToList() instead of .FirstOrDefault()

if(result != null)
{
    result.FirstName = "Good Game";
    result.LastName = "Well Played";
    await context.SavechangesASync();   // If inside an async function, else just SaveChanges()
}

This is just if you are returning one employee. Like I said, if you are returning a list, you will need to incorporate the changes in the code above. With the above, you will also need to do a where on the result set where you want to the return the values you want

EDIT: Changed ' to " in the Entity Framework approach