如何使用实体框架的连接查询

问题描述

在sql，EmployeeCategory和EmployeeStatus中有两个表，我想要一个从员工类别表员工类别第一次检查的连接查询，然后从employeeStatus表中查看员工状态，在employeestatus表中有多个记录针对同一个员工，我想要得到最后一个。

there are two table in the sql,EmployeeCategory And EmployeeStatus, i want a join query that 1st check from employee category table employee category then from employeeStatus table the employee status, in employeestatus table there are multiple records against same employee ,i want to get last one.

EmployeeStatus Table
id    Emp_Id    Status_Id  Shift_Id   sector_Id
1      22         1           1            1
2      22         3           0            1
3      22         1           2            1
4      22         1           1            1
EmployeeCategory Table
Id     Emp_Id     EmployeeCategory       sector_Id
1       22            A                      1

i我正在使用以下查询，但它不会返回任何员工

i am using following query but it does not return any employee

var gtemp = (from es in entities.EmployeeStatus
                                           join ec in entities.EmployeeCategories on es.Employee_Id equals ec.Employee_Id
                                           where es.Status_Id == 7 && es.EmployeeShift_Id == 1 select new
                               {
                                 Sector = es.sector
                           });

推荐答案

var qry = from ec in entities.EmployeeCategories.Where(x=>x.EmployeeCategory=="A")
			join es in entities.EmployeeStatus on ec.Emp_Id equals es.Emp_Id
			group es by new{Cat=ec.EmployeeCategory, EmpId= es.Emp_Id} into grp
			select new
			{
				Cat = grp.Key.Cat,
				EmpId = grp.Key.EmpId,
				Es_Id = grp.Max(x=>x.id)
			};

结果：

Result:

Cat EmpId Es_Id
A   22    4

这篇关于如何使用实体框架的连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！