Entity Framework update entity | Entity relation

You may have run to errors While update or add an entity in Entity Framework like this one: “The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship…”. Updating Many-to-one and many-to-many entity relations could be really tricky and sometimes pissing especially for beginner and inexperienced programmers. I really couldn’t find an article explaining bits and bobs of related entities update even though there are tons of issues in stackoverflow. I decided to explain all the aspects regarding update of Many-to-one and Many-to-Many entity relations.

Ok, let’s first talk about the scenarios you run to problem while updating. As long as you stick to simple forms, updating a Many-to-one relationship form one side, you are good to go. You simply set the foreign key value and that’s it. But what about updating a Many-to-one relationship from “many side”? To clarify what I meant consider you’re developing a form for a hotel reservation and one of the inputs is the info details of guests (Figure 1). As you can see, you can add as many as travelers you want in this form. In another panel or tab you want the user to select the services he wants from a List Box(Figure 2). After the user presses the submit button the passengers’ info for this reservation should be saved in Database. Entity classes are like this (to just make the point I don’t go to other details of these classes, just required fields to make the point):

    public class Guest

    {

        public int Id { get; set; }

        public string FirstName { get; set; }

        public string SurName { get; set; }

        public DateTime DateOfBirth { get; set; }

        public byte Gender { get; set; }

        public int ReservationId { get; set; }

        public Reservation Reservation { get; set; }

    }

    public class Reservation

    {

        public int Id { get; set; }

        public virtual ICollection<Guest> Guests { get; set; }

        public virtual ICollection<HotelServices> Services { get; set; }

    }

    public class HotelServices

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public string Description { get; set; }

        public ICollection<Reservation> Reservations { get; set; }

    }

}

 

Whatever User Interface you have at the end you want to save Reservation object. As you can see Reservation has a Many-to-one relation with the Guest class and a Many-to-Many relationship with the HotelServices class. I deliberately made such relationships to teach the details of both types of relationship at the same time so you can see the differences.

Entity relation

Figure 1

Entity Framework update entity

Figure 2

Add (Create) Related Entities in entity framework

Although the Add and Edit forms are similar the process of persisting object in DB significantly differs. First I start with the “Add”.  You prepare the Reservation object for save and just before persisting you have a Reservation object with Data like this(I use an unit-testing class to clearly the point):

        TransactionScope _trans;

        private ContextDB context;

        HotelServices _service1;

        HotelServices _service2;

        HotelServices _service3;

        private int servicesCount;

 

        [TestInitialize()]

        public void Initialize()

        {

            _trans = new TransactionScope();

            context = new ContextDB();

            _service1 = new HotelServices()

            {

                Name = "Pool and Massage",

                Description = "Description for Pool and Massage service",

                Id = 1

            };

 

            _service2 = new HotelServices()

            {

                Name = "24 hours Food And Drink",

                Description = "Description for 24 hours Food And Drink service",

                Id = 2

            };

 

            _service3 = new HotelServices()

            {

                Name = "Cruise Excursions",

                Description = "Description for Cruise Excursions service",

                Id = 3

            };

            context.HotelServices.Add(_service1);

            context.HotelServices.Add(_service2);

            context.HotelServices.Add(_service3);

            context.SaveChanges();

            servicesCount = context.HotelServices.Count();

        }

 

        [TestMethod]

        public void InsertAndUpdate()

        {

            #region Add

 

            var reservation = new Reservation()

            {

                Guests =

                    new List<Guest>()

                    {

                        new Guest()

                        {

                            FirstName = "John",

                            SurName = "Smith",

                            DateOfBirth = new DateTime(1989, 12, 6),

                            Gender = 1

                        },

                        new Guest()

                        {

                            FirstName = "Merry",

                            SurName = "Smith",

                            DateOfBirth = new DateTime(1991, 12, 6),

                            Gender = 2

                        },

                        new Guest()

                        {

                            FirstName = "Lili",

                            SurName = "Smith",

                            DateOfBirth = new DateTime(2009, 12, 6),

                            Gender = 2

                        }

                    },

                Services = new List<HotelServices>() {_service1, _service3}

            };

            context.Reservations.Add(reservation);

            context.SaveChanges();

 

        }

 

            #endregion

 

        [TestCleanup()]

        public void CleanUp()

        {

            _trans.Dispose();

        }

 

