Working With Multiple Tables in MVC Using LINQ to SQL

Database structure

Create two tables in the database with the name department and employee.

 

The following is the create table code for the department table:

CREATE TABLE [dbo].[department]
(
   [departmentid] [int] NOT NULL,
   [name] [nvarchar](50) NULL,
)

The following is the create table code for the employee table:

CREATE TABLE [dbo].[employee]
(
   [employeid] [int] NOT NULL,
   [name] [nvarchar](50) NULL,
   [gender] [nvarchar](50) NULL,
   [city] [nvarchar](50) NULL,
   [dpmtid] [int] NULL,
)

Create MVC Application

Step 1

Go to File => New => Project.

 

Step 2

Choose "ASP.NET MVC 4 Web Application" from the list, then provide the application name as "MVCtestlinq" and set the path in the location input where you want to create the application.

 

Step 3

Now choose the Project Template "Empty".

 

Adding a LINQ to SQL Class

Step 1

Right-click on the project and select "Add new item". Then, select Data from the templates.

 

Step 2

Choose "LINQ to SQL classes" from the list and provide a name. Now, after clicking on Add, you can see the .dbml file in the project.

 

Step 3

Drag the department and employee table from the database in the Server Explorer.

|

Create Model Class

The MVC model contains all the application logic validation, business logic and data access logic. We can create a department class under the Model Folder.

sing System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Mvctestlinq.Models
{
    public class depart
    {
        public List<department> depatmentss { set; get; }
        public List<employee> empss { set; get; }
    }
}

Create a department controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Mvctestlinq.Models;

namespace Mvctestlinq.Controllers
{
    public class departmentController : Controller
    {
        //
        // GET: /department/

        public ActionResult Index()
        {
            DataClasses1DataContext dpr = new DataClasses1DataContext();
          //  depart db = new depart();
            List<department> dps= dpr.departments.ToList();
            return View(dps);
        }
    }
}

Create a view to show the department name Name

Right-click on the Index Actionresult method and select Add view. After selecting add view, a dialog box will open. The view name is index by default. Now, select your model class and click on the OK button.

 

Let's see each view with code.

@model  IEnumerable< Mvctestlinq.department>

@{
    ViewBag.Title = " Deaprtment name ";
}

<h2>Index</h2>
@{
    ViewBag.Title = "Department";
}

<h2>Department</h2>

<div>
    <ul>
        @foreach (var item in @Model)
        {
            <li>

                @Html.ActionLink(item.name,"index","emp",new {departmentid=item.departmentid}, null )
            </li>
        }
    </ul>
</div>

The following is the output of the preceding code.

If we will click on any name of department list, the page redirects to the emp controller and calls the index method. The index method shows the total number of employees in the department with their names.

 

Create controller class for emp

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Mvctestlinq.Models;

namespace Mvctestlinq.Controllers
{
    public class empController : Controller
    {
        //
        // GET: /emp/
        DataClasses1DataContext db = new DataClasses1DataContext();
        public ActionResult Index(int departmentid)
        {
            List<employee> employed = db.employees.Where(emps => emps.dpmtid == departmentid).ToList();

            return View(employed);
        }
        public ActionResult details(int id)
        {
            DataClasses1DataContext db = new DataClasses1DataContext();

            empcontext empss = new empcontext();
            empss.empt = db.employees.ToList();
            var value = empss.empt.Single(d => d.employeid == id);
            return View(value);
        }
    }
}

Create a view to show the Employe list

Right-click on the Index Actionresult method and select Add view. After selecting add view, a dialog box will open. The view name is index by default. Now, select your model class and click on the OK button.

 

Let's see each view with code.

@model IEnumerable< Mvctestlinq.employee>

@{
    ViewBag.Title = "Employe list";
}

<h2>Employe List</h2>

<div>

<ul>
        @foreach (var employe in @Model)
        {

            <li>  @Html.ActionLink(employe.name, "details", new {id= employe.employeid  })</li>
         }
</ul>
</div>

The following is the output of the preceding code.

If we will click any name from the employee list, the page will redirect the emp controller and call the details method. The details method shows the details of employee:

@model  Mvctestlinq.employee

@{
    ViewBag.Title = "details";
}

<h2>details</h2>

<div>
    <table>
        <tr>
            <td>
                <b>employe id</b>
            </td>
            <td>
                @Model.employeid
            </td>
        </tr>
        <tr>
            <td>
                <b>Name</b>
            </td>
            <td>
                @Model.name
            </td>
        </tr>
        <tr>
            <td>
                <b>Gender</b>
            </td>
            <td>
                @Model.gender
            </td>
        </tr>
        <tr>
            <td>
                <b>city</b>
            </td>
            <td>
                @Model.city
            </td>
        </tr>

    </table>

    @Html.ActionLink("back to list", "Index", new { departmentid = @Model.dpmtid })
</div>

The following is the output of the preceding code.



Summary 

In this article, we learned how to use multiple tables in MVC using LINQ to SQL.