Summary: in this tutorial, you will learn various ways to update data in database tables in EF Core.
Setting up a sample project
First, download the HR sample project:
Download EF Core HR Sample Project
Second, run the Add-Migration command in the Package Manager Console (PMC) to create a new migration:
Add-Migration InitialCode language: C# (cs)Third, execute the Update-Database command in the PMC to create a new HR database and tables in the local SQL Server:
Update-DatabaseCode language: C# (cs)Performing a simple update
We’ll start with the Department entity:
Let’s start by inserting a new department into the Departments table by creating a new Department entity, adding it to the DbSet<Department>, and calling the SaveChanges() method of the HRContext:
using HR;
using var context = new HRContext();
var department = new Department() { Name = "Sales" };
context.Departments.Add(department);
context.SaveChanges();Code language: C# (cs)EF Core will insert one row into the Departments table with id 1.
To update an entity, you follow these steps:
- First, get the entity by querying it from the database.
- Second, make changes to the entity.
- Third, call the
SaveChanges()method of theDbContextto propagate the changes to the database.
For example, the following changes the name of the Sales department with Id 1 to Sales Force:
using HR;
using var context = new HRContext();
// get the department by Id
var department = context.Departments.Find(1);
if (department != null)
{
// make changes to the department
department.Name = "Sales Force";
// update the changes
context.SaveChanges();
}Code language: C# (cs)Note that the HRContext extends the DbContext and defines the DbSet such as Departments with the type DbSet<Department>.
How it works.
First, find the department with Id 1 by using the Find() method:
var department = context.Departments.Find(1);Code language: C# (cs)EF Core executes a SQL SELECT statement to find the department by Id:
-- [Parameters = [@__p_0 = '1' ], CommandType = 'Text', CommandTimeout = '30' ]
SELECT
TOP(1) [d].[Id],
[d].[Name]
FROM
[Departments] AS [d]
WHERE
[d].[Id] = @__p_0Code language: SQL (Structured Query Language) (sql)Second, change the department name to Sales Force:
department.Name = "Sales Force";Code language: C# (cs)Third, save the changes to the database by calling the Update() method of the HRContext:
context.SaveChanges();Code language: C# (cs)EF Core executes the SQL UPDATE statement to update the Departments table:
-- [Parameters=[@p1='1', @p0='Sales Force' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET COCOUNT ON;
UPDATE
[Departments]
SET
[Name] = @p0
OUTPUT 1
WHERE
[Id] = @p1;Code language: SQL (Structured Query Language) (sql)If you view the Departments table, you’ll see that the name has been updated successfully:
Id Name
----------- --------------------
1 Sales ForceCode language: plaintext (plaintext)Updating entity with Id
If you know the Id of the entity, you can update it by specifying the Id and new values for the properties that you want to change. For example:
using HR;
using var context = new HRContext();
// Update the name of department
// with Id 1 to Sales
var department = new Department()
{
Id = 1,
Name = "Sales",
};
context.Update(department);
context.SaveChanges();Code language: C# (cs)How it works.
First, create a new Department object by specifying the Id 1 and the new value for the Name property:
var department = new Department()
{
Id = 1,
Name = "Sales",
};Code language: C# (cs)Second, call the Update() method of the DbContext to update the entity:
context.Update(department);Code language: C# (cs)Third, call the SaveChanges() to apply the changes to the database:
context.SaveChanges();Code language: C# (cs)In this example, EF Core only needs to execute an UPDATE statement:
-- [Parameters=[@p1='1', @p0='Sales' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
UPDATE [Departments] SET [Name] = @p0
OUTPUT 1
WHERE [Id] = @p1;Code language: SQL (Structured Query Language) (sql)Notice that if the Id is invalid, EF Core won’t be able to find the record for updating. In this case, EF Core will raise an exception like the following example:
using HR;
using var context = new HRContext();
var department = new Department()
{
Id = 10, // Invalid Id
Name = "Sales",
};
context.Update(department);
context.SaveChanges(); // -> ExceptionCode language: C# (cs)Updating in a one-to-many relationship
The following program adds a new employee to the Employees table and a department to the Departments table:
using HR;
using var context = new HRContext();
var department = new Department() { Name = "Marketing" };
var employee = new Employee()
{
FirstName = "John",
LastName = "Doe",
Salary = 120_000,
JoinedDate = new DateTime(2023, 5, 1),
Department = department
};
context.Add(employee);
context.SaveChanges();Code language: C# (cs)The following shows the employee (not all fields) in the Employees table:
Id FirstName LastName DepartmentId
----------- --------------- --------------- ------------
1 John Doe 2Code language: plaintext (plaintext)And here are the contents of the Departments table:
Id Name
----------- ---------------
1 Sales
2 MarketingCode language: plaintext (plaintext)To update the department for the employee from Marketing to Sales, you can change the DepartmentId of the Employee entity and call the SaveChanges() method:
using HR;
using var context = new HRContext();
var employee = context.Employees.Find(1);
if(employee != null)
{
employee.DepartmentId = 2;
context.SaveChanges();
}Code language: C# (cs)Once you run the program, you’ll see that the DepartmentId is updated:
Id FirstName LastName DepartmentId
----------- --------------- --------------- ------------
1 John Doe 2Code language: plaintext (plaintext)Alternatively, you can change the Department property of the employee entity to another department and call the SaveChanges().
The following example changes the department of John Doe back to Sales:
using HR;
using var context = new HRContext();
// find the employee with id 1
var employee = context.Employees.Find(1);
if (employee != null)
{
// find the Sales department
var department = context.Departments
.Where(d => d.Name == "Sales")
.FirstOrDefault();
if (department != null)
{
employee.Department = department;
context.SaveChanges();
}
}Code language: C# (cs)How it works.
First, find the employee with id 1:
var employee = context.Employees.Find(1);Code language: C# (cs)EF Core executes a SELECT statement to find the employee by id:
-- [Parameters=[@__p_0='1'], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [e].[Id], [e].[DepartmentId], [e].[FirstName], [e].[JoinedDate], [e].[LastName], [e].[Salary]
FROM [Employees] AS [e]
WHERE [e].[Id] = @__p_0Code language: SQL (Structured Query Language) (sql)Second, find the department by name with the value "Sales" and get the first one:
var department = context.Departments
.Where(d => d.Name == "Sales")
.FirstOrDefault();Code language: C# (cs)-- [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [d].[Id], [d].[Name]
FROM [Departments] AS [d]
WHERE [d].[Name] = N'Sales'Code language: SQL (Structured Query Language) (sql)Third, update the department for the employee and call the SaveChanges() to apply the change to the database:
employee.Department = department;
context.SaveChanges();Code language: C# (cs)EF Core will execute the following UPDATE statement:
-- [Parameters=[@p1='1', @p0='1'], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
UPDATE [Employees] SET [DepartmentId] = @p0
OUTPUT 1
WHERE [Id] = @p1;Code language: SQL (Structured Query Language) (sql)Updating in a one-to-one relationship
The following example shows how to add a profile to an employee:
using HR;
using var context = new HRContext();
// find the employee with id 1
var employee = context.Employees.Find(1);
if (employee != null)
{
// create a profile for the employee
employee.Profile = new EmployeeProfile()
{
Phone = "(408)-111-2222",
Email = "[email protected]"
};
context.SaveChanges();
}Code language: C# (cs)EF Core inserts one row into the EmployeeProfiles table and set the value of the EmployeeId to the value of the Id of the employee:
Id Phone Email EmployeeId
----------- -------------------- -------------------- -----------
1 (408)-111-2222 [email protected] 1Code language: SQL (Structured Query Language) (sql)To update the phone of the employee, you can query the employee with a profile, modify the phone, and call SaveChanges() as follows:
using HR;
using Microsoft.EntityFrameworkCore;
using var context = new HRContext();
// find the employee with id 1
var employee = context.Employees
.Include(e => e.Profile)
.Where(e => e.Id == 1)
.FirstOrDefault();
if (employee is not null && employee.Profile is not null)
{
employee.Profile.Phone = "(408)-555-5555";
context.SaveChanges();
}Code language: C# (cs)How it works.
First, find the employee, including the profile, with id 1:
var employee = context.Employees
.Include(e => e.Profile)
.Where(e => e.Id == 1)
.FirstOrDefault();Code language: C# (cs) -- [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [e].[Id], [e].[DepartmentId], [e].[FirstName], [e].[JoinedDate], [e].[LastName], [e].[Salary], [e0].[Id], [e0].[Email], [e0].[EmployeeId], [e0].[Phone]
FROM [Employees] AS [e]
LEFT JOIN [EmployeeProfiles] AS [e0] ON [e].[Id] = [e0].[EmployeeId]
WHERE [e].[Id] = 1Code language: SQL (Structured Query Language) (sql)Second, modify the phone and update the database:
employee.Profile.Phone = "(408)-555-5555";
context.SaveChanges();Code language: C# (cs)-- [Parameters=[@p1='1', @p0='(408)-555-5555' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
UPDATE [EmployeeProfiles] SET [Phone] = @p0
OUTPUT 1
WHERE [Id] = @p1;Code language: SQL (Structured Query Language) (sql)If you view the EmployeeProfiles table, you’ll see that the phone of the employee is updated:
Id Phone Email EmployeeId
----------- -------------------- -------------------- -----------
1 (408)-555-5555 [email protected] 1Code language: plaintext (plaintext)Updating in a many-to-many relationship
The following add two skills to the employee with id 1:
using HR;
using var context = new HRContext();
var employee = context.Employees.Find(1);
if (employee is not null)
{
var skills = new List<Skill>()
{
new() { Title = "Sales Planning" },
new() { Title = "Sales Strategy" }
};
foreach (var skill in skills)
{
employee.Skills.Add(skill);
}
context.SaveChanges();
}Code language: C# (cs)EF Core will insert two new rows into the Skills table:
Id Title
----------- --------------------
1 Sales Planning
2 Sales StrategyCode language: SQL (Structured Query Language) (sql)And assign it to the employee with Id 1 by inserting two rows into the EmployeeSkill table:
EmployeesId SkillsId
----------- -----------
1 1
1 2Code language: SQL (Structured Query Language) (sql)The following shows how to remove the "Sales Strategy" from employee Id 1 and add a new skill "Market Trend Analysis" to the employee:
using HR;
using static System.Console;
using Microsoft.EntityFrameworkCore;
using var context = new HRContext();
// find employee with skills
var employee = context.Employees
.Include(e => e.Skills)
.Where(e => e.Id == 1)
.FirstOrDefault();
if (employee is not null && employee.Skills.Count > 0)
{
// remove the "Sales Strategy" skill
var skill = employee.Skills.Find(s => s.Title == "Sales Strategy");
if(skill is not null)
{
employee.Skills.Remove(skill);
}
// add "Market Trend Analysis" skill
employee.Skills.Add(new Skill() { Title = "Market Trend Analysis" });
}
context.SaveChanges();Code language: C# (cs)How it works.
First, find the employee with id 1 and also get the skills:
var employee = context.Employees
.Include(e => e.Skills)
.Where(e => e.Id == 1)
.FirstOrDefault();Code language: C# (cs)-- [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id], [t].[DepartmentId], [t].[FirstName], [t].[JoinedDate], [t].[LastName], [t].[Salary], [t0].[EmployeesId], [t0].[SkillsId], [t0].[Id], [t0].[Title]
FROM (
SELECT TOP(1) [e].[Id], [e].[DepartmentId], [e].[FirstName], [e].[JoinedDate], [e].[LastName], [e].[Salary]
FROM [Employees] AS [e]
WHERE [e].[Id] = 1
) AS [t]
LEFT JOIN (
SELECT [e0].[EmployeesId], [e0].[SkillsId], [s].[Id], [s].[Title]
FROM [EmployeeSkill] AS [e0]
INNER JOIN [Skills] AS [s] ON [e0].[SkillsId] = [s].[Id]
) AS [t0] ON [t].[Id] = [t0].[EmployeesId]
ORDER BY [t].[Id], [t0].[EmployeesId], [t0].[SkillsId]Code language: SQL (Structured Query Language) (sql)Second, disassociate the skill from the employee:
var skill = employee.Skills.Find(s => s.Title == "Sales Strategy");
if(skill is not null)
{
employee.Skills.Remove(skill);
}Code language: C# (cs) -- [Parameters=[@p0='1', @p1='2', @p2='Market Trend Analysis' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [EmployeeSkill]
OUTPUT 1
WHERE [EmployeesId] = @p0 AND [SkillsId] = @p1;
INSERT INTO [Skills] ([Title])
OUTPUT INSERTED.[Id]
VALUES (@p2);Code language: SQL (Structured Query Language) (sql)Skill table:
Id Title
----------- -------------------------
1 Sales Planning
2 Sales Strategy
3 Market Trend AnalysisCode language: plaintext (plaintext)Third, insert a new skill into the Skills table and associate the new skill with the employee:
employee.Skills.Add(new Skill() { Title = "Market Trend Analysis" });Code language: C# (cs)INSERT INTO [Skills] ([Title])
OUTPUT INSERTED.[Id]
VALUES (@p2);
-- [Parameters=[@p3='1', @p4='3'], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [EmployeeSkill] ([EmployeesId], [SkillsId])
VALUES (@p3, @p4);Code language: SQL (Structured Query Language) (sql)EmployeeSkill table:
EmployeesId SkillsId
----------- -----------
1 1
1 3Code language: plaintext (plaintext)Summary
- To update an entity, get its instance from the database, modify it, and call the
SaveChanges()method to update.
