CODEWITHSUNDEEP
linqexists
Amit
Amit

Reputation:

LINQ - Where not exists

What is the equivalent of following statement in LINQ:

Select t1.appname, t1.julianDte, t1.cat 
From table1 t1 
Where NOT EXISTS 
   ( Select * 
     from table t2 
     where t1.cat = t2.cat AND t2.julianDte < t1.julianDte )

Upvotes: 47

Views: 38340

Answers (3)

Bryan Watts
Bryan Watts

Reputation: 45445

Query syntax version of @Amy B's answer (with !Any inverted to All):

from t1 in db.Table1
where db.Table2.All(t2 => t1.cat != t2.cat || t2.julianDte >= t1.julianDte)
select new
{
    t1.appname,
    t1.julianDte,
    t1.cat
};

Upvotes: 11

fasr
fasr

Reputation: 111

from t1 in Context.table1DbSet
let ok = 
    (from t2 in Context.table2DbSet 
     where t2.Idt1 = t1.Idt1 && t1.DateValid.HasValue
    ).Any()
where 
   t1.Active
   && !ok

Upvotes: 1

Amy B
Amy B

Reputation: 110071

Try this Not Any pattern.

var query = db.table1
.Where(t1 => !db.table2
  .Any(t2 => t2.cat == t1.cat && t2.julianDte < t1.julianDte)
);

Upvotes: 62

Related Questions