c# - LINQ 自引用表 SELECT 查询
问题描述
我有一个自引用员工表,这意味着有一个员工的主键“EmployeeID”和一个外键“SupervisorID”作为父级(主管)。经理位于层级之上,因此他们的 SupervisorID 为空。还有一个日期列 (CreatedOn),用于过滤特定日期,但只有经理有日期,而不是他们的员工。我试图从特定日期、经理和他们的员工中获取所有结果,但我无法让它运行。我需要使用 LINQ 来实现这一点,但我认为我可以先进行 SQL 查询,看看我是否能够以某种方式将其迁移到 LINQ 查询(无论哪种语法)。但这是非常妄想的,一旦我意识到它和 LINQ 一样难......我认为这可以通过 CTE 来实现,但我可以' 根本无法运行自引用。要么我只找经理,要么什么都没有……这是一个示例表:
------------------------------------------------------
|EmployeeID|Name |SupervisorID|CreatedOn |
|1 |Sanders |NULL |2019-01-01 16:20:33|
|2 |Flanders|1 |NULL |
|3 |Andrews |1 |NULL |
|4 |Ranger |NULL |2019-03-20 18:04:56|
|5 |Hammer |4 |NULL |
|6 |Enderson|4 |NULL |
|7 |Larsson |NULL |2019-10-03 04:55:16|
|8 |Simpson |8 |NULL |
------------------------------------------------------
可以看到,Sanders (2019-01-01 16:20:33) 有 2 名员工 (Flanders, Andrews), Ranger 2019-03-20 18:04:56 有 2 名员工 (Hammer, Enderson) 和 Larsson (2019 -10-03 04:55:16) 有 1 名员工 (Simpson)
我对LINQ一无所获。如果我过滤日期,我就没有办法过滤员工的初始数据集了。
// This filters by date, but then I can't get the employees without date anymore
employees.Where(e => e.CreatedOn >= '2019-01-01' && e.CreatedOn < '2019-01-02')
// This filters all employees with a parent, but then I can't get the managers anymore and can't filter against a date anymore either
employees.Where(e => e.SupervisorID != null)
我尝试了加入,但这也不是很好:
from employees m in employees
join e in employees
on m.EmployeeId equals e.SupervisorID
where m.CreatedOn >= '2019-01-01' && m.CreatedOn < '2019-01-02'
select m
适当过滤器的预期结果集将是 Sanders 和他的员工,因为 Sanders 的条目是在 1 月 1 日创建的,并且他的员工在他手下工作:
------------------------------------------------------
|EmployeeID|Name |SupervisorID|CreatedOn |
|1 |Sanders |NULL |2019-01-01 16:20:33|
|2 |Flanders|1 |NULL |
|3 |Andrews |1 |NULL |
------------------------------------------------------
有谁知道,我怎么能做到这一点?我将无法调整数据库设计,因为我只能读取并且另一个应用程序推送数据,而我无法更改。
解决方案
让我们分别看主管和员工,然后将两者合并。
监事
对于主管来说,逻辑相当简单,您可以根据创建日期进行过滤。
var fromDate = new DateTime(2019,1,1);
var untilDate = new DateTime(2019,1,2);
var supervisors = employees
.Where(e => e.CreatedOn >= fromDate
&& e.CreatedOn < untilDate )
.ToList();
注意:在您的数据集中,只有主管有创建日期。如果员工也可以有创建日期,则需要显式过滤掉它们:
var supervisors = employees
.Where(e => e.CreatedOn >= fromDate
&& e.CreatedOn < untilDate
&& e.SupervisorID == null)
.ToList();
雇员
员工的逻辑稍微复杂一些,但您可以依靠 EF 的导航属性为您处理连接。
简而言之,您需要其主管的创建日期在选定范围内的员工。
var fromDate = new DateTime(2019,1,1);
var untilDate = new DateTime(2019,1,2);
var subordinates = employees
.Where(e => e.SupervisorID != null
&& e.Supervisor.CreatedOn >= fromDate
&& e.Supervisor.CreatedOn < untilDate )
.ToList();
合并两者
合并这些相当简单,它有效地做supervisorFilter OR subordinateFilter
:
var fromDate = new DateTime(2019,1,1);
var untilDate = new DateTime(2019,1,2);
var supervisorsAndSubordinates = employees
.Where(e =>
// Supervisor filter
(
e.CreatedOn >= fromDate
&& e.CreatedOn < untilDate
)
||
// Subordinate filter
(
e.SupervisorID != null
&& e.Supervisor.CreatedOn >= fromDate
&& e.Supervisor.CreatedOn < untilDate
))
.ToList();
这将为您提供您期望的结果集。请注意,当此结构是递归的(即多个“主管的主管”级别)时,解决方案会变得更加困难,但目前在您的示例中并非如此。
推荐阅读
- google-api - 使用 Java 在谷歌云存储上使用 *签名 url* 进行 *可恢复上传*
- java - 项目在 Eclipse 中工作,但在打包成 jar 后不能
- nginx - Nginx - 如何让 proxypass 目的地知道请求来自 Nginx?
- ios - FileManager.default.fileExists 表示文档目录不存在
- amazon-web-services - AWS Cloudsearch 是否可扩展?
- c# - 为什么 .NET 不解析带有嵌入 IPv4 值的某些 IPv6?
- python - 如何使用 quandl 函数 get (Status 404) (Quandl Error QECx02) 修复错误?
- php - 如何保持与 cURL 父母和孩子的持久 mysqli 连接
- python - AttributeError:“str”对象没有属性“str”
- r - 使用 seq 和 rep 创建一个由 5 个整数组成的序列,每次重复增加 1