Building a Hotel Management System With ASP.NET Core(#5) - Complex Data Relationships(ManyToMany Relationship Linking)

Repository

https://github.com/dotnet/core

What Will I Learn?

• You will learn how to implement ManyToMany Relationships in Entity Framework 6
• You will learn how to implement ManyToMany Relationships in Entity Framework Core
• You will learn about Fluent API in EF Core
• You will learn about Composite Keys.

Requirements

• Basic knowledge of C# programming language
• Visual Studio 2017/ VS code/ Any suitable code editor
• Previous Tutorial

Difficulty

• Intermediate/Advanced

Tutorial Contents

Previously, we rendered the list of RoomType Entities in our Room Index.cshtml view. There exists a One-Many relationship between the Room and RoomType entities, the RoomType being the Principal(Owner) entity.

Room

  public class Room
    {
        public string RoomTypeID { get; set; }
        public virtual RoomType RoomType { get; set; }
        .
        .
        .
     }

RoomType

 public class RoomType
    {
        public virtual ICollection<Room> Rooms { get; set; }
        .
        .
        .
    }

As can be seen above, we defined a reference navigation property and a collection navigation property in the Room and RoomType entities respectively, such that we were able to access the related properties and all its sub-properties.

However, we haven't considered a more complex scenario involving two entities having a Many to Many relationship. Room and Feature are two entities with such relationship.

Room

 public class Room
    {        
        public virtual ICollection<RoomFeature> Features { get; set; }
        .
        .
        .
     }

Feature

 public class Feature
    {
        public virtual List<RoomFeature> Rooms { get; set; }
    }

Many to Many relationships are the most complex form of relationship between two entities. In the case of One to One & One to Many relationships, a reference to one entity can be stored in the other, in the form of a foreign key. However, in a Many to Many relationship, such reference is not possible.

To relate entities with Many to Many relationship, you require a join table. In the simplest sense, a join table is a table containing the various relationships between the two entities in question. If the join table contains other properties apart from the two entities in question, then it is a Join Table with Payload.

Implementing Many-Many Relationship in Entity Framework (.NET Framework)

In ASP.NET MVC5, join tables are created for you in the database when you specify the Many to Many relationship for the entities. You would then only need to set up the table and column names using Fluent API in the following way:
In your ApplicationDbContext.cs

 protected override void OnModelCreating(ModelBuilder builder)
        {
                modelBuilder.Entity<Room>()
                        .HasMany(c => c.Features).WithMany(i => i.Rooms)
                        .Map(t => t.MapLeftKey("RoomD")
                        .MapRightKey("FeatureID")
                        .ToTable("RoomFeatures"));
          }

• The code above calls an entity generic method which returns the Room entity as specified.
• It then calls the HasMany() method which specifies that this Entity has a many relationship to the Feature entity via the Navigation Property Features -- (ICollection<Feature> Features).
• This returned ManyToMany Navigation Property in turn calls the WithMany method and specifies that Features has a many relationship to Room via Rooms -- (ICollection<Room> Rooms).
• It then calls the Map() method and specifies the Left and Right Column names and the Table name.

Implementing Many-Many Relationship in Entity Framework Core (.NET Core)

Implementing a ManyToMany relationship in EF Core takes a slightly different turn from that seen above. EF Core doesn't support automatic join tables without an intermediate entity to represent the join table.

To represent a ManyToMany relationship between our Room and Feature Entities, we create a third Entity(Join Entity) called RoomFeature which has only properties of the two entities. Then, we link each of these two principal entities to the third via a OneToMany relationship.

Create a new Class in your Models Folder

 public class RoomFeature
    {
        public string RoomID { get; set; }
        public virtual Room Room { get; set; }

        public string FeatureID { get; set; }
        public virtual Feature Feature { get; set; }
    }

• From the code above, we have a foreign key of each of the two related entities and a navigation property each to represent the entity.

Change the Navigation relationship of Room and Feature

Next, we change the relationship between the Room and Feature entities from a direct ManyToMany relationship, to a OneToMany relationship each between itself and the RoomFeature entity.

Room

 public class Room
    {
        public virtual ICollection<RoomFeature> Features { get; set; }
        .
        .
        .
    }

Feature

 public class Feature
    {
        public virtual ICollection<RoomFeature> Rooms { get; set; }
        .
        .
        .
    }

Adding a RoomFeature DbSet

Recall that to access a table in the database, we interact with the database via a DbContext. To access the table of a particular entity, we therefore need a DbSet property with which to associate it to.

Go to your ApplicationDbContext class and add a DbSet of Type RoomFeature

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }
        .
        .
        .
        public DbSet<RoomFeature> RoomFeatureRelationships { get; set; } 
        .
        .
        .
     }

Fluent API

Fluent API is an advanced way of configuring domain classes and mappings to override conventions. It does this by a concept known as Method Chaining. Fluent API is an alternative to Data Annotations. It allows us to write more complex configuration, and it has a higher precedence to Data Annotations. To use Fluent API in EF Core, we override the OnModelCreating() method of our ApplicationDbContext class.

We are going to make use of the Fluent API to configure the Primary Key of the RoomFeature table as well as its relationship with the associated entities.

Override the OnModelCreating() in your ApplicationDbContext as follows

    protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
        
            builder.Entity<RoomFeature>()
                .HasKey(x => new { x.RoomID, x.FeatureID });

            builder.Entity<RoomFeature>()
                .HasOne(rf => rf.Room)
                .WithMany(r => r.Features);

            builder.Entity<RoomFeature>()
                .HasOne(f => f.Feature)
                .WithMany(r => r.Rooms);           
        }

• The first statement(just after base.OnModelCreating(builder);) calls the HasKey()method on an instance of the RoomFeature entity and sets the primary key to a combination of the two foreign keys - RoomID and FeatureID. This is known as a Composite Key. Composite keys are made to prevent duplication on the table.

• The second statement configures a OneToMany relationship between Room and RoomFeature via the Features navigation property-

 public virtual ICollection<RoomFeature> Features { get; set; }

• The third statement, likewise configures a OneToMany relationship between Feature and RoomFeature via the Rooms navigation property

 public virtual ICollection<RoomFeature> Rooms { get; set; }

Updating Our Database

Now that we have made changes to our ApplicationDbContext, we need to effect those changes on our database.

• Add a Migration

Add-Migration RoomFeatureRelationship

• Then run

Update-Database

Now check your database, If everything went well, you should see the new table RoomFeatures and the appropriate Foreign keys for the Joined Entities

Curriculum

• Building a Hotel Management System With ASP.NET Core(#1) - Introduction
• Building a Hotel Management System With ASP.NET Core(#2) - Building the Service Layer
• Building a Hotel Management System With ASP.NET Core(#3) - Building the RoomType Controller Logic
• Building a Hotel Management System With ASP.NET Core(#4) - Building the Room Controller Logic

Proof of Work Done

Github Repo for the tutorial solution:
https://github.com/Johnesan/TheHotelApplication