locked
Linq Equivalent of SQL Subquery RRS feed

  • Question

  • Hello all,

    Could some one please give me the linq equivalent of SQL Query :   select  ItemID from clt  where type='1' and ItemID in (select  ItemID from clt  where type='0');

    Thanks in advance

    Rithesh Krishnan


    RK

    Sunday, March 4, 2012 5:57 PM

Answers

  • Hi Rithesh;

    I believe that this query should return what you are looking for.

    var result = from c1 in DataContext.clt
                 where c1.type == "1" &&
                     (from c2 in DataContext.clt
                      where c2.type == "0"
                      select c2.ItemID).Contains(c1.ItemID)
                 select c1.ItemID;
      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, March 4, 2012 7:14 PM
  • Just a small note. Since everything is IQueryable, then notice that nothing will be executed. So you can write it as two lines for readability.

    var dic = from c2 in DataContext.clt
                     
    where c2.type == "0"
                     
    select c2.ItemID

    var result = from c1 in DataContext.clt
                
    where c1.type == "1" && dic.Contains(c1.ItemID)
                
    select c1.ItemID;

    Monday, March 5, 2012 2:11 PM
  • @ Fernando, Thomas

    Thanks a lot for the code snippet. I tried the same query for datatable objects too; by modifying the code as below :

    var innerQuery= from mCT in dt.AsEnumerable()
                                       where  mCT .Field<string>("Type")== "0" select mCT ["ItemID"];
    var result =  from mNU in dt.AsEnumerable()
                                       where  mNU.Field<string>("Type")== "1" && innerQuery.Contains(mNU["ItemID"]) select mNU["ItemID"];

    Regards

    Rithesh Krishnan


    RK

    Monday, March 5, 2012 5:44 PM

All replies

  • Hi Rithesh;

    I believe that this query should return what you are looking for.

    var result = from c1 in DataContext.clt
                 where c1.type == "1" &&
                     (from c2 in DataContext.clt
                      where c2.type == "0"
                      select c2.ItemID).Contains(c1.ItemID)
                 select c1.ItemID;
      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Sunday, March 4, 2012 7:14 PM
  • Just a small note. Since everything is IQueryable, then notice that nothing will be executed. So you can write it as two lines for readability.

    var dic = from c2 in DataContext.clt
                     
    where c2.type == "0"
                     
    select c2.ItemID

    var result = from c1 in DataContext.clt
                
    where c1.type == "1" && dic.Contains(c1.ItemID)
                
    select c1.ItemID;

    Monday, March 5, 2012 2:11 PM
  • @ Fernando, Thomas

    Thanks a lot for the code snippet. I tried the same query for datatable objects too; by modifying the code as below :

    var innerQuery= from mCT in dt.AsEnumerable()
                                       where  mCT .Field<string>("Type")== "0" select mCT ["ItemID"];
    var result =  from mNU in dt.AsEnumerable()
                                       where  mNU.Field<string>("Type")== "1" && innerQuery.Contains(mNU["ItemID"]) select mNU["ItemID"];

    Regards

    Rithesh Krishnan


    RK

    Monday, March 5, 2012 5:44 PM