As you may notice I saved the services in “Initialization” method before test to show that services have been previously defined and loaded in the form whereas Guests' data are taken from the user (i.e they need to be added). This is important because for services we just need to set the relationship (fill the middle table) but for guests we need to add and set the relationship at the same time. Entity framework automatically does the first task when it sees your object is filled with services which are in DataContext BUT you should assist EF to do so. You need to load the services in your context(in this example because Context has been defined global and initialization is just before the test method services are already in Datacontext). The easiest way to this is to read the service before Reservation adding and pass their object references to the model. For example one line of code like this:

            var services=context. HotelServices.ToList();

If you do not do so, EF just insert another service in DB ignoring the service with the same Id in DB.  For Guests you need to do nothing! Just filling the Guest Collection of Reservation is enough. Ok now we need to test several things. First we should have newly added reservation in the Service collection. Second we should have the guests having the newly added reservation Id. And finally we should ensure that no more services have been added to the DB.

The unit test Assert section is as follows:

            // Check to see if the a relationship has been successfully set between service and reservation

            var readedService1InDb =

                context.HotelServices.Include("Reservations").FirstOrDefault(s => s.Id == _service1.Id);

            Assert.IsTrue(readedService1InDb.Reservations.Any(r => r.Id == reservation.Id));

 

            var readedService3InDb =

                context.HotelServices.Include("Reservations").FirstOrDefault(s => s.Id == _service3.Id);

            Assert.IsTrue(readedService3InDb.Reservations.Any(r => r.Id == reservation.Id));

            //Check guests having the newly added reservation Id

            var addedGuest1 = reservation.Guests.ElementAt(0);

            var addedGuest2 = reservation.Guests.ElementAt(1);

            var addedGuest3 = reservation.Guests.ElementAt(2);

            var guest1 =

                context.Guests.FirstOrDefault(

                    g =>

                        g.FirstName == addedGuest1.FirstName &&

                        g.SurName == addedGuest1.SurName &&

                        g.Gender == addedGuest1.Gender);

 

            Assert.AreEqual(reservation.Id, guest1.ReservationId);

 

            var guest2 =

                context.Guests.FirstOrDefault(

                    g =>

                        g.FirstName == addedGuest2.FirstName &&

                        g.SurName == addedGuest2.SurName &&

                        g.Gender == addedGuest2.Gender);

 

            Assert.AreEqual(reservation.Id, guest2.ReservationId);

 

            var guest3 =

                context.Guests.FirstOrDefault(

                    g =>

                        g.FirstName == addedGuest3.FirstName &&

                        g.SurName == addedGuest3.SurName &&

                        g.Gender == addedGuest3.Gender);

 

            Assert.AreEqual(reservation.Id, guest3.ReservationId);

            //Check to see no more services have been added

            Assert.AreEqual(servicesCount, context.HotelServices.Count());

 

Edit (Update) Related Entities in entity framework

As you have seen adding related entities especially in “One-To-Many” relation cases is not an obscure task; however updating related entities is tricky. The first time I wanted to update an entity’s list using Entity Framework, I expected EF to do everything. I simply passed new list of objects and from my point of view EF should have added, deleted or updated all the objects automatically. That’s not what EF do for you!

 When you want to build an “Edit” form you allow the user to select or input new values and you pass new values as a list to the object and you update the model. Well this seemingly “Update” operation is actually 3 operations. If you compare the previous list(the list created at “Add” operation) with the update list some objects have been deleted, some new objects are added and some have been modified. Thus you actually should Delete previous objects, Add new ones and Update mutual objects in both list. To do this you should find first which objects are deleted which ones are added and which ones are being modified. The code to find these three types for Guest objects is as follows:

 

 

           var readedReservation = context.Reservations.Include("Guests").Include("Services").FirstOrDefault(r => r.Id == reservation.Id);

            var editedReservation= new Reservation()

            {

                Guests =

                    new List<Guest>()

                    {

                        new Guest()

                        {

                            FirstName = "John",

                            SurName = "Smith",

                            DateOfBirth = new DateTime(1989, 12, 6),

                            Gender = 1,

                            Id = guest1.Id

                        },

                        new Guest()

                        {

                            FirstName = "Merry",

                            SurName = "Simpson",

                            DateOfBirth = new DateTime(1991, 12, 6),

                            Gender = 2,

                            Id = guest2.Id

                        },

                    },

                Services = new List<HotelServices>() { _service1,_service2}

            };

             //Finding Deleted objects

            var deletedIdsGuests =

                readedReservation.Guests.Select(g => g.Id)

                    .Except(editedReservation.Guests.Select(g => g.Id)).ToList();

            //Finding Added objects

            var addedIdsGuests =

                editedReservation.Guests.Select(g => g.Id)

                    .Except(readedReservation.Guests.Select(g => g.Id)).ToList();

 

            //Finding Edited Objects

            var editedIdsGuests =

                editedReservation.Guests.Select(g => g.Id)

                    .Intersect(readedReservation.Guests.Select(g => g.Id)).ToList();

 

