Summary: in this tutorial, you will learn how to use the EF Core SaveChanges() method to insert rows into tables.
Setting up an EF Core project
We’ll use the following data model which includes Department, Employee, EmployeeProfile, and Skill:
First, download the HR project:
Second, run the Add-Migration command in the Package Manager Console (PMC) to create a new migration:
Add-Migration InitialThird, execute the Update-Database in PMC to create a new database and tables:
Update-DatabaseInserting a single row
To insert a new row into a table, you follow these steps:
- First, create a new entity.
- Second, add it to the corresponding
DbSet. - Third, call the
SaveChanges()method of theDbContextclass.
For example, the following creates a new department, adds it to the DbSet<Department>, and calls the SaveChanges() method of the HRContext to insert a department into the Departments table:
using HR;
using var context = new HRContext();
var department = new Department() { Name = "Sales" };
context.Departments.Add(department);
context.SaveChanges();Code language: C# (cs)When running the program, you’ll see that EF Core generates the following SQL command:
-- [Parameters=[@p0='Sales' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [Departments] ([Name])
OUTPUT INSERTED.[Id]
VALUES (@p0);Code language: SQL (Structured Query Language) (sql)In this example, EF Core uses a simple INSERT statement to insert a new department into the Departments table and returns the inserted Id.
If you view the Departments table, you’ll see one row inserted:
Id Name
----------- ----------
1 Sales
Code language: plaintext (plaintext)Inserting multiple rows
To insert multiple rows into a table:
- First, create multiple entities
- Second, add the entities to the corresponding
DbSet - Third, call the
SaveChanges()method of theDbContextto create the entities in the database table.
For example, the following illustrates how to insert multiple Department entities:
using HR;
using var context = new HRContext();
var deparments = new List<Department>()
{
new Department(){ Name = "Marketing"},
new Department(){ Name = "Logistics"},
new Department(){ Name = "HR"},
new Department(){ Name = "General Administration"},
new Department(){ Name = "IT"},
};
foreach (var department in deparments)
{
context.Departments.Add(department);
}
context.SaveChanges();Code language: C# (cs)Behind the scenes, EF Core generates a MERGE statement to insert multiple rows into a table as shown in the log:
-- [Parameters=[@p0='Marketing' (Nullable = false) (Size = 4000), @p1='Logistics' (Nullable = false) (Size = 4000), @p2='HR' (Nullable = false) (Size = 4000), @p3='General Administration' (Nullable = false) (Size = 4000), @p4='IT' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
MERGE [Departments] USING (
VALUES (@p0, 0),
(@p1, 1),
(@p2, 2),
(@p3, 3),
(@p4, 4)) AS i ([Name], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Name])
VALUES (i.[Name])
OUTPUT INSERTED.[Id], i._Position;Code language: SQL (Structured Query Language) (sql)Id Name
----------- --------------------------
1 Sales
2 Marketing
3 Logistics
4 HR
5 General Administration
6 ITCode language: plaintext (plaintext)Insert an entity that has a one-to-many relationship
The following creates an employee with department id 1. Since the Employee has the DepartmentId foreign key property, you need to assign a valid value to it:
using HR;
using var context = new HRContext();
var employee = new Employee()
{
FirstName = "John",
LastName = "Doe",
Salary = 120000,
JoinedDate = new DateTime(2023,01,05),
DepartmentId = 1
};
context.Employees.Add(employee);
context.SaveChanges();Code language: C# (cs)If you run the program, EF Core will generate the following SQL and execute it to insert a new row into the Employees table:
-- [Parameters=[@p0='1', @p1='John' (Nullable = false) (Size = 4000), @p2='2023-01-05T00:00:00.0000000', @p3='Doe' (Nullable = false) (Size = 4000), @p4='120000' (Precision = 18) (Scale = 2)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [Employees] ([DepartmentId], [FirstName], [JoinedDate], [LastName], [Salary])
OUTPUT INSERTED.[Id]
VALUES (@p0, @p1, @p2, @p3, @p4);Code language: SQL (Structured Query Language) (sql)Id FirstName LastName Salary JoinedDate DepartmentId
----------- ---------- ---------- ------------------------------ --------------------------- ------------
1 John Doe 120000.00 2023-01-05 00:00:00.0000000 1Code language: plaintext (plaintext)The following example finds the IT department and creates an employee that belongs to that department:
using HR;
using var context = new HRContext();
// find the IT department
var departmentName = "IT";
var department = context.Departments
.Where(d => d.Name == departmentName)
.FirstOrDefault();
if (department == null)
{
Console.WriteLine($"Department {departmentName} not found.");
return;
}
// create a new employee
var employee = new Employee()
{
FirstName = "Jane",
LastName = "Doe",
Salary = 95000,
JoinedDate = new DateTime(2023, 1, 5),
Department = department,
};
context.Employees.Add(employee);
context.SaveChanges();Code language: C# (cs)In this example, EF Core executes two queries:
First, find the department whose name is IT using a SELECT statement:
-- [Parameters=[@__departmentName_0='IT' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [d].[Id], [d].[Name]
FROM [Departments] AS [d]
WHERE [d].[Name] = @__departmentName_0Code language: SQL (Structured Query Language) (sql)Second, insert a new row into the Employees table:
-- [Parameters=[@p0='6', @p1='Jane' (Nullable = false) (Size = 4000), @p2='2023-01-05T00:00:00.0000000', @p3='Doe' (Nullable = false) (Size = 4000), @p4='95000' (Precision = 18) (Scale = 2)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [Employees] ([DepartmentId], [FirstName], [JoinedDate], [LastName], [Salary])
OUTPUT INSERTED.[Id]
VALUES (@p0, @p1, @p2, @p3, @p4);Code language: SQL (Structured Query Language) (sql)Id FirstName LastName Salary JoinedDate DepartmentId
----------- ---------- ---------- ------------------------------ --------------------------- ------------
1 John Doe 120000.00 2023-01-05 00:00:00.0000000 1
2 Jane Doe 95000.00 2023-01-05 00:00:00.0000000 6Code language: plaintext (plaintext)Insert an entity that has a one-to-one relationship
The following example illustrates how to insert an employee with a profile into the Employees and EmployeeProfile tables:
using HR;
var employee = new Employee()
{
FirstName = "Bob",
LastName = "Climo",
JoinedDate = new DateTime(2023, 1, 5),
Salary=90000,
DepartmentId = 1,
Profile = new EmployeeProfile()
{
Phone = "(408)-123-456",
Email = "[email protected]"
},
};
using var context = new HRContext();
context.Employees.Add(employee);
context.SaveChanges();Code language: C# (cs)In this example, we create a new Employee object and assign 1 to the DepartmentId property and a new EmployeeProfile object to the Profile property.
Once we add the employee to the Employees DbSet and call the SaveChanges() method, EF Core generates and executes the two SQL commands:
First, insert a new row into the Employees table and return the Id of the row:
-- [Parameters=[@p0='1', @p1='Bob' (Nullable = false) (Size = 4000), @p2='2023-01-05T00:00:00.0000000', @p3='Climo' (Nullable = false) (Size = 4000), @p4='900000' (Precision = 18) (Scale = 2)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [Employees] ([DepartmentId], [FirstName], [JoinedDate], [LastName], [Salary])
OUTPUT INSERTED.[Id]
VALUES (@p0, @p1, @p2, @p3, @p4);Code language: SQL (Structured Query Language) (sql)Id FirstName LastName Salary JoinedDate DepartmentId
----------- ---------- ---------- ------------------------------ --------------------------- ------------
1 John Doe 120000.00 2023-01-05 00:00:00.0000000 1
2 Jane Doe 95000.00 2023-01-05 00:00:00.0000000 6
3 Bob Climo 90000.00 2023-01-05 00:00:00.0000000 1Code language: plaintext (plaintext)Second, insert a new row into the EmployeeProfiles table. The EmployeeProfile object has the EmployeeId returned by the first query:
-- [Parameters=[@p5='[email protected]' (Nullable = false) (Size = 4000), @p6='3' (Nullable = true), @p7='(408)-123-456' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [EmployeeProfiles] ([Email], [EmployeeId], [Phone])
OUTPUT INSERTED.[Id]
VALUES (@p5, @p6, @p7);Code language: C# (cs)Id Phone Email EmployeeId
----------- --------------- --------------- -----------
1 (408)-123-456 bob@example.com 3Code language: CSS (css)Insert an entity that has a many-to-many relationship
The following example illustrates how to create a new employee with:
FirstName,LastName,JoinedDate,SalaryDepartmentId6, which is theITdepartment.- A list of two
Skillobjects
using HR;
var employee = new Employee()
{
FirstName = "Alice",
LastName = "Smith",
JoinedDate = new DateTime(2023, 1, 5),
Salary = 70000,
DepartmentId = 6, // IT department
Skills = new List<Skill>()
{
new (){ Title="C# Programming" },
new (){ Title="ASP.NET Core" }
}
};
using var context = new HRContext();
context.Employees.Add(employee);
context.SaveChanges();Code language: C# (cs)Once running the program, EF Core executes three SQL statements.
First, insert a new row into the Employees table and return the id using the INSERT statement:
-- [Parameters=[@p0='6', @p1='Alice' (Nullable = false) (Size = 4000), @p2='2023-01-05T00:00:00.0000000', @p3='Smith' (Nullable = false) (Size = 4000), @p4='70000' (Precision = 18) (Scale = 2), @p5='C# Programming' (Nullable = false) (Size = 4000), @p6='ASP.NET Core' (Nullable = false) (Size = 4000)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [Employees] ([DepartmentId], [FirstName], [JoinedDate], [LastName], [Salary])
OUTPUT INSERTED.[Id]
VALUES (@p0, @p1, @p2, @p3, @p4);Code language: SQL (Structured Query Language) (sql)Id FirstName LastName Salary JoinedDate DepartmentId
----------- ---------- ---------- ------------------------------ --------------------------- ------------
1 John Doe 120000.00 2023-01-05 00:00:00.0000000 1
2 Jane Doe 95000.00 2023-01-05 00:00:00.0000000 6
3 Bob Climio 90000.00 2023-01-05 00:00:00.0000000 1
4 Alice Smith 70000.00 2023-01-05 00:00:00.0000000 6Code language: CSS (css)Second, insert multiple rows into the Skills table using the MERGE statement and return the inserted ids:
MERGE [Skills] USING (
VALUES (@p5, 0),
(@p6, 1)) AS i ([Title], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Title])
VALUES (i.[Title])
OUTPUT INSERTED.[Id], i._Position;Code language: SQL (Structured Query Language) (sql)Id Title
----------- -------------------------
1 C# Programming
2 ASP.NET CoreCode language: plaintext (plaintext)Third, insert multiple rows into the EmployeeSkill table using the INSERT statement:
-- [Parameters=[@p7='4', @p8='1', @p9='4', @p10='2'], CommandType='Text', CommandTimeout='30']
SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [EmployeeSkill] ([EmployeesId], [SkillsId])
VALUES (@p7, @p8),
(@p9, @p10);Code language: SQL (Structured Query Language) (sql)EmployeesId SkillsId
----------- -----------
4 1
4 2Code language: plaintext (plaintext)Summary
- Create a new entity, add it to the corresponding
DbSet, and call theSaveChanges()to insert a new row into the corresponding table.
