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 :/)?

 

 

 

 

Answer 1

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

Answer 2

 

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