To find out the state of previous objects(being deleted or edited) and the state of new objects(if added) you need to load the object from DB. Please pay attention to the include part, related objects being loaded with the object not only help us to find the state of related objects but also to help EF track the state of objects.

To find deleted Guests you must find those Guest Ids which are no more in new reservation Guest list. This means those objects have been deleted. Guest Ids which are only in new reservation list signal us that they have been added and the rest have been edited (those that exist in previous and new reservation Guest list). After dividing Previous and New Guest List’s objects to 3 groups of deleted, added and edited we should manipulate the Reservation object which is read from DB. This way when you remove, add or edit an object from the Guest list of Reservation object which is attached to DataContext, EF automatically updates the relation, Edit the object or add it. What EF does not do is when you remove a Guest from reservation list, EF does not remove the Guest object from DB. It just clears the Guest foreign key. It is a right behavior, since you may use Guest in other tables as well and you may want the Guest in DB. But when the foreign key of Guest is not nullable you will get and exception. To avoid this you either should make the foreign key nullable or delete the object. In Guest case of our example we do not need Guest object anywhere else and a guest without a relation to a reservation does not make sense so we delete it. In Many-To-Many relation(like the HotelServices in our example) EF behavior is different because it saves the relation in a middle-table and the object in that table has been inserted just to track the relation so without relation there should be no object in that table. This is the reason EF automatically deletes the removed object from reservation list and you do not to take any additional action. The complete code to update Guests and Services is as follows:

            #region Update

 

             var readedReservation = context.Reservations.Include("Guests").Include("Services").FirstOrDefault(r => r.Id == reservation.Id);

            var editedReservation= new Reservation()

            {

                Guests =

                    new List<Guest>()

                    {

                        new Guest()

                        {

                            FirstName = "John",

                            SurName = "Smith",

                            DateOfBirth = new DateTime(1989, 12, 6),

                            Gender = 1,

                            Id = guest1.Id

                        },

                        new Guest()

                        {

                            FirstName = "Merry",

                            SurName = "Simpson",

                            DateOfBirth = new DateTime(1991, 12, 6),

                            Gender = 2,

                            Id = guest2.Id

                        },

                    },

                Services = new List<HotelServices>() { _service1,_service2}

            };

 

 

            //Finding Deleted objects

            var deletedIds =

                readedReservation.Services.Select(sc => sc.Id)

                    .Except(editedReservation.Services.Select(sc => sc.Id)).ToList();

 

            //Finding Added objects

            var addedIds =

                editedReservation.Services.Select(sc => sc.Id)

                    .Except(readedReservation.Services.Select(sc => sc.Id)).ToList();

 

            //Unchanged are those who are in both objects

 

            //Deleting elements

            deletedIds.ForEach(di =>

                    readedReservation.Services.Remove(

                        readedReservation.Services.FirstOrDefault(i => i.Id == di)));

            //Adding elements

            addedIds.ForEach(ai => readedReservation.Services.Add(editedReservation.Services.FirstOrDefault(i => i.Id == ai)));

 

            //Finding Deleted objects

            var deletedIdsGuests =

                readedReservation.Guests.Select(g => g.Id)

                    .Except(editedReservation.Guests.Select(g => g.Id)).ToList();

            //Finding Added objects

            var addedIdsGuests =

                editedReservation.Guests.Select(g => g.Id)

                    .Except(readedReservation.Guests.Select(g => g.Id)).ToList();

 

            //Finding Edited Objects

            var editedIdsGuests =

                editedReservation.Guests.Select(g => g.Id)

                    .Intersect(readedReservation.Guests.Select(g => g.Id)).ToList();

 

            //Deleting elements

            deletedIdsGuests.ForEach(di =>

            {

                var deletingObject = readedReservation.Guests.ToList().FirstOrDefault(i => i.Id == di);

                context.Guests.Remove(deletingObject);

                readedReservation.Guests.Remove(deletingObject);

            }

                );

            //Adding elements

            addedIdsGuests.ForEach(

                ai =>

                    readedReservation.Guests.Add(

                        reservation.Guests.FirstOrDefault(i => i.Id == ai)));

 

            //Editing Elements

            editedIdsGuests.ForEach(ei =>

            {

                var guest = readedReservation.Guests.FirstOrDefault(od => od.Id == ei);

                var editedGuest = editedReservation.Guests.FirstOrDefault(od => od.Id == ei);

                guest.FirstName = editedGuest.FirstName;

                guest.SurName = editedGuest.SurName;

                guest.Gender = editedGuest.Gender;

            });

            context.Entry(readedReservation).State = EntityState.Modified;

            context.SaveChanges();

 

