Entity Framework Core 排除结果

Question

使用 Entity Framework Core 和 ASP.NET Core 2.1，如何返回一个表的结果，但前提是在第二个表中找不到该行 ID？

例如，应仅返回 Entity1 表中的前两行，因为第三行具有存储在 Entity2 表中的引用。

Entity1 表

+-----------+-----------+

| Entity1Id | Name      |

+-----------+-----------+

| 1         | Row One   |

| 2         | Row Two   |

| 3         | Row Three |

+-----------+-----------+

Entity2 表

+-----------+-----------+-----------+

| Entity2Id | Name      | Entity1Id |

+-----------+-----------+-----------+

| 1         | Row One   | 3         |

+-----------+-----------+-----------+

Answer 1

你可以做...var result = dbContext.Entity1.Where(w => !w.Entity2.Any()).ToList();这应该返回没有 Entity2 记录的所有 Entity1 行。

Answer 2

最直接的方法是使用可以描述如下的子查询：现在我们可以轻松地将公式转换为以下代码：IQueryable<Entity1> q = _context.Entity1.FromSql(&nbsp; &nbsp; @"select * from Entity1&nbsp;&nbsp; &nbsp; where not exists(&nbsp; &nbsp; &nbsp; &nbsp; select Entity2.Id from Entity2 where Entity2.Entity1Id = Entity1.Id&nbsp; &nbsp; )");另一种方法是左外连接：IQueryable<Entity1> q = from i in _context.Entity1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; join j in _context.Entity2 on i.Id equals j.Entity1Id into jj&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from x in jj.DefaultIfEmpty()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; where jj.All(x=> x.Entity1Id!=i.Id)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; select i ;