Code-First Entity Framework Can't update many to many related entities

Question

I have a many to many relationship between Tickets and Tags and can add tags to tickets without an issue. However I'm unable to update/remove tags from Tickets.

Test

[TestMethod]
        public void TagRepository_UpdateTicketWithTwoTagsToNone_TicketHasZeroTag()
        {
            // Arrange
            var ticketId = new Guid("54E86203-71F9-E411-80E5-000C29193DF7");
            var selectedTags = "";

            using (var context = new TicketModelContext())
            {
                using (new TransactionScope())
                {
                    var ticketToUpdate = context.Tickets.Include(t=>t.Tags).First(t => t.TicketId == ticketId);
                    Assert.AreEqual(0, ticketToUpdate.Tags.Count);

                    ticketToUpdate.Tags.Add(context.Tags.Find(new Guid("D1757675-A06C-4C1F-9DAD-03EE00BB1100")));
                    ticketToUpdate.Tags.Add(context.Tags.Find(new Guid("96C66A97-9C3E-4B15-BD70-A4C832EEDE8B")));
                    context.SaveChanges();

                    var setupTicket = context.Tickets.Single(t => t.TicketId == ticketId);
                    Assert.AreEqual(2, setupTicket.Tags.Count);


                    // Act
                    new TagRepository().UpdateTicketTags(ticketId, selectedTags);

                    // Assert
                    var updatedTeicket = context.Tickets.Include(t => t.Tags).First(t => t.TicketId == ticketId);
                    Assert.AreEqual(0, updatedTeicket.Tags.Count);
                    // HERE I EXPECT 0 BUT GET 2
                }
            }
        }

Repository Method

public void UpdateTicketTags(Guid ticketId, string selectedTags)
        {
            var tags = new List<Tag>();

            using (var context = new TicketModelContext())
            {
                if (!String.IsNullOrEmpty(selectedTags))
                {
                    foreach (var selectedTag in selectedTags.Split(','))
                    {
                        tags.Add(context.Tags.Find(new Guid(selectedTag)));
                    }
                }

                var ticketToUpdateTags = context.Tickets.Find(ticketId);

                context.Entry(ticketToUpdateTags).Collection(t => t.Tags).Load();

                ticketToUpdateTags.Tags = tags; // I EXPECT TAGS TO BE EMPTY IN THIS TEST
                context.SaveChanges();
            }
        }

SQL outputted by command context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

DELETE [dbo].[TagTickets]
WHERE (([Tag_TagId] = @0) AND ([Ticket_TicketId] = @1))


-- @0: '96c66a97-9c3e-4b15-bd70-a4c832eede8b' (Type = Guid)

-- @1: '54e86203-71f9-e411-80e5-000c29193df7' (Type = Guid)

-- Executing at 26/05/2015 13:16:39 +01:00

-- Completed in 24 ms with result: 1



DELETE [dbo].[TagTickets]
WHERE (([Tag_TagId] = @0) AND ([Ticket_TicketId] = @1))


-- @0: 'd1757675-a06c-4c1f-9dad-03ee00bb1100' (Type = Guid)

-- @1: '54e86203-71f9-e411-80e5-000c29193df7' (Type = Guid)

-- Executing at 26/05/2015 13:16:39 +01:00

-- Completed in 21 ms with result: 1

I have spent hours trying different variations of code with different tests and all have the same outcome. Any help would be much appreciated!

Answer 1

Basically the reason why your test wasn't working is because changes that occur over different contexts in Entity Framework are not reflected. Even though you used a TransactionScope, if you call SaveChanges() on inner context (happening in your UpdateTicketTags()), then those changes will not be reflected on the outer context.

I usually prefer breaking actions down into smaller chunks instead of one long context, so that

• it simulates how real world actions can occur in your application (e.g. if this is an ASP.NET MVC application, multiple controllers are processing multiple requests that may or may not be modifying the same table) and

• it gets me the most recent information for each object.

  [TestMethod]
  public void TagRepository_UpdateTicketWithTwoTagsToNone_TicketHasZeroTag()
  {
      // Arrange
      var ticketId = new Guid("54E86203-71F9-E411-80E5-000C29193DF7");
      var selectedTags = "";
  
      using (var context = new TicketModelContext())
      {
          var ticketToUpdate = context.Tickets.Include(t=>t.Tags).First(t => t.TicketId == ticketId);
          Assert.AreEqual(0, ticketToUpdate.Tags.Count);
  
          ticketToUpdate.Tags.Add(context.Tags.Find(new Guid("D1757675-A06C-4C1F-9DAD-03EE00BB1100")));
          ticketToUpdate.Tags.Add(context.Tags.Find(new Guid("96C66A97-9C3E-4B15-BD70-A4C832EEDE8B")));
          context.SaveChanges();
  
          var setupTicket = context.Tickets.Single(t => t.TicketId == ticketId);
          Assert.AreEqual(2, setupTicket.Tags.Count);
      }
  
      // Act
      new TagRepository().UpdateTicketTags(ticketId, selectedTags);
  
      using (var context = new TicketModelContext())
      {
          // Assert
          var updatedTeicket = context.Tickets.Include(t => t.Tags).First(t => t.TicketId == ticketId);
          Assert.AreEqual(0, updatedTeicket.Tags.Count);
      }
  }
  

Answer 2

Hmm I'm not sure what kind of error you are getting, but I would guess you have set MultipleActiveResultSets as false in web.config. Try adding this to web.config MultipleActiveResultSets=True to your connection string. That will enable EF to query while retrieving data etc. From my point of view your code looks fine.

"Multiple Active Result Sets (MARS) is a feature that works with SQL Server to allow the execution of multiple batches on a single connection." https://msdn.microsoft.com/en-us/library/h32h3abf%28v=vs.110%29.aspx

Answer 3

I have re-structured my test code to remove a repository call whilst inside a database context and moved my transaction scope outside to achieve what I needed. The main learning point is don't try and make changes to the database inside a context by calling another context.

[TestMethod]
        public void TagRepository_UpdateTicketWithTwoTagsToNone_TicketHasZeroTag()
        {
            // Arrange
            var ticketId = new Guid("54E86203-71F9-E411-80E5-000C29193DF7");
            var selectedTags = "";

            using (new TransactionScope())
            {
                using (var context = new TicketModelContext())
                {

                    var ticketToUpdate = context.Tickets.Include(t => t.Tags).First(t => t.TicketId == ticketId);
                    Assert.AreEqual(0, ticketToUpdate.Tags.Count);

                    ticketToUpdate.Tags.Add(context.Tags.Find(new Guid("D1757675-A06C-4C1F-9DAD-03EE00BB1100")));
                    ticketToUpdate.Tags.Add(context.Tags.Find(new Guid("96C66A97-9C3E-4B15-BD70-A4C832EEDE8B")));
                    context.SaveChanges();

                    var setupTicket = context.Tickets.Single(t => t.TicketId == ticketId);
                    Assert.AreEqual(2, setupTicket.Tags.Count);
                }

                // Act
                new TagRepository().UpdateTicketTags(ticketId, selectedTags);

                // Assert
                using (var context2 = new TicketModelContext())
                {
                    var updatedTeicket = context2.Tickets.Include(t => t.Tags).First(t => t.TicketId == ticketId);
                    Assert.AreEqual(0, updatedTeicket.Tags.Count);
                }
            }
        }