EF Core Insert

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 Initial

Third, execute the Update-Database in PMC to create a new database and tables:

Update-Database

Inserting 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 the DbContext class.

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

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);

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

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 the DbContext to 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();

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;

Id          Name
----------- --------------------------
1           Sales
2           Marketing
3           Logistics
4           HR
5           General Administration
6           IT

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

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);

Id          FirstName  LastName   Salary                         JoinedDate                  DepartmentId
----------- ---------- ---------- ------------------------------ --------------------------- ------------
1           John       Doe        120000.00                      2023-01-05 00:00:00.0000000 1

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

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_0

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);

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

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

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);

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 1

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);

Id          Phone           Email           EmployeeId
----------- --------------- --------------- -----------
1           (408)-123-456   bob@example.com 3

Insert an entity that has a many-to-many relationship #

The following example illustrates how to create a new employee with:

FirstName, LastName, JoinedDate, Salary
DepartmentId 6, which is the IT department.
A list of two Skill objects

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

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);

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 6

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;

Id          Title
----------- -------------------------
1           C# Programming
2           ASP.NET Core

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);

EmployeesId SkillsId
----------- -----------
4           1
4           2

Summary #

Create a new entity, add it to the corresponding DbSet, and call the SaveChanges() to insert a new row into the corresponding table.

Was this helpful?