更新内部联接数据

本文介绍了更新内部联接数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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