confusing issues with EF Core

Working with ORMs has been a such a life saver for the developers , It reduces their dependency on SQL knowledge and DB administrators , It helps developer be more productive thanks to the easiness of handling DB operations.
It even limits a number of security vulnerabilities that would cause them headaches if logic implemented with SQL.
Also without ORMS, it could be such a painful task to maintain SQL scripts , indexes, stored procedures especially in this era of continuous integration and delivery.
But for which cost do reap all these benefits. In this article , I will go through few of the dark sides of using ORM, my subject for this article is EF Core, which is the ORM I am using in my current project. For that I will go through some of the ambiguous bugs the I faced lately.

Data may have been modified or deleted since entities were loaded

This issue was quiet interesting for me , since we were early on setting up the basic boilerplate code for our modules , and we were building the initial entities , setting up their Crud operations , and while working on the update endpoint of some entities , I encountered this issue . Seemed weird to me at first because am the only one working on this testing data and it's already working only locally, so no way to have the data modified/deleted by another contributor. in the message error on visual studio output , along with the exception there was a link to Microsoft documentation about handling concurrency .
That link wasn't so helpful , because it even made the situation more puzzled. As mentioned in the beginning , we were only setting up the basic CRUD operations ,and we haven't thought nor planned how we are going to deal with concurrency conflicts.
The missed puzzle in this situation is that we were using a Framework name ABP Framework, that would facilitate generating entities , basic logic as well as setting up the general modules structure according to the DDD principles.
What I have missed in this situation was that the entities inherit by default from an AggregateRoot class of the ABP framework , this AggregateRoot class itself implements IHasConcurrencyStamp , which made it obligatory to handle cncurrency stamp conflicts , even if we didn't consider that yet. The issues happens mainly in the following scenario :

• We fetch an item from DB with its ID and its specific ConcurrencyStamp ( which was defined by the Framework we adopting for this project , will give it the value XXX)

• The Update Endpoint of the API takes as parameters the Item Id (Guid type) as well as the ItemUpdateDto, the latter was also generated by the ABP framework and inherits from the AggregateRootDto and have ConcurrecnyStamp property too.

• When the call reaches The API update endpoint with the ID and the EntityUpdateDto , the Update loginc goes as below ;

  • Fetch the item from Db using Id provided ( which brings the item with a new specific Concurrency Stamp YYY)
  • Map the ItemUpdateDto to the newly fetched item , so now the newly fetched item has the properties updated as well as the ConcurrencyStamp being updated as well , so now its ConcurrencyStamp is XXX
  • Save the modified Item to DB

Trying to save the Item to DB using EF Core is no longer possible , because the item when fetched , it had the ConcurrencyStamp of YYY , but now we want to save it with a different one ,wich doesn't make sense for EF Core.

In EF Core , for an entity to be modified and saved it has to keep the same concurrencyStamp throughout the whole process , but in my case , it was changed on the mapping step, which caused this issue What made the situation even more delicate , is the fact that those concurrency Stamps causing this issue weren't intentionally added to to my classes neither they were considered when investigating this issue .

The solution :

The solution that I opted for in this situation is to ignore the concurrency stamp property when mapping the ItemUpdateDto to the newly fetched Item at the step just before saving it with the new modifications.
Following the mapping configuration I put in place dor mapping ItempUpdateDto to a concrete Item:

CreateMap<ItemUpdateDto, Item>()
            .IgnoreAuditedObjectProperties()
            .Ignore(c => c.IsDeleted)
            .Ignore(c => c.DeleterId)
            .Ignore(c => c.DeletionTime)
            .Ignore(c => c.ExtraProperties)
            .Ignore(c => c.ConcurrencyStamp)

Below is the code for the Update endpoint ;

public virtual async Task<ItemDto> UpdateAsync(Guid id, 
        ItemUpdateDto input)
 {
      var item = await _itemRepository.GetAsync(id);
      ObjectMapper.Map(input, item);
      var returnItem = await _itemManager.UpdateAsync(id, item);
      return ObjectMapper.Map<Item, ItemDto>(returnItem);
}

