0

I’m working with MVC 5 and I’m quite new on it. How can convert the below sql code to lambda expression. Basically what I’m trying to do is to show all records that ProductID not exist in Scrap Table

Select * from Product
 where ProductID not in (Select ProductID from Scrap where ref = '123')
 and active = 1
tereško
  • 58,060
  • 25
  • 98
  • 150
Milli P
  • 17
  • 1
  • 5

3 Answers3

2

I believe your query is equivalent to:

select p.* 
from Product p
join Scrap s on p.ProductID = s.ProductID
where 
    s.ref <> '123'
    and p.active = 1

If so, try this:

from p in db.Products
join s in db.Scraps on p.ProductID equals s.ProductID
where s.Ref != "123" && p.Active == 1
select p
João Simões
  • 1,351
  • 1
  • 10
  • 20
1

Try something like:

var products = (from p in db.Products //db is an instance of my datacontext
                where !db.Scrap.Any(s => s.ProductId == p.ProductId && s.ref == "123")
                && p.active == 1 // p.active == true if active is of type bit in sql
                select p);
kelsier
  • 4,050
  • 5
  • 34
  • 49
1

Try

var query = 
    from p in db.Products
    where !(from s in db.Scrap
            where s.ref == '123'
            select s.ProductId)
           .Contains(p.ProductId)
    && p.Active = 1
Tom
  • 7,640
  • 1
  • 23
  • 47
  • thanks Tom.. just wondering what's the difference of ANY and contains?.. thanks – Milli P Aug 27 '14 at 01:11
  • `Contains` will (may) be slightly faster on a larger object set than `Any` - see [this](http://stackoverflow.com/questions/4445219/linq-ring-any-vs-contains-for-huge-collections) StackOverflow post for more information. – Tom Aug 27 '14 at 12:20