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.

Web App Series

Basics of .NET Web Apps: Part 2 – PostgreSQL and Entity Framework

If you’re just jumping into this post, make sure to catch up on the current state of the RescueShare project here.

If you’ve already read up, let’s get started!

So, now we have the bare bones of an API. But, how are we going to save and access data? We need to set up a database.

I chose to use PostgreSQL as my database based on the fact that it is powerful and is being used more frequently to handle large data. You can learn more about PostgreSQL here but the quick snapshot is that PostgreSQL is an open-sourced object-relational database that uses the SQL language. This blog series does not require experience with SQL but if you’re curious, this is a great place to get comfortable with the basics.

So, let’s download PostgreSQL!

Note: I am going to walk through the steps to download Windows x86-64 Version 11.3, but there are detailed instructions on the PostgreSQL website for other operating systems and versions.

Choose the OS and version you want to work with. I will be working with this:
2019-06-10_1831

Click through the prompts.

Make sure you install pgAdmin.

2019-06-10_1834

Okay, so now we have a pretty program and a powerful database. Cool. How do we get them to work together?

In many programs, the SQL language is how your logic will communicate with the data. However, we will be using a tool called Entity Framework to make our code cleaner and our lives a little easier.

What is Entity Framework?

TLDR: An object-relational mapper that allows you to use the what you already know and love (.NET) to communicate between your logic in Visual Studio and your data in PostgreSQL (although EF can be used with several other database providers listed here).

In-depth: Read all about Entity Framework (which I highly recommend because it’s super cool) here.

How do we get Entity Framework?

Remember the NuGet Packages we installed from Part 1? Entity Framework was one of those packages.

Why Entity Framework?

EF lets us use the Code First method which basically means we can build our database using classes rather than SQL queries. This allows us to organize our code in a more Object Oriented style and maintain a sleek and easy to-read-look.

Convinced?

Let’s get started.

First, it’s important to understand the basics of EF. There are several types in EF, most importantly being the DbContext. The DbContext communicates with Models to “write” and execute queries. Any tables you want to have in your database will be defined in the DbConext using this sytanx

        public DbSet Dogs { get; set; }
        public DbSet Shelters { get; set; }
        public DbSet Volunteers { get; set; }

This will look for a corresponding Model. The model is where you will define the columns for your table as properties and it looks a little like this:

namespace RescueShare.Models
{
    public class Dog
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public string Breed { get; set;}
    }
}

You can find the DbContext in the Data folder in the Solution Explorer. The default name will be ApplicationDbContext.

You can add models in the Models folder by right clicking on the folder and clicking Add > Class

Remember, every time you add a new model you want to be a table, you must add it to the DbConext.

We’ll talk more later about what these Model files will contain, but for now, just add the properties you want to see as columns in your table.

Now we know how to create tables using Entity Framework. Great!

But how do we actually add them to our database?

First, let’s take a look at what our database already has.

Log in to pgAdmin if you haven’t already (this was installed with PostgreSQL and you can get to it by a quick search in your downloads folder).

Once there, navigate to localhost -> Login / Group Roles 2019-06-25_2326

Add a name to your user (I used the name of my application) and added a password under the Definition tab.

Now you have a user! This is next part could have been done with the default postgres user, however, it’s best to create your own.

Back in Visual Studio, navigate to your startup.cs file. You should see some lines of code that look similar to this

services.AddDbContext(options =>
                options.UseSqlServer(
                    Configuration.GetConnectionString("DefaultConnection")));
            services.AddDefaultIdentity()
                .AddDefaultUI(UIFramework.Bootstrap4)
                .AddEntityFrameworkStores();

Currently, this is telling your application to use the default database (SQL Server) but we want to tell it to use pgAdmin. So, change the line that says

options.UseSqlServer

to say…

options.UseNpgsql

On the next line of code, you can see it is grabbing something called the “DefaultConnection”. This is found in our appsettings.json file. This also holds a default value we are going to change. Right now, you should see something that looks similar to this

"Server=(localdb)\\mssqllocaldb;Database=aspnet ..........

but we want to update it with our user we just created in pgAdmin.

Your updated version should look something like this (with your user information instead):

User ID=rescueshare;Password="";Host=localhost;Port=5432;Database=rescueshare_local;Pooling=true;

Congrats! You have now configured your application to use PostgreSQL!

Next step, getting your data actually into the database.

In the Data folder where you found the DbContext, you should have also seen a Migrations folder. A Migration is how Entity Framework wraps up all your database changes and converts them into SQL to send to PostgreSQL.

You can read more detail about Migrations in Entity Framework here

Remember the Package Manager Console from the first blog post that allowed you to add NuGet Packages? This is also where you will add migrations and update your database.

Here are the steps to adding a migration:

  1. In the Package Manger Console, run Add-Migrations

You will be prompted to add a name. This should be something short about the changes you made. After adding a name, hit enter.

You should see something like this:

2019-06-14_1301

This will automatically create a class in your Migrations folder where you can see the changes that will be made.

  1. Now that you have a migration, you need to update your database with the data.

Still in the Package Manager Console, run Update-Database.

Woo! You have just successfully moved all your database changes into your database which you can now view through pgAdmin!

That was a lot of heavy information so I highly recommend reading through some of the docs I linked throughout the post to get a deeper understanding for what we did here.

Up next, we’ll get more into the thick of it and start writing some world altering code.