CODEWITHSUNDEEP
sqllinqlinq-to-sqlselect
RememberME
RememberME

Reputation: 2092

How to write this query in Linq2Sql

I have a table company which holds the company_id, company_name and other details. I have a table subcontracts which has a company_id column that maps to company.company_id.

How can I write a select statement to get all active companies which have not been assigned to an active subcontract? IE The company_id cannot be found in subcontracts.company_id

******EDIT*****

I believe the correct sql is:

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
              )

Upvotes: 6

Views: 171

Answers (5)

Jonathan Bates
Jonathan Bates

Reputation: 1835

Sounds like you are trying to do a WHERE NOT IN, like maybe:

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

`

Upvotes: 1

Jacob Proffitt
Jacob Proffitt

Reputation: 12768

The sub-select is pretty much the same in 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 will translate this as a "not exists" on the subcontract table.

Upvotes: 2

Nick
Nick

Reputation: 1718

This should work:

var noContracts =
    from c in db.Companies
    join sc in db.Subcontracts.Where(sc => sc.active_status == 1) on c.company_id equals sc.company_id into compGroup
    from cg in compGroup.DefaultIfEmpty() 
    where cg.company_id == null
    select c;

This does a LEFT OUTER JOIN. All subcontracts without a corresponding company_id will be assigned a NULL value for company_id, which it then selects.

Upvotes: 1

Daniel Brückner
Daniel Brückner

Reputation: 59645

I have not tested it and it may well be that LINQ to SQL fails to translate the query, but in theory this should work.

var result = context
   .Subcontracts
   .Select(subcontract => new
      {
         Subcontract = subcontract,
         NotAssignedCompanies = context
            .Companies
            .Where(company => !company.Subcontracts.Contains(subcontract))
      });

This will return all not assigned companies for all subcontracts. If you only need the information for one specific subcontract, the following query will be sufficient.

var notAssignedCompanies = context
   .Companies
   .Where(company => !company.Subcontracts.Contains(specificSubcontract));

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48402

Figure out how you would do this in standard SQL and then pick up a copy of Linqer (http://www.sqltolinq.com/). This product will convert almost any SQL statement into a LINQ query. It's not free, but not expensive either, and comes with a 30 day trial period. I have found it to be extremely useful.

Upvotes: 1

Related Questions