How to do a LINQ Outer Join of a SubTable?

Question

I have a data tables with this type of setup.

Table A

AID     AName
---------------------
1       A_Name1
2       A_Name2
3       A_Name3

Table B

BID     BName
---------------------
10      B_Name1
20      B_Name2
30      B_Name3

Table C

AID     BID
---------------------
1       10
2       10
2       20
2       30
3       20
3       30

I want to use LINQ to write a query that for a given ID of A give me the list of B that is not in table C.

So for AID of 1 I want BID of 20 & 30.

For AID of 3 I want BID of 10

For AID of 2 nothing returned anything returned.

I know the SQL would be

SELECT
    B.BID
FROM B
LEFT OUTER JOIN C ON C.BID = B.BID AND A.AID = 1 
-- AID would change based on which I was looking for, 1, 2, or 3

What is the LINQ Equivalent?

Please assume I have objects that match the tables of A, B, and C accordingly.

Answer 1

For LinqToSql, database optimizer will handle this appropriately. Use the "Not Any" pattern.

IQueryable<B> query =
  db.BTable
    .Where(b => !db.CTable
      .Where(c=> c.AID == 1)
      .Any(c => c.BID == b.BID)
    )

For LinqToObjects, we don't want to enumerate the CList for each item in BList. Do that enumeration one time.

List<int> BIDList = CList
  .Where(c => c.AID == 1)
  .Select(c => c.BID)
  .ToList();
IEnumerable<B> query = BList
  .Where(b => !BIDList.Contains(b.BID));