Ordered collection without duplicates in Entity Framework Core-CodePudding

I need to store a list of elements my entities that is ordered. But because it's a many-to-many relationship, not a one-to-many, I can't just add a sorting index to my child entities. As example consider these entities:

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Dish> FavoriteDishes { get; set; }
}

public class Dish
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Person> People { get; set; }
}

As you can see, this is a many-to-many relationship where a person has a list of favorite dishes they like to eat and every dish has a list of people who like that dish.

The problem arises when that list of favorite dishes is to be considered e.g. a ranking, like a top ten list. Now there need to be a way to create a defined but user editable order for these elements. Additionally the lists should not contain duplicates.

With every dish belonging to multiple people I can't just add a index property here. I thought about using something like List<Tuple<int,Dish>> to store every dish with it's index, but I don't know how to set this up as I don't have much experience with Entity Framework Core.

I couldn't find much on this problem kind of problem for many-to-many relationships. All I could find was for one-to-many (like adding the index property to the child entity) or about returning the list ordered by some property (like get => SomeList.Sort(...)).

The only way I can think of right now would be something like storing a list of index/id pairs in an unmapped property and storing that as a string to maintain the ordering when storing/retrieving it from the database. But that does seem very tedious, especially when trying to keep these in sync with the actual list edited by the user. Implementing the PersonDish element for the many-to-many relationship manually instead of using the auto generated one also doesn't seem very simple (again, not much experience with ef). And both methods have to be done for every list property that needs to be ordered, making this not very scalable.

Any tips or recommendations on how to approach this problem?

CodePudding user response：

I think you need to define the third table explicitly and add a Rating property

public class PersonDish
{
    public int PersonId { get; set; }
    public int DishId { get; set; }

    public int? Rating {get; set;}

    public  virtual Person Person { get; set; }
    public virtual Dish Dish { get; set; }
}

public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual List<PersonDish> PersonDishes { get; set; }
    }

public class Dish
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual List<PersonDish> PersonDishes { get; set; }
    }

and dbcontext

            modelBuilder.Entity<PersonDish>( b =>
            {

                b.HasKey( k=> new { k.PersonId, k.DishId });

                b.HasIndex(i=> i.PersonId);
                b.HasIndex(i=> i.DishId);

                b.HasOne( a=> a.Dish)
                     .WithMany(m=> m.PersonDishes)
                    .HasForeignKey(d => d.DishId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .IsRequired();

                b.HasOne(a=>a.Person)
                               .WithMany(m=>m.PersonDishes)
                                .HasForeignKey(k=> k.PersonId)
                                .OnDelete(DeleteBehavior.ClientSetNull)
                                .IsRequired();
            });