Learn how an entity framework core update records to the database. We can update records either in connected or disconnected scenarios. In the connected Scenario, we open the context, query for the entity, edit it, and call the SaveChanges method. In the Disconnected scenario, we need to is to attach/add it to the context. Set its State as Modified and then call the SaveChanges But we need to be careful in a disconnected scenario as the update will update all the fields, There is a chance of accidentally overwriting a field. We also show you how to update multiple records & Related Data etc.
Source Code:
The source code of this project available in GitHub. It also contains the script of the database
Table of Contents
Update the Records
Updating the entity involves getting the entity from the database, make the necessary changes, and then call the SaveChanges to persist the changes in the database.
There are two Scenario’s that arise, when you update the data to the database.
- Connected scenario
- Disconnected scenario
Let us look at both scenarios.
Update Records in Connected Scenario
Whenever we make a query to the database, the context retrieves it and mark the entity as Unchanged. It then starts to track any changes made to it a process we call it as Change Tracking. Whenever we make changes to the entity, the context marks the entity as Modified. When we call the SaveChanges the context creates an update SQL command and updates the database.
The above scenario works correctly if the entity is connected with context, which loads it. i.e the context is not closed or disposed of. We call this connected scenario.
Example
The following example updates the Descr field of Accounts Department in Connected Scenario.
First, we create a new context and retrieve the existing department data from the database. We modify the Descr field. Since the context is open, it will mark the entity as Modified. Finally, when we call the SaveChanges, the context generates the update SQL statement to persist the change to the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | using (EFCoreContext db = new EFCoreContext()) { department = db.Departments.Where(d => d.Name == "Sales").First(); department.Descr = "This is Sales Department"; db.SaveChanges(); Console.WriteLine("Department {0} ({1}) is Updated ", department.Name, department.DepartmentID); } |
If you log the SQL Command, you will see the following Update Statement. Note that query only updates the Descr field and not the other fields. This is because, context not only tracks the entity as a whole, but also the individual properties.
1 2 3 4 5 6 7 8 9 10 | SELECT TOP(1) [d].[DepartmentID], [d].[Descr], [d].[Name] FROM [Departments] AS [d] WHERE [d].[Name] = N'Sales' UPDATE [Departments] SET [Descr] = @p0 WHERE [DepartmentID] = @p1; SELECT @@ROWCOUNT; |
Update Records in Disconnected Scenario
The connected scenario is not always possible in real life apps.
For Example, in a web application, the user requests for the Department model. We create a new instance of the context, fetch the data, close the context, and sends the data back to the user. The user will modify the data and sends it back. Now we will create a new instance of the context. The newly created context is not aware of the Department model. Hence it won’t update the database if you call SaveChanges. To update this entity we need to attach the Department to the context and inform it to mark its status as Modified. Now if we call the SaveChanges method, the context will send an update query to the database.
Example
In the following example, we load the Purchase department and close the context. Now we make the changes to the Descr field.
Now, we open a new context. This new context is not aware of any Department model. Hence we need it to attach it to the context and set its state as Modified. We do that using the Entry method of the Context. The Entry method allows us to view the tracked entities, modify their status, etc. We set the State of the entity to Modified.
Finally, we invoke the SaveChanges method to update the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | Department department; //Disconnected Scenario using (EFCoreContext db = new EFCoreContext()) { department = db.Departments.Where(d => d.Name == "Sales").First(); } department.Descr = "Sales Department-Disconnected Scenario"; using (EFCoreContext db = new EFCoreContext()) { db.Entry(department).State = EntityState.Modified; db.SaveChanges(); } |
If you take a look at the SQL update query, you will see the difference between the query created in connected & disconnected scenario. In connected Scenario, the query updates only the Descr filed. i.e because context is tracking it and knows which fields is changed. While in disconnected Scenario, the update query includes all the fields, because context does not know which fields are modified.
1 2 3 4 | UPDATE [Departments] SET [Descr] = @p0, [Name] = @p1 WHERE [DepartmentID] = @p2; |
We can also use the Attach or the Add method to add the entity to the context. But remember that the Add method adds the entity with the state as Added. While if you use the Attach method, the entity is added with the state as Unchanged
1 2 3 4 5 6 7 8 9 | //Attach the entity and change its state //db.Departments.Attach(department); //db.Entry(department).State = System.Data.Entity.EntityState.Modified; //Add the Entity and change its state //db.Departments.Add(department); //db.Entry(department).State = System.Data.Entity.EntityState.Modified; |
Updating Records
You need to careful when updating the records in a disconnected way. For Example consider the case, where you need to update the Department record and change the Descr field.
The Wrong Way
In this example, updateDepartment1 gets id of the Department & Descr to update as the argument.
We create a new Department and assign the DepartmentID & Descr. We attach this entity to context and set its state as Modified. The SaveChanges will update the record with DepartmentID is 2 with the new Descr Value. But it will also update the Name field as Null as we have not provided any value to it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | private void UpdateWrongWay(int id, string descr) { Department department = new Department(); department.DepartmentID = id; department.Descr = descr; using (EFCoreContext db = new EFCoreContext()) { db.Entry(department).State = EntityState.Modified; db.SaveChanges(); } } |
The Right Way
The correct way it to query and load the Department entity. Update only changed fields and invoke SaveChanges. In this way you will not overwrite any fields accidentally and the context generates the update query efficiently.
1 2 3 4 5 6 7 8 9 10 11 12 | private void UpdateRightWay(int id, string descr) { using (EFCoreContext db = new EFCoreContext()) { Department department = db.Departments.Where(f => f.DepartmentID == id).FirstOrDefault(); if (department == null) throw new Exception(""); department.Descr = descr; db.SaveChanges(); } } |
Update Multiple Records
In case of multiple Records, you can loop through them, query the department from the database, update the changed the field and call SaveChanges to update the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | private void UpdateMultipleRecords() { List<Department> departments = new List<Department>(); departments.Add(new Department { DepartmentID = 1, Descr = "Sales" }); departments.Add(new Department { DepartmentID = 2, Descr = "Purchase" }); departments.Add(new Department { DepartmentID = 3, Descr = "HR" }); using (EFCoreContext db = new EFCoreContext()) { foreach (var item in departments) { var dept = db.Departments.Where(f => f.DepartmentID == item.DepartmentID).FirstOrDefault(); if (dept == null) throw new Exception(""); dept.Descr = item.Descr; } db.SaveChanges(); } } |
how to we do as below statement in EF core?
UPDATE example SET col1=’value1′ WHERE is=100 AND editsequense=1