Self join in LINQ query and return View

asp.net-mvc, c#

Solution

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>

Problem

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.ObjectQuery`1[<>f__AnonymousType1`6[System.Int32,System.String, System.String,System.String,System.Nullable`1[System.Int32],System.String]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[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?

Original source