LINQ:当连接表中的count等于0时，列出所有

本文关键字：0时 等于 count 连接 LINQ | 更新日期: 2023-09-27 18:14:24

目前我有两个表

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中实现这一点?任何帮助都非常感激!

LINQ:当连接表中的count等于0时，列出所有

Linq 

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

可以使用内部select:

SELECT * FROM jobs
WHERE job_id NOT IN (SELECT job_id FROM invoices);
相关文章：