How to display data from multiple table using join

Experts,

Now i have added following code in TabMasterController

 private CAFMEntities db = new CAFMEntities();

        public ViewResult Index(string sortorder, int? pagesize, int? page)
        {
            int pageSize = pagesize ?? 10;
            if (Request.HttpMethod != "GET")
            {
                page = 1;
                pageSize = 10;
            }
            ViewBag.SelectedPageSize = pageSize;

            ViewBag.CurrentSort = sortorder;
            ViewBag.FirstNameSortParm = String.IsNullOrEmpty(sortorder) ? "FirstName desc" : "";
            ViewBag.LastNameSortParm = sortorder == "LastName" ? "LastName desc" : "LastName";
            ViewBag.DepNameSortParm = sortorder == "depName" ? "depName desc" : "depName";

            var joined = from tm in db.TabMasters
                         join tk in db.TabKeys on tm.colID equals tk.colID
                         join td in db.TabDeps on tk.depID equals td.depID
                         select new { tm.FirstName, tm.LastName, tk.colID, tk.depID, td.depName };
            switch (sortorder)
            {
                case "FirstName":
                    joined = joined.OrderBy(m => m.FirstName);
                    break;
                case "FirstName desc":
                    joined = joined.OrderByDescending(m => m.FirstName);
                    break;
                case "LastName":
                    joined = joined.OrderBy(m => m.LastName);
                    break;
                case "LastName desc":
                    joined = joined.OrderByDescending(m => m.LastName);
                    break;
                case "depName":
                    joined = joined.OrderBy(m => m.depName);
                    break;
                case "depName desc":
                    joined = joined.OrderByDescending(m => m.depName);
                    break;
                default:
                    joined = joined.OrderBy(m => m.FirstName);
                    break;
            }

            int pageIndex = (page ?? 1) - 1;
            int start = (pageIndex * pageSize);
            ViewBag.TotalRecord = joined.Count();
            ViewBag.StartRecord = start + 1;
            ViewBag.EndRecord = ((start + pageSize) >= ViewBag.TotalRecord) ? ViewBag.TotalRecord : (start + pageSize);
            return View(joined.ToPagedList(pageIndex, pageSize));
        }

UPDATED

namespace MVCMultilingual.Models
{
    public class TabMasterModel 
    {
        [ReadOnly(true)]
        public int colID { get; set; }
        [ReadOnly(true)]
        public int depID { get; set; }
        [DisplayName("FirstName")]
        public string FirstName { get; set; }
        [DisplayName("LastName")]
        public string LastName { get; set; }
        [DisplayName("depName")]
        public string depName { get; set; }
    }
}

and

@model PagedList.IPagedList<MVCMultilingual.Models.TabMasterModel>

but i dont know hot to set query output to the class

The following code of Index page of TabMasterController

@model PagedList.IPagedList<MVCMultilingual.TabMaster>
@using Helper
@{
    ViewBag.Title = "Index";
}
<h2>
    Index</h2>
<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table cellpadding="0" cellspacing="0" width="500">
    <tr class="row-head">
        <td>
            @Html.ActionLink("FirstName", "Index", new { sortOrder = ViewBag.FirstNameSortParm, pagesize = ViewBag.SelectedPageSize })
        </td>
        <td>
            @Html.ActionLink("LastName", "Index", new { sortOrder = ViewBag.LastNameSortParm, pagesize = ViewBag.SelectedPageSize })
        </td>
        <td>
            @Html.ActionLink("depName", "Index", new { sortOrder = ViewBag.DepNameSortParm, pagesize = ViewBag.SelectedPageSize })
        </td>
        <td>
        </td>
    </tr>
    @{
        foreach (var row in Model.Select((item, index) => new { index, item }))
        {
        @Html.Raw(GridHelper.BeginRow(row.index));
        <td>
            @Html.DisplayFor(modelItem => row.item.FirstName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => row.item.LastName)
        </td>
         <td>
            @Html.DisplayFor(modelItem => row.item.depName)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id = row.item.colID }) |
            @Html.ActionLink("Details", "Details", new { id = row.item.colID }) |
            @Html.ActionLink("Delete", "Delete", new { id = row.item.colID })
        </td>
        @Html.Raw(GridHelper.EndRow());
        }
    }
    @Html.Raw(GridHelper.BeginFooter(ViewContext.RouteData.Values["controller"].ToString(), 3, Convert.ToInt16(ViewBag.StartRecord), Convert.ToInt16(ViewBag.EndRecord), Convert.ToInt16(ViewBag.TotalRecord), Convert.ToInt16(Model.PageCount), Convert.ToInt16(Model.PageNumber), Convert.ToInt16(ViewBag.SelectedPageSize), ViewBag.CurrentSort, Model.HasPreviousPage, Model.HasNextPage))
    @Html.Raw(GridHelper.EndFooter())
  </table>

in this code i have use following line

@model PagedList.IPagedList<MVCMultilingual.TabMaster>

Now i would like to add TabDep.depName in this page but it will gives me an error

   @Html.DisplayFor(modelItem => row.item.depN开发者_JAVA技巧ame)

Error: 'MVCMultilingual.TabMaster' does not contain a definition for 'depName' and no extension method 'depName' accepting a first argument of type 'MVCMultilingual.TabMaster' could be found (are you missing a using directive or an assembly reference?)

for a start you need to have a check to make sure joined != 0, then you wont be trying to use the data when not created / I.E no results from database. You always have a sortorder so you will always go into the switch. last tabMaster is your model for TabMaster table, what you have created is a new model so you should actually be creating a ViewModel and populating the new View model with the data and then looping your records through the view model, you will find that this fixes the problem

select new MyViewModel{
     fname = tm.FirstName, 
     lname = tm.LastName, 
     ID = tk.colID, 
     DEP = tk.depID, 
     name = td.depName
 }

Once you call this:

select new { tm.FirstName, tm.LastName, tk.colID, tk.depID, td.depName };

Your result is not TabMaster anymore. The result becomes anonymous type and everything expecting TabMaster will fail. So if you want to use a projection and work with it in strongly typed way you must project to a new type - something like TabMasterViewModel`.

Btw. Why do you have junction table mapped? Just remove Id in TabKey and make both ColId and DepId composite primary key and your model will simplify because you will have direct M:N relation between TabMaster and TabDep.