Best approach for many-to-many relationship in Entity Framework code-first

Question

I've been re-examining the way I'm using entities and I need advice.

I'm creating a yoga booking service and each user will have a profile, spaces to meet up for yoga and events, with a date and time, for each space to meet up.

So my relationships look like this and they are all one-to-many

YogaProfile -> YogaSpace(s) -> YogaSpaceEvent(s)

When an event gets created, members (YogaProfiles) can join anyone's event. Kind of like a class. It's a registering/scheduling system.

Initially I created a table called RegisterdStudents and added the collection to YogaSpaceEvent. Like this

public class YogaSpaceEvent 
{
    // other code here left out
    public virtual ICollection<RegisteredStudent> RegisteredStudents { get; set; }
}

and RegisteredStudent looks like this

public class RegisteredStudent 
{
     [Key]
     public int RegisteredStudentId { get; set; }

     [Index]
     public int YogaSpaceEventId { get; set; }

     [ForeignKey("YogaSpaceEventId")]
     public virtual YogaSpaceEvent YogaSpaceEvent { get; set; }

     [Index]
     public int StudentId { get; set; }
}

This all works fine, but then I learned more about many-to-many and thought I might need to use it here being that many profiles can register for one event and many events can be registered to one profile ex. a class can have many attending and a student can attend many classes.

So I changed the code to make a many-to-many relationship by creating a virtual ICollection on each of the two entities (YogaProfile, YogaSpaceEvent) and creating a join table called RegisteredStudentInEvent like this

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<YogaProfile>()
            .HasMany<YogaSpaceEvent>(x => x.YogaSpaceEvents)
            .WithMany(x => x.RegisteredStudents)
            .Map(x =>
            {
                x.MapLeftKey("YogaProfileId");
                x.MapRightKey("YogaSpaceEventId");
                x.ToTable("RegisteredStudentInEvent");
            });
    }

Now I can successfully add multiple students (YogaProfile) to one class (YogaSpaceEvent) and in the table I see the rows with the event (YogaSpaceEventId) and who's registered (YogaProfileId).

But now, looking at this many-to-many relationship setup, I see I'll NEVER need to add multiple classes (YogaSpaceEvent) to a student (YogaProfile) like below because YogaSpaceEvents gets added to a collection on YogaSpaces, not YogaProfile

yogaProfile.YogaSpaceEvents.Add(yogaSpaceEvent)

So my question is, should I go back to the initial way I was doing it or stay with this many-to-many model?? What's the difference, pro's, cons, etc?

Answer 1

After further looking and discovering I think this might be the best solution.

Keep my original 'RegsiteredStudent' entity but make it look like this

public class RegisteredStudent
{
    public RegisteredStudent() { }

    [Key]
    public int RegisteredStudentId { get; set; }

    [Key]
    [Column(Order = 1)]
    public int YogaProfileId { get; set; }

    [Key]
    [Column(Order = 2)]
    public int YogaSpaceEventId { get; set; }

    [ForeignKey("YogaProfileId")]
    public YogaProfile YogaProfile { get; set; }

    [ForeignKey("YogaSpaceEventId")]
    public virtual YogaSpaceEvent YogaSpaceEvent { get; set; }
}

This way I can add a registered student (YogaProfile) to a new event like this

yogaSpaceEvent.RegsiteredStudents.Add(yogaProfile);

and we should be all good with the references, so that I can look up who's who...

Answer 2

Correct me if I'm wrong, but it seems to me that a Profile represents a Person. An Event represents something like a yoga class during a certain time that the Profile may attend. And a Space is the location where this Event takes place.

There is a one-to-many relation between Space and Event: In one Place may many Events be held. Every event takes place at exactly one Place.

There is many-to-many relation between Profile and Event: every Profile may attend many Events, and every Event may be attended by many Profiles.

For example Event E1 is held at Space S1. Every Profile that decides to attend event E1, he should go to space S1. This should not be recorded at the Profile. If the Event E1 is moved to a different Space, say S2, you only have to change one record: the one that relates the Event and the Space. This one change will make that all Profiles that attend E1 can see that they should go to S2, although nothing in the Profiles is changed.

