EF Core Many to Many Join Table with Extra Properties Not Working

Question

I want to have this join table which has 2 foreign keys but also adds some other properties:

[Serializable]
public class GroceryItemGroceryStore
{
    [Key, Column(Order = 0), Required]
    public int GroceryItemId { get; set; }
    [Key, Column(Order = 1), Required]
    public int GroceryStoreId { get; set; }

    public virtual GroceryItem GroceryItem { get; set; }
    public virtual GroceryStore GroceryStore { get; set; }

    [Required]
    public int NotInEstablishmentCount { get; set; }
    [Required]
    public int InEstablishmentCount { get; set; }
    [Required]
    public double Price { get; set; }
}

Which joins these entities:

public class GroceryItem : VeganItem<GroceryItemTag, GroceryStore>
{
    public GroceryItem(string name, string brand, string description, string image)
    {
        this.Name = name;
        this.Brand = brand;
        this.Description = description;
        this.Image = image;
    }

    [Required]
    public string Name { get; set; }
    [Required]
    public string Brand { get; set; }
    [Required]
    public string Description { get; set; }
    public string Image { get; set; }
    public virtual ICollection<GroceryItemGroceryStore> GroceryItemGroceryStores { get; set; }
}

[Serializable]
public abstract class VeganItem<VeganItemTagType, EstablishmentType>
{
    public int Id { get; set; }
    [Required]
    public int IsNotVeganCount { get; set; }
    [Required]
    public int IsVeganCount { get; set; }
    [Required]
    public int RatingsCount { get; set; }
    [Required]
    public int Rating { get; set; }
    [Required]
    public List<VeganItemTagType> Tags { get; set; }
    //[Required]
    public List<EstablishmentType> Establishments { get; set; }
    [Required]
    public int CurrentRevisionId { get; set; }
}

and these entities:

[Serializable]
public class GroceryStore : Establishment<GroceryItem>
{
    public GroceryStore(string name, string placeId, string street, string suburb, string city, string streetNumber)
    {
        this.Name = name;
        this.PlaceId = placeId;
        this.Street = street;
        this.StreetNumber = streetNumber;
        this.City = city;
        this.Suburb = suburb;
    }
}

[Serializable]
public abstract class Establishment<VeganItemType>
{
    public int Id {get; set;}
    [Required]
    public string Name {get; set;}
    [Required]
    public string PlaceId {get; set;}
    [Required]
    public string Street {get; set;}
    public string Suburb {get; set;}
    public string City {get; set;}
    
    public List<VeganItemType> VeganItems {get; set;}
    public string StreetNumber {get; set;}
}

So I try to specify my join table as the table to use when defining my many-to-many relationship in my database context:

builder.Entity<GroceryItem>()
    .HasMany(p => p.Establishments)
    .WithMany(p => p.VeganItems)
    .UsingEntity(j => j.ToTable("GroceryItemGroceryStores"));   

  

Something is not quite right because I get this error when I run dotnet ef migrations add InitialCreate:

Cannot use table 'GroceryItemGroceryStores' for entity type 'GroceryItemGroceryStore' since it is being used for entity type 'GroceryItemGroceryStore (Dictionary<string, object>)' and potentially other entity types, but there is no linking relationship. Add a foreign key to 'GroceryItemGroceryStore' on the primary key properties and pointing to the primary key on another entity typed mapped to 'GroceryItemGroceryStores'.

Where am I going wrong?

EDIT - Problem with inserting data
The tables have all been created successfully. But, GroceryItemGroceryStores table and GroceryStores table do not get any insertions after inserting a GroceryItem which has an establishment on it.

Following is the payload being sent to add a GroceryItem which should also add a GroceryStore and GroceryItemGroceryStore:

{
    "name": "yummy fofod",
    "brand": "goodfoobdgffe",
    "description": "dfsad",
    "establishments": [{
        "name": "Macdonalds",
        "street": "Beach Rd",
        "placeId": "fsadfsdfsdfsdfsadfsadfsdf"
    }],
    "tags": [
        {
            "name": "sdf",
            "id": "9",
            "iconCodePoint": 23145
        }
    ]
}

And this is the controller that handles the above payload request:

[HttpPost]
public async Task<ActionResult<GroceryItem>> PostGroceryItem(GroceryItem groceryItem)
{
    _context.GroceryItems.Add(groceryItem);
    await _context.SaveChangesAsync();

    return CreatedAtAction("GetGroceryItem", new { id = groceryItem.Id }, groceryItem);
}

Answer 1

The presence of the following two properties -

public virtual GroceryItem GroceryItem { get; set; }
public virtual GroceryStore GroceryStore { get; set; }

in the GroceryItemGroceryStore model has already put GroceryItem and GroceryStore in a many-to-many relationship through the joining entity GroceryItemGroceryStore.

Then your relation configuring code is trying to create a new many-to-many relation between them, but trying to use the same joining entity. That's where the problem is, and that's exactly what the error message is saying.

Remove the configuration code, and it should work automatically.

If you want to configure the relationship manually, then change it to -

builder.Entity<GroceryItemGroceryStore>()
    .HasOne(p => p.GroceryItem)
    .WithMany(p => p.GroceryItemGroceryStores)
    .HasForeignKey(p=> p.GroceryItemId);
    
builder.Entity<GroceryItemGroceryStore>()
    .HasOne(p => p.GroceryStore)
    .WithMany()
    .HasForeignKey(p=> p.GroceryStoreId);

Or, even better would be adding the following property -

public virtual ICollection<GroceryItemGroceryStore> GroceryItemGroceryStores { get; set; }

to the GroceryStore model, and changing the config to -

builder.Entity<GroceryItemGroceryStore>()
    .HasOne(p => p.GroceryItem)
    .WithMany(p => p.GroceryItemGroceryStores)
    .HasForeignKey(p=> p.GroceryItemId);
    
builder.Entity<GroceryItemGroceryStore>()
    .HasOne(p => p.GroceryStore)
    .WithMany(p=> p.GroceryItemGroceryStores)
    .HasForeignKey(p=> p.GroceryStoreId);

EDIT:
You should remove the Establishments property from the VeganItem model and the VeganItems property from the Establishment model. Otherwise, they will create a separate many-to-many relationship through a separate joining table.

EDIT - For inserting data
Follow the steps below -

1. Create a new GroceryStore (e.g. newStore) from your payload
2. Insert it to the database
3. Create a new GroceryItem (e.g. newItem) from the payload
4. Do the following -

newItem.GroceryItemGroceryStores = new List<GroceryItemGroceryStore>
{
    new GroceryItemGroceryStore { GroceryStoreId = newStore.Id }
};

_context.GroceryItems.Add(newItem);
await _context.SaveChangesAsync();