locked
Get only values that aren't on other table

    Question

  • Consider the following tables:

    ORDER_DETAIL
    ID
    PRODUCT_ID
    VALUE

    PRODUCT
    ID
    NAME

    Now if I want to get all products that do not exist in ORDER_DETAIL table, I would (on SQL) do something like:

    select * from product where id not in (select product_id from order_detail)

    or  maybe

    select *
    from product p
    left join order_detail d on d.product_id = p.id
    where d.id is null

    Using LINQ to Entities, the best I could get until now is this:

    var q = (from p in product
    join d in order_detail on p.id equals d.product_id into od
    select new {p,od}).Where(e=>e.od.Count() == 0);

    The above do the trick, but it is the right/best way (I know it isn’t  pretty :/)?

     

     

     

     

    Friday, August 22, 2008 7:17 PM

Answers

  • Here's an easier way to write this query in LINQ:

    var q = db.product.Where (p => !db.order_detail.Any (od => od.product_id == p.id));

    And if you've got association properties set up (which you should), then the query is even simpler:

    var q = db.products.Where (p => !p.order_detail.Any());

    Joe
    Monday, August 25, 2008 2:31 AM

All replies

  • Here's an easier way to write this query in LINQ:

    var q = db.product.Where (p => !db.order_detail.Any (od => od.product_id == p.id));

    And if you've got association properties set up (which you should), then the query is even simpler:

    var q = db.products.Where (p => !p.order_detail.Any());

    Joe
    Monday, August 25, 2008 2:31 AM
  •  

    Thank you Joe, the way you showed it is much better, and worked 100%

    After so many years of SQL thinking, i'm still struggling to think on "linq mode" ... I will sure have a lot more of dumb questions to do, but every little step  make me like more linq Smile

    Monday, August 25, 2008 3:11 AM