"Where Not In" in LINQ to EF4

Is there a direct transla开发者_运维技巧tion between this SQL Statement to LINQ?

Select ProductId 
From ProductReport
Where ProductId NOT IN(Select ProductId From ClientProduct Where ClientId = CAST('06ae6be1-ca94-44c9-bd30-f1d4f3ac3264' AS uniqueidentifier))

I can achieve the same thing with two LINQ queries, but I'd rather use one query and hit the database once.

---

var q = from p in ProductReport
        where !ClientProduct.Any(c => 
           c.ClientId == new Guid("06ae6be1-ca94-44c9-bd30-f1d4f3ac3264") && 
           p.ProductId == c.ProductId)
        select p.ProductId;

---

Presuming you have navigations in your model -- and you should.

var clientId = new Guid("06ae6be1-ca94-44c9-bd30-f1d4f3ac3264");
var q = from pr in Context.ProductReports
        where !pr.Clients.Any(c => c.ClientId == clientId)
        select pr.ProductId;