自加入LINQ查询并返回View [英] Self join in LINQ query and return View

问题描述

我正在使用LINQ自联接查询在视图上显示数据.我的sql表包含一些员工详细信息.我需要使用其经理姓名显示员工详细信息 因为它在表中是ManagerID，

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
 

我需要将其更改为名称

我的代码在控制器操作中

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());

和在视图中

@model IEnumerable <mvc4application.models.employee>

但是我遇到运行时错误

传递到字典中的模型项是类型 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

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

当然，我理解这是因为我的观点是使用Mvc4application.Models.Employee type.

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 .

我们可以在MVC中将SQL视图用作模型，以便我们可以在SQL中进行联接吗?

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

推荐答案

您将返回一个匿名对象，而您的视图已被严格键入为IEnumerable<mvc4application.models.employee>.

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; }
}

然后修改您的LINQ查询，以将各种域EF对象投影到视图模型中:

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>

这篇关于自加入LINQ查询并返回View的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！