none
Delete related child records before inserting RRS feed

  • Question

  • I have a Farm entity, a Specie entity and a link table with foreign keys FarmID and SpecieID. This link table is not available in EF since it just creates a collection on each side of the relationship. 

    There is a multiselect in the MVC view which allows the customer to select multiple species for this farm.

    I want to delete all the related records in the link table before I insert the correct species selected by the user.

    There are updates to related parent records that need to be performed and I can updates those without problems.

    The context doesn't know about the previously related species since this is a web application so there is no posted species except for the newly selected ones. 

    The issue I have is that I since the context is tracking the entities, when I try to delete those old child records, SaveChanges and then try to work with the Farm entity, EF complains.

    "Attaching entity Farm failed because another entity of the same type already has the same primary key value."

    I have tried detaching, setting entity state etc. and still no joy. It seems one way to do this is to create a new context after deleting the child records but there must be a recommended way to do this in the same context. 

    Monday, December 5, 2016 10:06 PM

All replies

  • Hi forwheele,

    Based on your description, it seems that it is a many-to-many relationship update, please refer the following code.

    using (var dbCtx = new SchoolDBEntities())
        {
            /* 1- Get existing data from database */
            var existingStudent = dbCtx.Students.Include("Courses")
                    .Where(s => s.StudentName == stud.StudentName).FirstOrDefault<Student>();
    
            /* 2- Find deleted courses from student's course collection by 
            students' existing courses (existing data from database) minus students' 
            current course list (came from client in disconnected scenario) */
            var deletedCourses = existingStudent.Courses.Except(stud.Courses, 
                    cours => cours.CourseId).ToList<Course>();
    
            /* 3- Find Added courses in student's course collection by students' 
            current course list (came from client in disconnected scenario) minus 
            students' existing courses (existing data from database)  */
            var addedCourses = stud.Courses.Except(existingStudent.Courses, 
                    cours => cours.CourseId).ToList<Course>();
    
            /* 4- Remove deleted courses from students' existing course collection 
            (existing data from database)*/
            deletedCourses.ForEach(c => existingStudent.Courses.Remove(c));
                    
            //5- Add new courses
            foreach(Course c in addedCourses)
            {
                /*6- Attach courses because it came from client 
                as detached state in disconnected scenario*/
                if (dbCtx.Entry(c).State == System.Data.EntityState.Detached)
                    dbCtx.Courses.Attach(c);
    
                //7- Add course in existing student's course collection
                existingStudent.Courses.Add(c);
            }
    
            //8- Save changes which will reflect in StudentCourse table only
            dbCtx.SaveChanges();
        }
           

    For more information, please refer to:

    http://www.entityframeworktutorial.net/EntityFramework4.3/update-many-to-many-entity-using-dbcontext.aspx

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, December 6, 2016 2:32 AM
    Moderator
  • Thanks for the reply.

    I still get the same error when I try to attach the specie; "Attaching an entity of type 'Models.Species' failed because another entity of the same type already has the same primary key value." 

    The entities are setup as follows:

    Properties has many Facilities has many Farms has many Species. Farms and Species have a link table with FarmID and SpecieID.

     var existingProperty = _db.Properties
                        .Include(x => x.Facilities).Where(s => s.PropertyID == property.PropertyID)
                        .Include(f => f.Facilities.Select(ff => ff.Farms.Select(s => s.Species))).FirstOrDefault();
                
                    foreach (var existingFacility in existingProperty.Facilities)
                    {
                        foreach (var existingFarm in existingFacility.Farms)
                        {
                            var farmFromView =
                            (from f in property.Facilities
                             from ff in f.Farms
                             where ff.FarmsID == existingFarm.FarmID
                             select ff).SingleOrDefault();
    
                            var existingSpecies = new List<Species>();
                            foreach (var specieID in farmFromView.SelectedSpecieIDs)
                            {
                                int intSpecieID = Convert.ToInt32(specieID);
                                existingSpecies.Add(_db.Species.AsNoTracking().SingleOrDefault(x => x.SpeciesID == intSpecieID));
                            }
    
                            var speciesToDelete = existingFarm.Species.Except(existingSpecies).ToList();
                            var speciesToAdd = existingSpecies.Except(existingFarm.Species).ToList();
                            speciesToDelete.ForEach(s => existingFarm.Species.Remove(s));
    
                            foreach (var c in speciesToAdd)
                            {
                                if (_db.Entry(c).State == EntityState.Detached)
                                {
                                    //_db.Species.Attach(c);//BOMBS HERE
                                    // existingFarm.Species.Add(c);
                                }
                            }
    
                        }
                    }
    
                    _db.SaveChanges();

    Tuesday, December 6, 2016 6:35 PM
  • I ended up with this so thanks for your help.

      Farm existingFarmFromDb = null;
                    foreach (var facility in property.Facilities)
                    {
                        foreach (var farm in facility.Farms)
                        {
                            //get the existing record from the database
                            existingFarmFromDb = _db.Farms.Where(x => x.FarmID == Farm.FarmID).Include(x => x.Species).FirstOrDefault();
                            if (existingFarmFromDb == null)
                            {
                                continue;
                            }
    
                            //Species Work
                            //loop the selectedIDs property containing specie ids if any, get the specie from the db and add them to Farm.Species
                            if (farm.SelectedSpecieIDs != null && farm.SelectedSpecieIDs.Count > 0)
                            {
                                var specieIds = farm.SelectedSpecieIDs.Select(int.Parse).ToList(); //convert ids to ints
                                var species = _db.Species.Where(x => specieIds.Contains(x.SpeciesID)).ToList();
                                foreach (var specie in species)
                                {
                                    farm.Species.Add(specie);
                                }
                            }
    
                            //compare the species in the db version of the farm and the one from the view and get a list to delete or add
                            var speciesToDelete = existingFarmFromDb.Species.Except(farm.Species).ToList();
                            var speciesToAdd = farm.Species.Except(existingFarmFromDb.Species).ToList();
    
                            //delete species if needed
                            speciesToDelete.ForEach(s => existingFarmFromDb.Species.Remove(s));
    
                            //add new species if needed
                            speciesToAdd.ForEach(s => existingFarmFromDb.Species.Add(s));
                            //End Species Work
    
                            //tell context to update values
                            _db.Entry(existingFarmFromDb).CurrentValues.SetValues(Farm);
                        }
                        _db.Entry(existingFarmFromDb?.Facility).CurrentValues.SetValues(facility);
                        _db.Entry(existingFarmFromDb?.Facility.Contact).CurrentValues.SetValues(facility.Contact);
                    }
                    _db.Entry(property.Contact).State = EntityState.Modified;
                    _db.SaveChanges();

    Thursday, December 8, 2016 10:05 PM
  • Hi forwheeler,

    I am glad to know you solved this problem and thanks for sharing the solution. Please mark it as answer, it will be very beneficial for other community members who have the similar questions.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 19, 2016 7:23 AM
    Moderator