Working With Multiple Tables in MVC Using Entity Framework

Introduction

In every MVC project, the controller plays a very important role. It handles all the user requests. If required, it wakes up the model, tells the model to do its job and when the job is done, the controller passes the data to the view and the view then renders the data and displays it to the front-end user as a respons in HTML format. This article shows something like that. Today we will see how to retrieve the records from multiple tables. For the demo we will be using these two tables.

In the tables above, we have StudentId as a primary key column for tblStudent and CourseId as a primary key column for tblCourse. There is also a foreign key relationship between these two tables. Course_Id in the tblStudent is the foreign key column pointing to the CourseId column of the tblCourse table. So, we have our table. Now let's switch to Visual Studio and create a new project.

Step 1. Click File -> New -> Project.

Step 2. Expand the Installed templates then expand Visual C# then expand Web then select Visual Studio 2012 then select ASP.NET MVC 4 Web Application as a new project. The provide a meaningful name then click OK.

Step 3. Select the Empty project template then select the Razor View engine then click OK.

Step 4. A project with an empty template will be created with these directories and files in the Solution Explorer.

Step 5. As we know, we will be retrieving the records from the database tables using Entity Framework and to use Entity Framework, first we need to install it. Select Tools -> NuGet Package Manager -> Manage NuGet packages for solution.

Step 6. A manage NuGet packages window will pop up. Expand the online packages then select NuGet.org. Then look for EntityFramework then click Install.

Select the project. Click OK. The License acceptance window will pop up. Click I Accept in the bottom right.

Wait a few seconds while it's installed.

In the Manage NuGet packages, it will give us a green-marked sign on the top-right section of the EntityFramework which means this package is installed.

Click close. Go to the Solution Explorer and expand references, you will see we have a reference to the EntityFramework.

Step 7. The next step is to add a class file in the Models folder. We will add two class files. One with a class name “Student” in which we will add four auto-implemented properties and all the property names will match the names of the column in the tblStudent table and another class with a name “Course” in which we will add two auto-implemented properties and all the property names will match the names of the column in the tblCourse table. To add a class file in the Models folder, right-click on the Models folder then select add then select class.

After adding both classes and properties it will look like this. Student. cs

Course. cs

Step 8. In the models folder, we will add another class file and we will name that class StudentCourseContext. First, let's add this class and then we will understand the purpose of this class.

The purpose of this class is to establish a connection between our applications and the database and to establish a connection and to retrieve all the records from the table we need to use two classes present in the System.Data.Entity namespace.

• DbContext
• DbSet

The DbContext class will do all the hard work for us. It will establish a connection to the database. The only thing we need to do here is inherit our StudentCourseContext from the DbContenxt class and add a connection string in the root web. config file.

<connectionStrings>
    <add name="StudentCourseContext" connectionString="server=.; database=db_StudentEntity; integrated security=sspi" providerName="System.Data.SqlClient"/>
</connectionStrings>

We have created a connection string with the name “StudentContextCourse” that matches the name of our StundentCourseContext.cs. So, when we create an instance of this class, using DbContext it will look for a connection string with that name within the web. config file and if the match is found, it will use the connection string and establish the connection. DbSet We will add two properties in this class out of which one will return us DbSet<Student> and another will return us the DbSet<Course> object back. Using these two properties we will get all the records present in the database tables.

Step 9. By default, Entity Framework will look for or create a table in a database with the same name as that of the class and here the class names are Student and Course. But in SQL Server the table name is tblStudent and tblCourse. So, in order to map these classes with the database tables we can use the Table attribute in which we will pass the database table name. This Table attribute is present in the System.ComponentModel.DataAnnotations.Schema namespace.

And in the global.asax file, we will add the following code.

Since we are not creating a new database we need to pass null. We have created our Model. Build the solution.

The next step is to add a controller to the Controllers folder and to do that right-click on the Controllers folder then click Add -> Controller.

Step 10. Add a controller.

Provide the Controller the name StudentController.

Click Add.

