Self join in LINQ query and return View

Question

I am using LINQ Self Join Query to display data on the view .my sql table contains some employees details .I need to show employee details with their Manager Name as it is ManagerID in the table as

EmpID  Name ManagerID   Designation Phone   Address
1   Mike    3          Developer    123456  Texas
2   David   3           RM          123456  Delhi
3   Roger   NULL        GM          123456  Dallas
4   Marry   2          Developer    123456  NY
5   Joseph  2          Developer    123456  Singapore
7   Ben 2              Developer    123456  Mumbai
8   Steven  3          TL           123456  Banglore
 

i need to change it to name

my code is in controller action

var emp = from m in t.Employees
          join e1 in t.Employees on m.ManagerID equals e1.EmployeeID
          select new { Id = m.EmployeeID , 
                       Name = m.Name, 
                       Manager = e1.Name , 
                       Designation = m.Designation,
                       Phone =m.Phone ,address = m.Address };

return View(emp.Tolist());

and in View

@model IEnumerable <mvc4application.models.employee>

but I am getting runtime error

The model item passed into the dictionary is of type System.Data.Objects.ObjectQuery1[<>f__AnonymousType16[System.Int32,System.String, System.String,System.String,System.Nullable1[System.Int32],System.String]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable1[Mvc4application.Models.Employee]'.] System.Web.Mvc.ViewDataDictionary`1.SetModel(Object value) +405487

Off Course i am understanding this because my view is using Mvc4application.Models.Employee type .

As I am not able to cast it to model type .

can we use SQL view as model in MVC, so that we can do joining in SQL?

Answer 1

You are returning an anonymous object whereas your view is strongly typed to IEnumerable<mvc4application.models.employee>.

I would very strongly recommend you writing a view model that will match the requirements of your view and contain the information you would like to be working with in this view:

public class EmployeeViewModel
{
    public int EmployeeID { get; set; }
    public string Name { get; set; }
    public string ManagerName { get; set; }
    public string Designation { get; set; }
    public string Phone { get; set; }
    public string Address { get; set; }
}

and then adapt your LINQ query in order to project the various domain EF object into the view model:

IEnumerable<EmployeeViewModel> employees = 
    from m in t.Employees
    join e1 in t.Employees on m.ManagerID equals e1.EmployeeID
    select new EmployeeViewModel
    { 
        EmployeeID = m.EmployeeID , 
        Name = m.Name, 
        ManagerName = e1.Name,
        Designation = m.Designation,
        Phone = m.Phone,
        Address = m.Address 
    };

    return View(employees.ToList());

and finally make your view strongly typed to the view model:

@model IList<EmployeeViewModel>

and now you could present the information:

<table>
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Manager name</th>
            <th>Designation</th>
            <th>Phone</th>
            <th>Address</th>
        </tr>
    </thead>
    <tbody>
        @for (var i = 0; i < Model.Count; i++)
        {
            <tr>
                <td>@Html.DisplayFor(x => x[i].EmployeeID)</td>
                <td>@Html.DisplayFor(x => x[i].Name)</td>
                <td>@Html.DisplayFor(x => x[i].ManagerName)</td>
                <td>@Html.DisplayFor(x => x[i].Designation)</td>
                <td>@Html.DisplayFor(x => x[i].Phone)</td>
                <td>@Html.DisplayFor(x => x[i].Address)</td>
            </tr>
        }
    </tbody>
</table>