Uncategorized

Using lookup tables (join tables) in Entity Framework

Very rarely, if ever, do we come across a situation in programming where an entire application relies on isolated database tables that do not have share data between each other. So, then, how do you use Entity Framework to help these tables communicate?

Solution: Join Tables

There are several relationship types your Entity Framework Entities can have with each other.

  1. One-to-One
  2. One-to-Many
  3. Many-to-Many

One-to-One can be handled simply by using a foreign key. For simplicity sake, let’s say we have a Dog class and an Adopter class. A dog can only have one adopter and an adopter can only adopt one dog. Here is what the class setup looks like:

Dog Class:

public class Dog  
{ 
    public string Id { get; set; }
    public string Name { get; set; }
    
    public virtual Adopter Adopter {get; set}
}

Adopter Class:

public class Adopter 
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string DogId {get; set;}
    
    public virtual Dog Dog {get; set}
}

When this is compiled by Entity Framework, the Id of the Dog with be placed in the virtual Dog column of the Adopter table and the Id of the Adopter will be placed in the virtual Adopter column of the Dog table. And that’s it. Simple.

For a One-to-Many relationship, let’s say an Adopter can adopt multiple dogs (as it should be), but a dog can still only have one Adopter.

This is what the class setup will look like:

Dog Class:

public class Dog  
{ 
    public string Id { get; set; }
    public string Name { get; set; }
    
    public virtual Adopter Adopter {get; set}
}

Adopter Class:

public class Adopter 
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string DogId {get; set;}

    public virtual ICollection Dogs {get; set;}
}

Here, the Dog Class stays the same since it can still only have one Adopter. However, the Adopter class looks a little different. Instead of a single foreign key, we have a collection of Dogs. In the database this looks like a list of Dog Ids.

Finally, we have the Many-to-Many relationship. In this situation, a Dog can have many Adopters and and Adopter can have many Dogs. Now, following the pattern above, you may think that we would just set up a virtual collection in both the Dog and Adopter classes. This, however, is not the case. Instead, we need what is called a Binding or Join Table.

Join Tables allow Many-to-Many relationships in Entity Framework by matching the necessary data (normally an Id).

Here is what our classes will look like:

Dog Class:

public class Dog  
{ 
    public string Id { get; set; }
    public string Name { get; set; }
    
    public virtual ICollection AdoptionMember {get; set;}
}

Adopter Class:

public class Adopter 
{
    public string Id { get; set; }
    public string Name { get; set; }
    
    public virtual ICollection AdoptionMember {get; set;}
}
public class AdoptionMember 
{
    public string Id { get; set; }
    public string AdoptionDate { get; set; }
    public int AdoptionFee { get; set;}
    public string DogId {get; set;}
    public string AdopterId {get; set;}
    
    public virtual Dog Dog { get; set; }
    public virtual Adopter Adopter { get; set; }
}

Notice that there is a third class, the AdoptionMember. Both the Dog and Adopter classes will have a collection of the AdoptionMember class. The AdoptionMember class has single Foreign Keys for each the Dog and Adoption classes. The AdoptionMember class also has shared data such as the Adoption Date and the Adoption Fee for each individual adoption.

This is a very basic overview of the most common data relationship types you will run into using MVC and how to set up your data when using Entity Framework Code First method.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s