Associated with SubQuery SQL with LINQ

Question

select * from Table1 where TC in (select TC from Table2 where Application in ('AAA'))` 

help me in converting the above query to LINQ.

Answer 1

Without the where Application in ('AAA') this looks pretty simple:

 from t1 in db.Table1s where db.Table2s.Select(t2 => t2.TC).Contains(t1.TC) from t1 in db.Table1s 

UPDATE (how wrong I was!)

 List<string> myCollection = new List<string> { "AAA" }; from t1 in db.Table1s where db.Table2s.Where(t2 => myCollection.Contains(t2.Application)).Select(t2 => t2.TC).Contains(t1.TC) from t1 in db.Table1s 

should work with collections in code.

Answer 2

Try this way

In LINQ (so far) there is no subroutine "B".

Use the Any operator to do the same thing.

For instance:

all customers who are in the same city as the employee

  from c in db.Customers where db.Employees.Any(e => e.City == c.City) select c; 

or

The left side of the .Any () operator is a subquery.

 query.Any(x => predicate) 

equivalent to SQL

 EXISTS( SELECT * FROM query WHERE predicate ) 

get more information here

http://social.msdn.microsoft.com/Forums/en-AU/linqprojectgeneral/thread/360166df-4e50-44d8-812a-04b5bc4fedd1