Learn how an entity framework 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 already have the entity with use. Hence all 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, Hence 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.
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
The responsibility of updating the records falls with the DBContext class. 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. If we add a new entity to the context using the Add or AddRange it will mark it as Added. If you use the Remove to delete the entity, it marks it as Deleted. When we call the SaveChanges the context creates an insert, update or delete SQL command depending on the state of the entity to update 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 Modifiedtransparently. 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 15 16 17 18 19 20 21 | private void UpdateConnected() { Department dep; //Connected Scenario using (EFContext db = new EFContext()) { db.Database.Log = Console.WriteLine; dep = db.Departments.Where(d => d.Name == "Accounts").First(); dep.Descr = "This is Accounts Department"; db.SaveChanges(); Console.WriteLine("Department {0} ({1}) is Modified ", dep.Name, dep.DepartmentID); Console.ReadKey(); } } |
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 | UPDATE [dbo].[Departments] SET [Descr] = @0 WHERE ([DepartmentID] = @1) |
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. When the user asks to update the Department, 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 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | private void UpdateDisconnected() { Department dep; //Disconnected Scenario using (EFContext db = new EFContext()) { Console.Clear(); db.Database.Log = Console.WriteLine; dep = db.Departments.Where(d => d.Name == "Purchase").First(); } dep.Descr = "Purchase Department-Disconnected Scenario"; using (EFContext db = new EFContext()) { db.Database.Log = Console.WriteLine; db.Entry(dep).State = System.Data.Entity.EntityState.Modified; //OR //db.Departments.Attach(dep); //db.Entry(dep).State = System.Data.Entity.EntityState.Modified; //OR //db.Departments.Add(dep); //db.Entry(dep).State = System.Data.Entity.EntityState.Modified; db.SaveChanges(); } Console.WriteLine("Department {0} ({1}) is Updated ", dep.Name, dep.DepartmentID); Console.ReadKey(); } |
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 5 | UPDATE [dbo].[Departments] SET [Name] = @0, [Descr] = @1 WHERE ([DepartmentID] = @2) |
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 10 | //Attach the entity and change its state db.Departments.Attach(dep); db.Entry(dep).State = System.Data.Entity.EntityState.Modified; OR //Add the Entity and change its state db.Departments.Add(dep); db.Entry(dep).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 15 16 17 18 19 20 21 22 23 24 25 26 27 | private void updateDepartment() { updateDepartment1(2, "Purchase Department"); } private void updateDepartment1(int id, string descr) { Department dep = new Department(); dep.DepartmentID = id; dep.Descr = descr; using (EFContext db = new EFContext()) { db.Database.Log = Console.WriteLine; db.Entry(dep).State = System.Data.Entity.EntityState.Modified; db.SaveChanges(); //NULL Value is inserted into the name field. } Console.WriteLine("Department {0} ({1}) is Updated ", dep.Name, dep.DepartmentID); Console.ReadKey(); } |
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 13 14 15 16 17 18 19 20 21 | private void updateDepartment2(int id, string descr) { using (EFContext db = new EFContext()) { db.Database.Log = Console.WriteLine; Department dep = db.Departments.Where(f => f.DepartmentID == id).FirstOrDefault(); if (dep == null) throw new Exception(""); dep.Descr = descr; db.SaveChanges(); } Console.WriteLine("Department {0} ({1}) is Updated ", dep.Name, dep.DepartmentID); Console.ReadKey(); } |
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 22 23 24 25 26 27 28 29 30 31 32 | private void UpdateMultipleRecords() { List<Department> deps = new List<Department>(); deps.Add(new Department { DepartmentID = 1, Descr = "Sales" }); deps.Add(new Department { DepartmentID = 2, Descr = "Purchase" }); deps.Add(new Department { DepartmentID = 3, Descr = "HR" }); using (EFContext db = new EFContext()) { db.Database.Log = Console.WriteLine; foreach (var item in deps) { var dept = db.Departments.Where(f => f.DepartmentID == item.DepartmentID).FirstOrDefault(); if (dept == null) throw new Exception(""); dept.Descr = item.Descr; } db.SaveChanges(); } Console.WriteLine("Records Updated "); Console.ReadKey(); } |
References
Read More
thank u
Thank you
Tutorial is good but no proper indexing. There should be side bar or something to check what all points are there in the tutorial.
It’s like maze once you enter tutorial
THANK YOU