I am sure that a developer’s life is full of “aha moments” or epiphanies. Somehow your thinking process gets stuck on something and you are convinced that that particular process works in a certain way, then the code breaks and you hit a wall, but after a good dose of re-education, some “stackoverflow” and/or Googling  the “Ah-ha!!!” moment happens.
Well, I thought that documenting some of those moments would help me to remember and hopefully help you to find your answers faster. So, here I am with the first post of the “Ah-ha Moment” series.

---

While working on this particular application, I needed a method to move an object from a section to another. Here is what I created:

public void MoveItemToItinerary(OptionalDetail optDetail, Guid DestinationMasterId, EMSDataModelContainer db)
{
   ItineraryDetail itiDetail = db.ItineraryDetails.Create();
   itiDetail.ItemID = optDetail.ItemID;
   itiDetail.TypeId = optDetail.TypeId;
   itiDetail.OrderId = db.ItineraryDetails.Where(d => d.ItineraryMasterId == DestinationMasterId).Max(o => o.OrderId).GetValueOrDefault() + 1;
   Guid ItiDetId = Guid.NewGuid();
   itiDetail.ItineraryDetailId = ItiDetId;
   //Removed for brevity

   db.ItineraryDetails.Add(itiDetail);
   //Some other operation not important in this case
}

The object needs to be added at the bottom of the “ItineraryDetails” collection, to do so I needed to add 1 to the highest OrderId in the collection (OrderId is an Int).  To get the highest number I used the EF method Max with the fluent API.
Now, this works just fine, I was happy and committed the code for production.  Then I get a call from one of the users saying that the application was messing up the items order.  Why??

Turns out that this method can also be called within a loop, in case multiple items are moved at once.  And I noticed that in that case the Max OrderId number kept on being the same in all iterations;  therefore, all added items got the same OrderId!!?? Problem.

Why was that happening?? After some Googling and some Julie Lerman @julielerman (see her Pluralsite courses here and here) I realized that the SaveChanges() of the DBcontext was only called at the end of the iteration so each newly added item was not persisted to the DB.  That is a problem since the call for the Max OrderId is actually checking the DB and not the entities in memory.
And there is my “Aha moment”…..I completely disregard the newly added entities not yet persisted to the DB.  I needed to make sure that the call to Max would include such entities. EF comes to the rescue with a nice keyword: “Local”. If I include this in the call to Max EF will check the memory copy of the collection and not the DB:

itiDetail.OrderId = db.ItineraryDetails.Local.Where(d => d.ItineraryMasterId == DestinationMasterId)
.Max(o => o.OrderId).GetValueOrDefault() + 1;

Great! Let’s run the app and see if it works…….and it doesn’t. With this change the first iteration will return 0 + 1 which is not correct if the actual collection already has items in the DB.

This makes total sense as now we are only checking the local copy of this collection. Well, then I need to load in memory the collection before checking the Max OrderId. To do this I just need to add the following line of code with the beautiful “.Load” method :

db.ItineraryDetails.Where(d => d.ItineraryMasterId == DestinationMasterId).Load();

EF is smart enough to only load the entities that are not already in memory, so in the case where my method is called within a loop, I don’t risk to overwrite any entities.

Now it works perfectly, EF loads the collection from the DB and the call to Max is local so also the newly added entities are accounted for, even before calling SaveChanges(). Here is the final code:

public void MoveItemToItinerary(OptionalDetail optDetail, Guid DestinationMasterId, EMSDataModelContainer db)
{
   ItineraryDetail itiDetail = db.ItineraryDetails.Create();
   itiDetail.ItemID = optDetail.ItemID;
   itiDetail.TypeId = optDetail.TypeId;
   db.ItineraryDetails.Where(d => d.ItineraryMasterId == DestinationMasterId).Load();
   itiDetail.OrderId = db.ItineraryDetails.Local.Where(d => d.ItineraryMasterId == DestinationMasterId)
.Max(o => o.OrderId).GetValueOrDefault() + 1;

   Guid ItiDetId = Guid.NewGuid();
   itiDetail.ItineraryDetailId = ItiDetId;
   //Removed for brevity

   db.ItineraryDetails.Add(itiDetail);
   //Removed for brevity
}

So, my Ah-ha Moment this time was, “Make sure you load the DB entities in memory before running any logic that utilizes data from the whole collection”.