Step 11. Create an instance of the StudentCourseContext class in the Index action method.

But when we say StudentCourseContext, we don't get any IntelliSense, and if we hardcode the class name we get a compile-time error.

But we have a class with that name in our project. So, why are we getting this error? If you look at the StudentController class, it is present in the StudentEntities.Controllers namespace but if you look at the StudentCourseContext class, it is present in the StudentEntitse.Models namespace.

So, in order to use this class we need to import that namespace first.

In this StudentCourseContext class we have a property Courses and to invoke this property we can say cs. Courses.

Look at the return type of this property. It is returning us the DbSet<Course> but we want to return a list of Courses back and for that, we can convert this course property to a list using the ToList() extension method present in the System. Linq namespace.

After initializing the records in the List object, pass the object to the view.

But you might be wondering why we are able to convert the Courses property to a list since it returns a DbSet<Course> object back. Because this DbSet class implements the IEnumerable interface.

Step 12. The next step is to add a view and for that right-click on the Index method and click Add view.

The View name should be Index. The View engine should be Razor. Check, create a strongly typed view, and select Course class as a model. Select List as the scaffold template then click Add. In the Index.cshtml, you will see much auto-generated code. But don't worry about it. We will discuss it in just a bit but before let's run our application.

When we run the application, we get the preceding error stating “The resource cannot be found”. We have written our code in a simple and best possible way, so what went wrong? If you recap from the previous article of this series, we have discussed MVC is all about the convention and all the route information that is stored in routeconfig.cs states how every MVC should work. This file is present in the App_start directory. To learn more click here.

Above here, look at the default name of the controller. It is Home, but our controller name is Student. So, to override the default convention we must replace Home with Student.

If we run the application we will now get the output as expected which is all the course names.

Look at the preceding output, we have all the course names. But there is a slight problem with our Index view.

1. We don't want Create, Edit, Details and Delete hyperlinks.
2. We want all the course names as hyperlinks that when clicked will redirect to the details of the students that opted for the course that we clicked.

Remove this highlighted code.

Run the application.

Now, let's look at the code that generated the read-only text for us.

@Html.DisplayFor is nothing but a HTML helper that generates controls like TextBox, checkbox, dropdownlist and so on. But let's see which HTML helper generates a hyperlink?

The code that we removed from the Index view that created a Create hyperlink. The ActionLink HTML helper generates a hyperlink for us.

The first parameter is the link text and the second parameter is the action method name to which this Index view will be redirected to. Run the application.

Currently if we click on any of the links, we will get an exception.

It is trying to navigate to the StudentName action method but currently in our Student controller we don't have any action method with that name.

Step 13. The next step is to add a StudentName action method in the StudentController class.

We have created a new object for the StudentCourseContext class and then we invoked the Students property that contains all the student records.

But by just converting the Students property and initializing it into a list object will not give us the student records specific to a course back. It will give us all the student records, no matter what course name link we click. So, to overcome this problem, we will add a parameter in this StudentName action method and we will also add a new object routeValue in the action link we created in Index view.

In Index view

We have created a new RouteValue object Id in which we have passed the courseId. So, when we click any of the links then the courseId with that specific course name will be assigned to the course link.

We will use this same Id object and pass it in as a parameter in the StudentName action method and when we click the link in the Index view then CourseId from the action link will be passed here.

Now the next step is to filter the records based on a condition that will check if the CourseId of the Course and the course id of the student matches and if the match is found, it will display the student records. To filter a record, we can use the Where extension method present in the System.Linq namespace.

Step 14. The next step is to create a StudentName view and for that right-click on the StudentName action method and click Add view.

The View name should be StudentName. The View engine should be Razor. The Model class should be Student. The Scaffold template should be List. Click Add. Remove these codes from the StudentName view.

Run the application.

Click on the C# link.

It gave us the Student records who opted for C#. Click on ASP.Net.

Click on PHP.

Click on Java.

So, in this article we saw how easy it is to work with multiple tables in MVC. I hope you like it. Thank you.