C# ASP.NET MVC Making a join table with payload properties

MVC is great, but how do you make join tables with payload? A quick read HERE would suggest that it's a hack to make a join table with payload. For those who don't want to open the link:

When Entity Framework creates a data model, it does not represent the PJT [pure join table] table directly in the data model. Instead, Entity Framework creates a direct-navigation relationship between the related tables, known as a many-to-many association. If the join table contains fields that are not keys, the table is not a PJT and therefore Entity Framework cannot create a direct-navigation (many-to-many) association between the tables.

What this all means is that, by default, when you make a many to many relationship between two data models, the join table doesn't exist in the database. Instead of a database table, a direct-navigation relationship is made. This relationship represents a PJT (Pure Join Table) in our application. A PJT is a join table whose data columns in the table are the primary keys from each data model. The PJT that EF makes is not made as a table, so we have no way to view or alter it. On top of that, how do we add additional fields (payload) to our table?

EF is making all these changes behind the scenes, so to get our application to do what we want we need to do it ourselves.

Consider the following models:

public class User
{
    int Id { get; set; }
    string Name { get; set; }
}

public class Car
{
    int Id { get; set; }
    string Brand { get; set; }
}

In this example, I would like to make a non-PJT for my two data models: User and Car, that is, I want EF to create a custom join table instead of the default PJT.

*It is important to understand why we want this custom table. In this scenario, I have data that is exclusive to the relationship between a User and a Car, such as "PreviousOwnerName", which makes sense to exist in the join table rather than the User or Car data models.

// Default EF join table
    UserID (PK)
    CarID (PK)

// The table we want to have
    UserID (PK)
    CarID (PK)
    ResaleValue int
    PreviousOwnerName string
    // other fields here...

Setting up our models

The way we are going to make a join table is make a model to represent our join table, and add a reference to it from our other models:

public class User
{
    int Id { get; set; }
    string Name { get; set; }

    public virtual ICollection <UserHasCar> UserHasCars { get; set; }
}

public class Car
{
    int Id { get; set; }
    string Brand { get; set; }

    public virtual ICollection <UserHasCar> UserHasCars { get; set; }
}

public class UserHasCar
{
     // Foreign keys
     [Key, Column(Order = 0)]
     public int UserId { get; set; } // Always follow the convention and name this field ModelId; append an "Id" to the end of your model name

     [Key, Column(Order = 1)]
     public int CarId { get; set; } // Same goes for here

     // Navigation properties
     [Required]
     public virtual User User { get; set; } // The name and type should be identical as seen here

     [Required]
     public virtual Car Car { get; set; }

     // Additional properties
     public int ResaleValue { get; set; }
     public string PreviousOwnerName { get; set; }
     // other fields we want to have
}

If you noticed, I added virtual to the properties on my two models:

#Example of using virtual

public virtual ICollection <UserHasCar> UserHasCars { get; set; }

In a nutshell, what virtual does is it allows EF to override the methods to access this data member when it is needed. This is called lazy-loading. Once a database query asks for a UserHasCar record, that is when the database will find a UserHasCar record, as opposed to at the start of your application, which is eager-loading. A resource can be found HERE. If you don't know which to pick, stick with adding the virtual keyword in and do your research later on which method of loading data you would prefer.

Database context setup

All of these changes would be worth nothing if we didn't apply them to our database. Go into your DbContext class and add the code:

public class MyDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Car> Cars { get; set; }

    public DbSet<UserHasCar> UserHasCars { get; set; }

    public MyDbContext()
        :base("ConnectionStringNameGoesHere")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().HasMany(m => m.UserHasCars)
            .WithRequired(m => m.User)
            .HasForeignKey(m => m.UserId);
        modelBuilder.Entity<Car>().HasMany(m => m.UserHasCars)
            .WithRequired(m => m.Car)
            .HasForeignKey(m => m.CarId);
    }
}

Our DbContext class should contain DbSets of each data model we have, including the data model representing our join table. Then, overriding OnModelCreating will be the final addition we need to add to create our join table in our database. Within this model we are explicitly defining the many to many relationship between our data models. The code within OnModelCreating generates for us a table in our database and is the last step needed to create the join table. 

Now, re-create your database to apply the changes. 

Adding and Removing the object in the database

Now that the database is properly setup, we are able to add and remove instances of UserHasCar to and from our database. Adding or removing an instance of UserHasCar is the same process if you would add or remove any other entity from the database. To insert a UserHasCar entity into the database, you must first properly populate your object. In this example, I choose to make a constructor for my UserHasCar object so I can populate the object. You may choose to follow this method or use setters, or some other means, to populate your object:

# In UserHasCar.cs

public UserHasCar(int userId, int carId, ApplicationUser user, Car car, int resaleValue, string previousOwnerName)  
{
    this.UserId = userId;
    this.CarId = carId;
    this.User = user;
    this.Car = car;
    this.ResaleValue = resaleValue;
    this.PreviousOwnerName = previousOwnerName;
}

Now, inserting a UserHasCar entity into the database is as easily as:

ApplicationDbContext db = new ApplicationDbContext();

db.UserHasCars.Add( new UserHasCar{
    ApplicationUserId = [USER ID],
    ApplicationUser = [USER],
    CarId = [CAR ID],
    Car = [CAR],
    ResaleValue = [RESALE VALUE],
    PreviousOwnerName = [PREVIOUS OWNER]
});

db.SaveChanges(); // This will save the entity in the database

*Please note that if you are inserting this data such as "Car" and "ApplicationUser," you must query the database for the proper object. I have left this detail out so you may use your own code to generate the proper Car, ApplicationUser, ApplicationUserId (etc.) to populate these fields.



Removing a UserHasCar entity looks something like this:

ApplicationDbContext db = new ApplicationDbContext();

string userId = [USER ID];
int carId = [CAR ID];

List <UserHasCar> list = (from rec in db.UserHasCars
                          join car in db.Cars on rec.CarId equals car.Id
                          where rec.ApplicationUserId == userId && rec.CarId == carId
                          select rec).ToList();

foreach (UserHasCar car in list)
{
    db.UserHasCars.Remove(car);
}

db.SaveChanges();

Properly removing entities such as UserHasCar require you to join the joint entity (UserHasCar) with the entities it represents. In this example, I join against the Cars table, on the Id property of a Car. I also set where my user id and car id equal the two variables above. Finally, I iterate through the list of possible UserHasCar objects I may have found and remove them all. You do not have to remove all instances like I have in the example, but I show this to show you it is possible to do. 

Comments

If there is any typo, or something doesn't work as described, please comment and I will fix the post.