Introduction
Hi, today I am going to show you how to join two tables in C#.
First, we want to create an MVC application, give the application a name and then continue. Once our application is created, we now go to the model's folder and create three classes. In this application, I am using Events, Location, and EventsLocationViewModel.
Notice that I am using a code-first approach. I am using System.ComponentModel.DataAnnotation; this allows me to declare a primary key in this table. I've declared EventID as a Key in this example its data type is int I've done it this way so that it can auto increment in the DB when the record is being saved.
I created the Location Table, notice in this table I have used using System.ComponentModel.DataAnnotation and a new Library Using System.ComponentModel.DataAnnotation.Schema. The .Schema allows me to declare a foreign key, in this case, the FK is EventID.
Now we create the ViewModel, this table combines the properties of all the other tables, only the values that you would like to join. Now we scaffold the Events and Locations Table as controllers with Entity Framework. Once we have the CRUD for these two tables we enter some dummy data so that we have data in our tables to display in our joined table.
Above is the Location Table
Above is the events table.
First, we establish a connection between the database by creating an instance of the database. Second, we make an instance of the ViewModel class in a list form.
Here is the “fun” part, for the program to get data from both the tables and just display specific items the tables need to be Joined. Hence, we write a query linking the PK from the Events Table to the FK in the Location Table. Afterward is a free flow, we create a foreach loop and take initialize all the values we want to display from those tables and equate it to the items selected from the DB, we add it to the list and return the list.
Here is what your final products will look like.
I hope this was helpful. Thank you!