如何在Linq2Sql中编写此查询

Question

我有一个表公司持有的company_id，company_name和其他细节。我有一个表subcontracts，它有一个映射到company.company_id的company_id列。

如何编写select语句来获取所有未分配到有效委外的有效公司？即在subcontracts.company_id中找不到company_id

*编辑*

我相信正确的sql是：

select company_id 
from company 
where company.active_status = 1 
and not exists( select * from subcontracts 
                where subcontracts.company = company.company_id 
                AND subcontracts.active_status = 1
              )

Answer 1

子选择在LINQ中基本相同。

var noSubs = from company in context.Companies
             where company.active_status == 1 &&
                 !(from subcontract in context.Subcontracts
                  where subcontract.active_status == 1
                  select subcontract.company_id).Contains(company.company_id)
             select company;

Linq to SQL会将其转换为分包表上的"not exists“。

Answer 2

弄清楚如何在标准SQL中做到这一点，然后拿起Linqer (http://www.sqltolinq.com/)的副本。该产品可以将几乎任何SQL语句转换为LINQ查询。它不是免费的，但也不贵，而且有30天的试用期。我发现它非常有用。

Answer 3

听起来像是你在尝试做一个WHERE NOT IN，比如：

var companiesWithoutSubcontracts = 
        from noSub in Companies
        where !(from withSub in Companies
                select withSub.company_id)
               .Contains(noSub.company_id)
        select noSub;

`