EF Core 2 Stopping Circular Dependency on Many to Many Relationship

Question

I am using the Sakila Sample Database from MySql on a MySql server. The Diagram looks as follows.

The important tables are the store, inventory and film tables. The is a many-to-many relationship between the tables and the linker table is the inventory table.

I scaffolded this Database in a new dotnetcore project using EFCore 2. I am trying to get a list of stores and their list of films.

The Entities are defined as follows:

Store

public class Store
{
    public Store()
    {
        Customer = new HashSet<Customer>();
        Inventory = new HashSet<Inventory>();
        Staff = new HashSet<Staff>();
    }

    public byte StoreId { get; set; }
    public byte ManagerStaffId { get; set; }
    public short AddressId { get; set; }
    public DateTimeOffset LastUpdate { get; set; }

    public Address Address { get; set; }
    public Staff ManagerStaff { get; set; }
    public ICollection<Customer> Customer { get; set; }
    public ICollection<Inventory> Inventory { get; set; }
    public ICollection<Staff> Staff { get; set; }
}

Inventory

public partial class Inventory
    {
        public Inventory()
        {
            Rental = new HashSet<Rental>();
        }

        public int InventoryId { get; set; }
        public short FilmId { get; set; }
        public byte StoreId { get; set; }
        public DateTimeOffset LastUpdate { get; set; }

        public Film Film { get; set; }
        public Store Store { get; set; }
        public ICollection<Rental> Rental { get; set; }
    }

Film

public partial class Film
    {
 public Film()
 {
     FilmActor = new HashSet<FilmActor>();
     FilmCategory = new HashSet<FilmCategory>();
     Inventory = new HashSet<Inventory>();
 }

 public short FilmId { get; set; }
 public string Title { get; set; }
 public string Description { get; set; }
 public short? ReleaseYear { get; set; }
 public byte LanguageId { get; set; }
 public byte? OriginalLanguageId { get; set; }
 public byte RentalDuration { get; set; }
 public decimal RentalRate { get; set; }
 public short? Length { get; set; }
 public decimal ReplacementCost { get; set; }
 public string Rating { get; set; }
 public string SpecialFeatures { get; set; }
 public DateTimeOffset LastUpdate { get; set; }

 public Language Language { get; set; 
 public Language OriginalLanguage { get; set; }
 public ICollection<FilmActor> FilmActor { get; set; }
 public ICollection<FilmCategory> FilmCategory { get; set; }
 public ICollection<Inventory> Inventory { get; set; }

}

My context looks as follows:

  modelBuilder.Entity<Inventory>(entity =>
  {
         entity.ToTable("inventory", "sakila");

         entity.HasIndex(e => e.FilmId)
             .HasName("idx_fk_film_id");

         entity.HasIndex(e => new { e.StoreId, e.FilmId })
             .HasName("idx_store_id_film_id");

And lastly the repo looks as follows:

public IEnumerable<Store> GetStores()
{
    return _context.Store.
        Include(a => a.Inventory).
        ToList();
}

Problem: When I call this method from a Controller to get the list of stores I don´t get any json response on Postman. Yet if I debug into the list that is returned from the Controller I find the list of stores. The problem is that the list contains: store->inventory->film->store->inventory->film->store... Etc. Creating a circular dependency that fills up the allowed Process memory of the request.

Possible Solutions: I think it has to do with the fact that on the Context both the Foreign Keys are defined as HasIndex instead of HasKey

entity.HasIndex(e => new { e.StoreId, e.FilmId })
                 .HasName("idx_store_id_film_id");

When I define it as HasKey then I get an Error:

'The relationship from 'Rental.Inventory' to 'Inventory.Rental' with foreign key properties {'InventoryId' : int} cannot target the primary key {'StoreId' : byte, 'FilmId' : short} because it is not compatible. Configure a principal key or a set of compatible foreign key properties for this relationship.'

Answer 1

To answer @hamzas comment, I did find a solution to this problem. I used EFCore to build the entities and the DBContext through scaffolding (DB First). As a best practice you should be using Models (Dtos) to represent the Data for the client. EFCore is very helpful in giving us the flexibility to access this M to N relationship however we want. This gives us the flexibility to represent this Data to the client however we want.
Whatever your use case might be. You have to convert the M to N relationship into an 1 to N model.
Use Case #1: You want to show all the movies for a specific store.
Solution
Step #1: You create a StoreDto (Model)

public class StoreDto
{
    int StoreId { get; set; }
    ICollection<FilmDto> Films { get; set; }
       = new List<FilmDto> ();
}

Step #2: Create a FilmDto

public class FilmDto
{
    int FilmId { get; set; }
    int StoreId { get; set; }
    string FilmName { get; set; }
}

Step #3: You provide a Mapping with auto mapper

public class MappingProfiles : Profile 
{
    public MappingProfiles()
    {
        CreateMap<Store, StoreDto>();
        CreateMap<Film, FilmDto>();
    }
}

Step #4: Query the data correctly, Unfortunately I don´t have this example anymore to test this code, so here is where you´ll have to experiment a bit

public Store GetFilmsForStore(byte StoreId)
{
    
    return _context.Store.
        Include(a => a.Inventory).
        ThenInclude(i => i.Film)
        ToList();
}

On the "Include" part you want to only get the Inventory entries where StoreId == Inverntory.StoreId and then Include the Films Object from the resulting list. I hope you get the jist of it. You want to break up your m to n relationships and make them seem like 1 to m for your clients.