更新内部联接数据 [英] Update inner join data

问题描述

从上一个问题转发而来此处.

As brought forwarded from the previous question here.

我正在使用 DTO 方法来内部联接数据.现在可以根据我的合并结果更新数据了吗?数据将如何更新回到可能存在的原始表中?

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?

要求:

• 我正在使用Entity Framework，C#，ASP.NET Web API
• 我正在使用SQL Server

目前，我已根据他们的 ID 成功地加入了员工和部门表. 我根据他们的 shift_id 加入了员工和工作班次.

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

内部联接查询在这里:

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

数据在这里:

[{"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

我可以知道在C#和Linq中应该执行哪些代码吗?它会更新回我的数据库表吗? 而且，我听说过使用SQL存储过程的这种解决方案，您也可以向我展示这种方法

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

推荐答案

根据您的评论.用于基于联接更新一个/多个值的SQL Server将是

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'

我不知道表的结构，只使用了INNER JOIN.

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

这就是您要回派一名员工的情况.就像我说的，如果要返回列表，则需要将更改合并到上面的代码中.使用上面的方法，您还需要在结果集上执行where，以在其中返回所需的值

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

在实体框架方法中将'更改为"

Changed ' to " in the Entity Framework approach

这篇关于更新内部联接数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！