Now Let’s write a unit test to see if we achieved our goals. First, all the objects in the editedReservation Guest and HotelServices lists should be existed in the DB and have the same value (including Id for edited objects). Second the objects which are no more in editedReservation Guest and HotelServices list should not be in DB too:

            readedReservation = context.Reservations.Include("Guests").Include("Services").FirstOrDefault(r => r.Id == reservation.Id);

 

            /////////////// Services Assert //////////////

            var modifiedServices = editedReservation.Services.Select(s => s.Id).ToList();

            modifiedServices.ForEach(id =>

                        {

                            Assert.AreEqual(editedReservation.Services.FirstOrDefault(s => s.Id == id).Name, readedReservation.Services.FirstOrDefault(s => s.Id == id).Name);

                            if (!addedIds.Contains(id))

                            {

                                Assert.AreEqual(editedReservation.Services.FirstOrDefault(s => s.Id == id).Id, readedReservation.Services.FirstOrDefault(s => s.Id == id).Id);

                               

                            }

                        });

 

            deletedIds.ForEach(id=>Assert.IsFalse(readedReservation.Services.Any(r=>r.Id==id)));

            /////////////// Services Assert //////////////

 

            /////////////// Guests Assert //////////////

            var modifiedGuests = editedReservation.Guests.Select(s => s.Id).ToList();

            modifiedGuests.ForEach(id =>

            {

                Assert.AreEqual(editedReservation.Guests.FirstOrDefault(s => s.Id == id).FirstName, readedReservation.Guests.FirstOrDefault(s => s.Id == id).FirstName);

                Assert.AreEqual(editedReservation.Guests.FirstOrDefault(s => s.Id == id).SurName, readedReservation.Guests.FirstOrDefault(s => s.Id == id).SurName);

                Assert.AreEqual(editedReservation.Guests.FirstOrDefault(s => s.Id == id).Gender, readedReservation.Guests.FirstOrDefault(s => s.Id == id).Gender);

 

                if (!addedIds.Contains(id))

                {

                    Assert.AreEqual(editedReservation.Guests.FirstOrDefault(s => s.Id == id).Id, readedReservation.Guests.FirstOrDefault(s => s.Id == id).Id);

 

                }

            });

 

            deletedIdsGuests.ForEach(id => Assert.IsFalse(readedReservation.Guests.Any(r => r.Id == id)));

            /////////////// Guests Assert //////////////

 

Read 1611 times Last modified on Tuesday, 14 July 2015 13:28
Rate this item
0
(0 votes)
About Author
Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

Advanced Programming Concepts
News Letter

Subscribe our Email News Letter to get Instant Update at anytime