Joining LINQ and returning yourself

Question

I am using LINQ Self Join Query to display data in a .my sql table. The sql table contains some data about employees. I need to show data about employees with their manager name since this 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 get a runtime error

The model element passed to 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 understand this because my mind uses the Mvc4application.Models.Employee type .

Since I can not attribute it to the type of model.

can we use the SQL view as a model in MVC so that we can integrate into SQL?

Answer 1

You return an anonymous object, while your presentation is strongly typed on IEnumerable<mvc4application.models.employee> .

I highly recommend you write a presentation model that will meet the requirements of your presentation and contain the information you would like to work 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 to project the object's 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 for the view model:

 @model IList<EmployeeViewModel> 

and now you can provide 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>