In this tutorial let us look at how to add a record, add multiple records to the database. Before inserting records into the database, we must add the entities to the context first. To do that we use the Add & AddRange methods. Once the records are added to the context, we can call the SaveChanges method, which sends the insert query to the database. The EF Core also takes care of updating identity values generated in the database in the entity. We also show you how to add related entities or data.
Source Code:
The source code of this project available in GitHub. It also contains the script of the database
Add Single Record
The code below creates a new instance of the Department object. Then it uses the Add method of the DbSet to add the newly created Department entity to the DbContext. Note that the Add method adds the new entity in Added State. Finally, we call the SaveChanges method to insert the new Department record into the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 | public void AddSingleRecord() { using (EFCoreContext db = new EFCoreContext()) { Department department = new Department(); department.Name = "Secuirty"; db.Departments.Add(department); db.SaveChanges(); } } |
The DepartmentID is an identity field. The database generates its value when we insert the record into the database. The EF Core retrieves the newly inserted value of DepartmentID and updates the DepartmentID in the entity. This will keep the entity in sync with the database.
The SQL Query of the above method is as follows. Note the select statement, which retrieves the DepartmentID
1 2 3 4 5 6 7 8 | INSERT INTO [Departments] ([Descr], [Name]) VALUES (@p0, @p1); SELECT [DepartmentID] FROM [Departments] WHERE @@ROWCOUNT = 1 AND [DepartmentID] = scope_identity(); |
Whenever we add an entity using the Add or AddRange method, the context marks the state of the entity as added. Hence when we call the SaveChanges Context will create an insert query and sends it to the database.
AddRange
You can add multiple records or multiple objects using the AddRange method of DbSet as shown in the following code. The code creates a list of department objects and inserts two new departments to the list. We add the list to the context using the AddRange method.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | using (EFCoreContext db = new EFCoreContext()) { List<Department> deps = new List<Department>(); deps.Add(new Department { Name = "Dept1", Descr = "" }); deps.Add(new Department { Name = "Dept2", Descr = "" }); db.Departments.AddRange(deps); db.SaveChanges(); } //SQL Command INSERT INTO [Departments] ([Descr], [Name]) VALUES (@p0, @p1); SELECT [DepartmentID] FROM [Departments] WHERE @@ROWCOUNT = 1 AND [DepartmentID] = scope_identity(); INSERT INTO [Departments] ([Descr], [Name]) VALUES (@p0, @p1); SELECT [DepartmentID] FROM [Departments] WHERE @@ROWCOUNT = 1 AND [DepartmentID] = scope_identity(); |
The EF Core will save only those records, which you add to the context using the Add method or AddRange method.
For Example, consider the following example. We retrieve all the Departments into the deps List. We create two more Departments (Dept3 & Dept4) and add it the deps list. In the next line, we add the Dept5 using the Add method.
This code only inserts the Dept5 to the database. The Dept3 & Dept4 are not added as EF Core is not aware of them as they are not added to the context.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | using (EFCoreContext db = new EFCoreContext()) { List<Department> deps = db.Departments.ToList(); //These records are not added deps.Add(new Department { Name = "Dept3", Descr = "" }); deps.Add(new Department { Name = "Dept4", Descr = "" }); //This record is added db.Departments.Add(new Department { Name = "Dept5", Descr = "" }); db.SaveChanges(); } |
The Context will track only those entities, which it retrieves from the database. It also tracks those entities which you add to the context using Add or AddRange methods. Hence it tracks all entities which are in the deps collection as they came from the database using. db.Departments.ToList(). But later we added two more departments to deps directly. The Context is not aware of these new records. Hence when the context calls SaveChanges, it does not create an Insert query for them.
Identity Insert
If you provide the value to identity column, EF Core always includes it in Insert Query. This behavior is different from Entity Framework, which always ignored.
For Example, we provide the hard coded value to the DepartmentID, which is a Primary key. This query will result in following error.
1 2 3 4 5 6 7 | Department department = new Department(); department = new Department { DepartmentID = 10, Name = "Sales", Descr = "" }; db.Departments.Add(department); db.SaveChanges(); |
1 2 3 | SqlException: Cannot insert explicit value for identity column in table 'Departments' when IDENTITY_INSERT is set to OFF. |
To do that we need to send the SET IDENTITY_INSERT Departments ON query to the database. But catch here is that the SaveChanges always creates a new database connection and closes it after it finished. Hence we need to open a connection to the database in the code. The SaveChanges uses the existing connection and does not open a new connection. Now you can send raw SQL command using the ExecuteSqlRaw. Remember to close the connection when done as shown in the code below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | using (EFCoreContext db = new EFCoreContext()) { Department department = new Department(); department = new Department { DepartmentID = 10, Name = "Sales", Descr = "" }; db.Departments.Add(department); try { db.Database.OpenConnection(); db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT Departments ON"); db.SaveChanges(); db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT Departments OFF;"); } catch (Exception ex) { throw; } finally { db.Database.CloseConnection(); } Console.WriteLine("Departments added "); Console.ReadKey(); } |
It is a very common scenario that you may want to add related entities. .
The following example code creates a new Employee. It then creates a new Department . The newly created Employee is added to the Employees Navigation property.
Now, when we add the new dep to the context using the Add method it also adds the Employee automatically. Hence SaveChanges will inserts a new employee and department in the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | using (EFCoreContext db = new EFCoreContext()) { //Create new employee Employee emp = new Employee(); emp.FirstName = "Anil"; emp.LastName = "Kumble"; // Creat a new department Department department = new Department(); department.Name = "Bowling"; department.Employees = new List<Employee>(); department.Employees.Add(emp); //Add department to Departments //Note that we are not adding Employee. Employee is already added to Dep db.Departments.Add(department); //Save db.SaveChanges(); } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | INSERT INTO[Departments] ([Descr], [Name]) VALUES(@p0, @p1); SELECT[DepartmentID] FROM[Departments] WHERE @@ROWCOUNT = 1 AND[DepartmentID] = scope_identity(); INSERT INTO[Employees] ([DepartmentID], [FirstName], [LastName]) VALUES(@p2, @p3, @p4); SELECT[EmployeeID] FROM[Employees] WHERE @@ROWCOUNT = 1 AND[EmployeeID] = scope_identity(); |
Or in this case, we add two employees to Dep and then add the Department to the Context.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | using (EFCoreContext db = new EFCoreContext()) { // Creat a new department Department department = new Department(); department.Name = "Bowling"; department.Employees = new List<Employee>(); department.Employees.Add(new Employee { FirstName = "Anil", LastName = "Kumble" }); department.Employees.Add(new Employee { FirstName = "Harbajan", LastName = "Singh" }); //Add department to Departments //Note that we are not adding Employee. Employee is already added to Dep db.Departments.Add(department); //Save db.SaveChanges(); Console.WriteLine("Department {0} ({1}) is added ", department.Name, department.DepartmentID); Console.ReadKey(); } |
We create a new employee and assign him an existing department, which we queried from the database. Then Add the employee to the context and call SaveChanges.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | using (EFCoreContext db = new EFCoreContext()) { // Get the existing Department Department department = db.Departments.Where(f=> f.Name== "Bowling").FirstOrDefault(); //Add Employee with Deparmtnet Employee emp = new Employee(); emp.FirstName = "Kapil"; emp.LastName = "Dev"; emp.Department = department; db.Employees.Add(emp); //Save db.SaveChanges(); Console.WriteLine("Department {0} ({1}) is added ", department.Name, department.DepartmentID); Console.WriteLine("Employee {0} ({1}) is added in the department {2} ", emp.FirstName, emp.EmployeeID, emp.Department.Name); Console.ReadKey(); } |
You can also assign the ForeignKey value as shown below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | using (EFCoreContext db = new EFCoreContext()) { //Add Employee with Deparmtnet Employee emp = new Employee(); emp.FirstName = "Kapil"; emp.LastName = "Dev"; emp.DepartmentID = 10; db.Employees.Add(emp); //Save db.SaveChanges(); //Console.WriteLine("Employee {0} ({1}) is added in the department {2} ", emp.FirstName, emp.EmployeeID, emp.Department.Name); Console.ReadKey(); } |
Gracias
thank you very much! well written article!