Database operation expected to affect 1 row(s) but actually affected 0 row(s)

With this one ,the exception message also provided a link to official documentation about handling optimistic concurrency exceptions , but I checked concurrency stamps for the update operation and it was matching as required , so why this specific error message. It didn't reveal much of details , and I was stuck for quite some time.
What saved me was changing the way I investigate this kind of issues , so instead of jumping around the LINQ expressions code and the different steps of logic to see what's causing this issue ( which obviously was not working) , I opened SQL profiler to see what are the queries generated by EF Core , and I looked into each of them , they were looking perfect.
I pasted those queries in SQL server and tried to run them , and here the issue appears , which apparently was related to a foreign key constraint that was not valid. I was inserting/Updating a player row into the the players table , in the data I am sending there was a field named TeamId , which was a foreign key referencing the teams table. That foreign key was not valid as it didn't exist in the teams table. But could you have guessed that from the exception message , for me it was almost impossible to spot.

The solution

This way to investigating EF Core issues is the best when the issue happens at the EF call of saving data to DB, and the steps are as mentioned below :

• Open SQL profiler from SQL server Management System , from Tools > SQL server Profiler
• Spot the queries shown up upon hitting the endpoint causing the issue
• Copy these queries and try to execute them on SQL server
• If there is issues with the data you are inserting/Updating , SQL server will be more verbose about it , it will point exactly to the constraint , trigger or index causing the issue.

Of course , in this description SQL server is what I was using when investigating this issue , but surely you could try the same process with the DBMS you are using and its own profiler.

If you don't want to use a profiler at all , you could use Stackify Prefix in its free version , it's an awesome tool that displays all the SQL queries created by EF Core , as well as their timing and much more details.

EF Core ; Two foreign key referencing the same table

Implementing some kind of API design in C# code lately has put me in situation where I needed the same table to be referenced two times in one class. For the sake of this article , we'll set the example of class , having two teachers : principle and substitute:
A class should have at least one principle teacher , 0 or 1 substitute teacher , and a teacher may be assigned to multiple classes

To implement this relationship I opted initially for one to many relationship between class and teachers which I realized soon that it is not the right way to implement this, then I kept experimenting with the configuration of the entities in Fluent API , trying to make them created as required in the design , by just Fluent configuration , but in vain.

The solution

What I ended up doing on the entities implementation is the following:

public class Class
     {

        public Guid Id { get; set; }
        public List<Student> Students { get; set; }
        public Guid principalTeacherId { get; set; }
        public Guid substituteTeacherId { get; set; }

        public Teacher principalTeacher { get; set; }
        public Teacher substituteTeacher { get; set; }
      }

public class Teacher
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string Degree { get; set; }
        public int Age  { get; set; }
        public string SubjectName { get; set; }
        public bool IsPrinciple { get; set; }
        public bool IsSubstitute { get; set; }

        //Classes for which the teacher is a substitute
        public List<Class> SubstituteClasses { get; set; }
        //Classes for which the teacher is a principle
        public List<Class> PrincipleClasses { get; set; }
     }


public class Context : DbContext
{
    ...

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Class>()
                    .HasOne(m => m.PrincipalTeacher)
                    .WithMany(t => t.PrincipleClasses)
                    .HasForeignKey(m => m.principalTeacherId )
                    .WillCascadeOnDelete(false);

        modelBuilder.Entity<Class>()
                    .HasRequired(m => m.substituteTeacher)
                    .WithMany(t => t.SubstituteClasses)
                    .HasForeignKey(m => m.substituteTeacherId )
                    .WillCascadeOnDelete(false);
    }
}

All this makes sense especially the way the Class entity is set , but what I missed at the beginning was the fact I should have two Collection of classes in the Teacher entity . Setting that made relationship

Conclusion

Still many more issues that I had to handle to implement all kinds of designs in EF Core Code first that I will be sharing continuously in this blog, so if you find ths subject interesting , keep an eye on the next articles. I hope you are enjoying this learning journey , keep coding !