具有"not in"的实体框架查询

Question

我在相关表上有一个简单的（很容易，不简单）的"not in"查询。

SELECT CompetencyID, CompetencyName FROM Competency
WHERE (Deleted = 0) AND (CompanyID = 1) AND (CompetencyID NOT IN(SELECT CompetencyID
FROM CompetencyGroups WHERE (Deleted = 0) AND (CompanyID = 1) AND (GroupID = 1))) AND
(ParentID = 0) ORDER BY CompetencyName

在SQL中，我得到了我需要的列表，其余项目不在组中。 现在，我想使用 EF5 将其绑定到数据网格。

我无法正确获取查询语法（使用 VB.net）来列出 ID 和能力名称...

将提供的 c# 答案转换为 VB：

Dim excludeList = context.CompetencyGroups.Where(Function(x) x.Deleted = False And x.GroupID = GroupID).Select(Function(x) x.CompetencyID).ToArray
Dim results = context.Competencies.Where(Function(c) Not excludeList.Contains(c.CompetencyID) And c.Deleted = False And c.CompanyID = 1 And c.ParentID = 0).OrderBy(Function(c) c.CompetencyName)
GridView2.DataSource = results
GridView2.DataBind()

希望这对将来的某人有所帮助。 我花了大约 4 个小时来搜索、询问和转换......

Answer 1

类似

var excludeList = context.CompetencyGroups.Where(x => x....).Select(x => x.CompetencyID).ToArray();
var results = context.Competency.Where(x => !excludeList.Contains(x.CompetencyID));

更新：其他人编辑了这个，然后有人拒绝了它，但编辑很好（选择值）

Answer 2

如果你需要 Cast 来制作一个整数数组。

Dim excludeList = context.CompetencyGroups.Cast(Of CompentencyGroup).Select(Function(x) x.CompetencyID).ToArray()
Dim results = context.Competency.Where(Function(x) Not excludeList.Contains(x.CompetencyID))