Updating a database record using the Entity Framework (lazy loading and virtual properties)

Question

I am struggling with updating existing lecturer data in a database.

Each teacher has a Name , AcademicDegree and the Courses that are taught to them (Courses == Lessons).

There are actually more properties in class Lecturer , but they are not relevant. For simplicity, suppose the POCO class is defined as follows:

 // POCO class (Entity Framework Reverse Engineering Code First) public class Lecturer { public Lecturer() { this.Courses = new List<Course>(); } public int Id_Lecturer { get; set; } // Primary Key public string Name { get; set; } public int? Academic_Degree_Id { get; set; } public virtual AcademicDegree AcademicDegree { get; set; } public virtual ICollection<Course> Courses { get; set; } } 

In the data access layer, I have a void UpdateLecturer(Lecturer lecturer) method that should update the lecturer whose Id_Lecturer is equal to lecturer.Id_Lecturer (using lecturer.Name , lecturer.AcademicDegree and lecturer.Courses ).

This is a very convenient method, because in ViewModel I can call _dataAccess.UpdateLecturer(SelectedLecturer) (where SelectedLecturer bound in XAML ; SelectedLecturer properties can be set by the user in TextBox es and Checkbox ).

Unfortunately, this method:

  public void UpdateLecturer(Lecturer lecturer) { using(var db = new AcademicTimetableDbContext()) { // Find lecturer with Primary Key set to 'lecturer.Id_Lecturer': var lect = db.Lecturers.Find(lecturer.Id_Lecturer); // If not found: if (lect == null) return; // Copy all possible properties: db.Entry(lect).CurrentValues.SetValues(lecturer); // Everything was copied except 'Courses'. Why?! // I tried to add this, but it doesn't help: // var stateMgr = (db as IObjectContextAdapter).ObjectContext.ObjectStateManager; // var stateEntry = stateMgr.GetObjectStateEntry(lect); // stateEntry.SetModified(); db.SaveChanges(); } } 

updates everything ( Id_Lecturer , Name , Academic_Degree_Id and AcademicDegree ) except Courses , which do not change after db.SaveChanges() .

Why? How can i fix this?

---

Similar issues:

• updating-an-entity-in-entity-framework
• how-do-can-i-attach-a-entity-framework-object-that-isnt-from-the-database
• using-dbcontext-in-ef-feature-ctp5-part-4-add-attach-and-entity-states.aspx
• entity-framework-updating-with-related-entity
• entity-framework-code-first-no-detach-method-on-dbcontext

---

- Change -

I also tried this way (idea came from this post ):

 public void UpdateLecturer(Lecturer lecturer) { using (var db = new AcademicTimetableDbContext()) { if (lecturer == null) return; DbEntityEntry<Lecturer> entry = db.Entry(lecturer); if (entry.State == EntityState.Detached) { Lecturer attachedEntity = db.Set<Lecturer>().Find(lecturer.Id_Lecturer); if (attachedEntity == null) entry.State = EntityState.Modified; else db.Entry(attachedEntity).CurrentValues.SetValues(lecturer); } db.SaveChanges(); } } 

but still, courses do not overwrite old values.

---

- Change 2 -

In response to a @Slauma question, I will describe how I loaded SelectedLecturer (which is passed to UpdateLecturer(Lecturer lecturer) as an argument).

I am implementing the MVVM concept, so I have a View project in my solution with the DataContext set to LecturerListViewModel . The view has a DataGrid with a list of all the lecturers extracted from the database. DataGrid bound as follows:

 <DataGrid AutoGenerateColumns="False" Name="LecturersDataGrid" HeadersVisibility="Column" IsReadOnly="True" ItemsSource="{Binding Lecturers,Mode=TwoWay}" SelectedItem="{Binding SelectedLecturer, Mode=TwoWay}"> <DataGrid.Columns> <DataGridTextColumn Header="Name" Binding="{Binding Name}" /> <DataGridTextColumn Header="Academic degree" Binding="{Binding AcademicDegree.Degree_Name}" /> <DataGridTemplateColumn> <DataGridTemplateColumn.CellTemplate> <DataTemplate> <StackPanel Orientation="Horizontal"> <Button Content="Edit" Click="EditButtonClick"/> <Button Content="Delete" Command="{Binding DataContext.RemoveLecturer, ElementName=LecturersDataGrid}" /> </StackPanel> </DataTemplate> </DataGridTemplateColumn.CellTemplate> </DataGridTemplateColumn> </DataGrid.Columns> </DataGrid> 

Lecturers are retrieved from the database in the LecturerListViewModel constructor as follows:

 /// /// Code within LecturerListViewModel class: /// // All lecturers from database. public ObservableCollection<Lecturer> Lecturers // Constructor public LecturerListViewModel() { // Call to Data Access Layer: Lecturers = new ObservableCollection<Lecturer>(_dataAccess.GetAllLecturers()); // Some other stuff here... } private Lecturer _selectedLecturer; // Currently selected row with lecturer. public Lecturer SelectedLecturer { get { return _selectedLecturer; } set { SetProperty(out _selectedLecturer, value, x => x.SelectedLecturer); } } /// /// Data Access Layer (within DataAccess class): /// public IEnumerable<Lecturer> GetAllLecturers() { using (var dbb = new AcademicTimetableDbContext()) { var query = from b in dbb.Lecturers.Include(l => l.AcademicDegree).Include(l => l.Timetables).Include(l => l.Courses) select b; return query.ToList(); } } 

Answer 1

Setting the state in Modified and SetValues updates only the scalar properties of the Lecturer object. Updating the Courses collection (which is not a scalar property) requires additional work. You must handle cases where the course could be removed from the collection, the course could have been added, or the scalar properties of the course could have been changed.

Also, the method of updating the collection depends on which course depends on the teacher or not. Do I need to delete the course from the database when it was deleted from the collection, or do I need to delete only the relationship between the lecturer and the course? Do I need to create a new course when it was added to the collection, or do I only need to establish relationships?

If no courses are to be deleted and new courses are not created, the Update method may look like this:

 public void UpdateLecturer(Lecturer lecturer) { using(var db = new AcademicTimetableDbContext()) { if (lecturer == null) return; var lecturerInDb = db.Lecturers .Include(l => l.Courses) .Single(l => l.Id_Lecturer == lecturer.Id_Lecturer); // Update lecturer db.Entry(lecturerInDb).CurrentValues.SetValues(lecturer); // Remove courses relationships foreach (var courseInDb in lecturerInDb.Courses.ToList()) if (!lecturer.Courses.Any(c => c.Id_Course == courseInDb.Id_Course)) lecturerInDb.Courses.Remove(courseInDb); foreach (var course in lecturer.Courses) { var courseInDb = lecturerInDb.Courses.SingleOrDefault( c => c.Id_Course == course.Id_Course); if (courseInDb != null) // Update courses db.Entry(courseInDb).CurrentValues.SetValues(course); else { // Add courses relationships db.Courses.Attach(course); lecturerInDb.Courses.Add(course); } } } db.SaveChanges(); } 

Depending on the details of your scenario, the correct solution may be slightly different.

Edit

If the courses in the lecturer.Courses collection have a link to Lecturer (it may have the Lecturer navigation property), you may have problems if you attach a course from this collection to the context, because lecturerInDb already attached and has the same key. You can try changing the last else block to hopefully solve the problem:

  else { // Add courses relationships var courseToAttach = new Course { Id_Course = course.Id_Course }; db.Courses.Attach(courseToAttach); lecturerInDb.Courses.Add(courseToAttach); }