So I don't understand why you would need a relation between Profiles and Spaces. I'm pretty sure that for a booking service in a well defined database you better not have such a relation between Profiles and Spaces

So:

• Space has many Events, every Event is held at one Space: one-to-many
• Profile attends many Events; Event is attendef by many Profiles: many-to-many
• No direct relation between Profile and Space

This gives you classes like the following.

class Space
{
    public int Id {get; set;}

    // zero or more Events are held at this Space:
    public virtual ICollection<Event> Events {get; set;}

    ... // other properties
}
class Event
{
    public int Id {get; set;}

    // every event takes place at exactly one Space using foreign key
    public int SpaceId {get; set;}
    public virtual Space Space {get; set;}

    // every Event is attended by zero or more Profiles (many-to-many)
    public virtual ICollection<Profile> Profiles {get; set;}       

    public DateTime StartTime {get; set;}
    public TimeSpan Duration {get; set;}
    ... // other properties       
}
class Profile
{
    public int Id {get; set;}

    // every Profile attend zero or more Events (many-to-many)
    public virtual ICollection<Event> Events {get; set;}       

    ... // other properties       
}

Finally the DbContext:

public MyDbContext : DbContext
{
    public DbSet<Event> Events {get; set;}
    public DbSet<Space> Spaces {get; set;}
    public DbSet<Profile> Profiles {get; set;}
}

Because I followed the entity framework code first conventions, this is all that Entity Framework needs to know that you intended the one-to-many and the many-to-many relations. Entity Framework will create the foreign keys and the extra junction tables needed for the many-to-many relationships. No need for Attributes or Fluent API. Only if you want different table names or column names you'll need fluent API or attributes.

There is no safeguard that two different Events are held at the same Space at the same Time. If you want that, you should give every Space zero or more TimeSlots (every TimeSlot belongs to exactly one Space).

To make things easier, I make every time slot one hour. Events that are more than one hour need several TimeSlots

class TimeSlot
{
    public int Id {get; set;}

    public long Hour {get; set;}
    // TODO: create a function from Hour to DateTime

    // every TimeSlot is a TimeSlot of exactly one Space using foreign key
    public int SpaceId {get; set;}
    public virtual Space Space {get; set;}

    // every TimeSlot is used by one Event using foreign key
    public int EventId {get; set;}
    public Event Event {get; set;}
}

Now an Event is held in one or more TimeSlots (which are TimeSlots of a certain Space). Thus a multi-hour event could be held in one Space or several Spaces, as you desire

To create an Event at a certain Space on a certain Time, ask the Space whether it has TimeSlots at the requested hours. If so, the Space is occupied. If not, the space is free.

So If I want to create an Event, for instance an evening course, that takes 3 wednesday evenings between 20:00 and 21:00, starting at may 1st 2018, convert those three times to Hours:

IEnumerable<long> hours = ... // the three times the course will start

// find a space that has not any TimeSlots during these hours
Space freeSpace = myDbContext.Spaces
    .Where(space => !space.TimeSlots
        .Select(timeSlot => timeSlot.Hour)
        .Union(hour)
        .Any())
    .FirstOrDefault();
if (freeSpace != null
{   // this Space has no TimeSlot at any of the three hours,
    // create an time slots in this Space
    IEnumerable<TimeSlot> timeSlots = myDbContext.TimeSlots.AddRange(hours
        .Select(hour => new TimeSlot()
        {
            Hour = hour,
            Space = freeSpace,
        })
        .ToList());

    // create an Event to be held at these TimeSlots (and thus at the FreeSpace
    var event = myDbContext.Add(new Event()
    {
        TimeSlots = timeSlots.ToList();
        Description = ...
        ...
    });

To let the following Profiles attend this event:

    IEnumerable<Profile> profilesWishingToAttend = ...
    foreach (Profile profileWishingToAttend in profilesWishingToAttent)
    {
         profileWishingToAttend.Events.Add(event);
    }

Instead of adding the event to the Profiles, you could, add the Profiles to the event:

    IEnumerable<Profile> profilesWishingToAttend = ...
    foreach (Profile profileWishingToAttend in profilesWishingToAttent)
    {
         event.Profiles.Add(profileWishingToAttend);
    }