none
Linq where clause RRS feed

  • Question

  • Sir,

     

    i want to create a  linq sql from this:

    select p.name from product p where parentid in (select c.parentid from category c where c.categoryid in (select f.categoryid from fathercategory f ) )

     

    Please, help.

    Thank in advance.

    Wednesday, November 10, 2010 9:19 AM

Answers

  • select c1.CategoryName from category c1 where c1.parentID in (select c2.parentID from category c2 where c2.parentID =1)


    ...translates to:

    var q = from c1 in category where (from c2 in category where c2.ParentID == 1 select c2.parentID).Contains(c1.parentID) select c1.CategoryName;

    ...but why the inner query when it filters on parent ID and returns parent id? A simple 'from c1 in category where c1.parentid == 1 select c1.CategoryName' gives the exact same result. (Unless of course one of the 'parentid' references in the inner query is replaced by some other column)

     

    select c1.CategoryName from category c1 where c1.parentID in (select c2.parentID from category c2 where c2.parentID in (select c3.ParentID from category c3 where c3.parentID =1))


    ...translates to:

    var q = from c1 in category where (from c2 in category where (from c3 in category where c3.parentID == 1 select c3.parentID).Contains(c2.ParentID) select c2.parentID).Contains(c1.parentID) select c1.CategoryName;

    ...and again, for this query, you can simplify it to 'from c1 in category where c1.parentid == 1 select c1.CategoryName' since the inner query just returns the same ID that is passed to it in the where clause.


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by liurong luo Wednesday, November 17, 2010 1:56 AM
    • Marked as answer by liurong luo Thursday, November 18, 2010 9:58 AM
    Thursday, November 11, 2010 11:01 AM
    Answerer

All replies

  • Hello,

    There is no "IN" operator in LINQ - you need to use the Any in LINQ

     

    you can use this statement

    from p where db.Categories.Any(c => c.ParentId == p.ParentId && db.FatherCategory.Any(f=>f.CategoryId = c.CategoryId));

     

     


    Ali Hamdar (alihamdar.com - www.ids.com.lb)
    Wednesday, November 10, 2010 9:32 AM
  • Hi,

    Here you have a sample of the fast way to perfom a "NOT IN" operation:

    http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/d0a0e8f4-34c5-4d6f-b428-4d49b7ea3a25

    JAReyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solucion de esta pregunta te ha sido útil.
    Wednesday, November 10, 2010 10:06 AM
  • Thanks a lots .

    But i have other problem here.

    I only have one table category and i have a sql like this:

    select c1.CategoryName from category c1 where c1.parentID in (select c2.parentID from category c2 where c2.parentID =1)

     

    And another one like this:

     

    select c1.CategoryName from category c1 where c1.parentID in (select c2.parentID from category c2 where c2.parentID in (select c3.ParentID from category c3 where c3.parentID =1))

     

    Please, help.

    Very thanks.

     

    Thursday, November 11, 2010 10:41 AM
  • select c1.CategoryName from category c1 where c1.parentID in (select c2.parentID from category c2 where c2.parentID =1)


    ...translates to:

    var q = from c1 in category where (from c2 in category where c2.ParentID == 1 select c2.parentID).Contains(c1.parentID) select c1.CategoryName;

    ...but why the inner query when it filters on parent ID and returns parent id? A simple 'from c1 in category where c1.parentid == 1 select c1.CategoryName' gives the exact same result. (Unless of course one of the 'parentid' references in the inner query is replaced by some other column)

     

    select c1.CategoryName from category c1 where c1.parentID in (select c2.parentID from category c2 where c2.parentID in (select c3.ParentID from category c3 where c3.parentID =1))


    ...translates to:

    var q = from c1 in category where (from c2 in category where (from c3 in category where c3.parentID == 1 select c3.parentID).Contains(c2.ParentID) select c2.parentID).Contains(c1.parentID) select c1.CategoryName;

    ...and again, for this query, you can simplify it to 'from c1 in category where c1.parentid == 1 select c1.CategoryName' since the inner query just returns the same ID that is passed to it in the where clause.


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by liurong luo Wednesday, November 17, 2010 1:56 AM
    • Marked as answer by liurong luo Thursday, November 18, 2010 9:58 AM
    Thursday, November 11, 2010 11:01 AM
    Answerer