Linq + MVC从作业ID获取作业名称 [英] Linq + MVC getting Job Name from Job ID

问题描述

我有一个包含3个表的SQL数据库

I have a SQL Database with 3 tables

• 员工
• EmployeeDetails
• 工作

我使用Linq将Employee和Employee Details链接在一起，所以现在我有了一个表，该表列出了所有员工以及在EmployeeDetails表中引用的JobID.因此，当我运行我的项目时，它如下所示；

I used Linq to link Employee and Employee Details together so now I have a table which list all of employees and a JobID which is referenced in EmployeeDetails Table. So when I run my project it looks like below;

名称扩展职位标题电子邮件

|马特|分机0000 |56 |[e-mail]

|Matt| Ext 0000 | 56 | [e-mail]

现在我要做的是链接到名为Jobs的第三张表中，以更改ID 56并确实获得工作名称.

Now what I want to do is link into the 3rd table which is called Jobs, to change ID 56 and do actually get the job name.

我已经创建了我的模型和视图模型；

I've created my Model and my View model;

下面是我的控制器

namespace ServiceDirectory.Controllers
{
    public class EmployeeController : Controller
    {

        private ApplicationDbContext db;

        public EmployeeController()
        {
            db = new ApplicationDbContext();
        }

        // GET: Employee
        public ActionResult Index(string searchBy, string search)
        {

            List<EmpVM> Employee = new List<EmpVM>();


            var Emp = (from E1 in db.Employee
                       join E2 in db.EmployeeDetails
                       on E1.EmployeeID equals E2.EmployeeID
                       select new EmpVM
                       {
                           EmployeeID = E1.EmployeeID,
                           Forename = E1.Forename,
                           Surname = E1.Surname,
                           Ext = E1.Ext,
                           Email = E1.Email,
                           Active = E1.Active,
                           JobID = E2.JobID
                       });

            if (searchBy == "Forename")
            {
                
                return View(Emp.Where(a => a.Forename.StartsWith(search) && a.Active == true));
            }
            else
            {
               
                return View(Emp.Where(a => a.Surname.StartsWith(search) && a.Active == true));
            }


        }

        protected override void Dispose(bool disposing)
        {
            db.Dispose();
        }
    }
}

所以我要寻找的是链接到第3个表并将Jobs ID转换为Job name.

So what I'm looking for is to link into 3rd table and get Jobs ID to Job name.

推荐答案

听起来像是需要给 Employee 一个 object 类实例，该实例是作业类型.

Sounds like you need to give your Employee an object class instance that is of the Job type.

public class Employee {
 public string EmployeeID {get;set;}
                           public string Forename {get;set;}
                           public string Surname {get;set;}
                           public string Ext {get;set;}
                           public string Email{get;set;}
                           public bool Active {get;set;}
                           public Job Job {get;set;}
}

然后，您应通过以下方式选择它们:

Then you should select them in the following way:

var Emp = (from E1 in db.Employee
                       join E2 in db.EmployeeDetails
                       on E1.EmployeeID equals E2.EmployeeID
                       select new EmpVM
                       {
                           EmployeeID = E1.EmployeeID,
                           Forename = E1.Forename,
                           Surname = E1.Surname,
                           Ext = E1.Ext,
                           Email = E1.Email,
                           Active = E1.Active,
                           Job= db.Job.First(x => x.JobID == E2.JobID)
                       });

或者，您可以尝试使用Entity Framework进行以下操作:

Alternatively, you can try the following with Entity Framework:

var Employee = db.Employee.Include(x => x.EmployeeDetails).ThenInclude(y => y.Job);

请确保使用System.Data.Entity; 添加，以获取包含lambda的Include版本.

Make sure to add using System.Data.Entity; to get the version of Include that takes in a lambda.

这篇关于Linq + MVC从作业ID获取作业名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！