c# - LINQ:当连接表中的计数为零时列出所有

Question

目前我有两张 table

1)“工作”列表

+--------+-------------+
| job_id | job_name    |
+--------+-------------+
| 1234   | Test Job #1 |
| 5678   | Test Job #2 |
| 9101   | Test Job #3 |
| 1475   | Test Job #4 |
+--------+-------------+

2) 与每个工作相关的“发票”列表

| invoice_id    | job_id    | invoice_amount
| 1             | 1234      | 950
| 2             | 1234      | 120
| 3             | 5678      | 560

我需要找到一种方法来选择所有没有“发票”的“工作”——所以最终的预期结果是:

| job_id    | job_name      |
| 9101      | Test Job #3   | 
| 1475      | Test Job #4   | 

我正在尝试这样的事情:

SELECT *
FROM jobs
WHERE NOT EXISTS (
  SELECT * FROM invoices 
  WHERE jobs.job_id = invoices.invoice_id
)

仍然没有运气。这在 LINQ 中如何实现？任何帮助深表感谢!

Answer 1

林克

var results = (from job in jobs
              join invoice in invoices on job.job_id equals invoice.job_id into total
              from record in total.DefaultIfEmpty()
              where record == null
              select job).ToList();

方法语法

GroupJoin 有点棘手，同时也很方便。

此表解释了 GroupJoin 的工作原理。 (注意:观察代码中k和g的用法)

|   Job Records (k)    |Invoice Records (g) |
+----------------------+--------------------+
| 1234   | Test Job #1 |{2 matching records}|
| 5678   | Test Job #2 |{1 matching record} |
| 9101   | Test Job #3 |{0 matching records}|
| 1475   | Test Job #4 |{0 matching records}|  

最终代码

 var result = jobs.GroupJoin(invoices,r=>r.job_id,c=>c.invoice_id,
                                (k,g) => new {k,g})
                                .Where(x=>x.g.Count()==0)
                                .Select(s=> new {s.k.job_id,s.k.job_name}).ToList();