Convert sql to LINQ TOP 1 NOT in

Question

How do I convert this SQL to LINQ?

Select Top 1 iD, Grad from tbl1 
WHERE tbl1.ID not in 
(Select GradId from tbl2 WHERE ID = 99469)

possible duplicate: http://stackoverflow.com/questions/296972/sql-to-linq-tool — David Starkey, Mar 26 '13 at 22:29

p.s.w.g · Accepted Answer · 2013-03-26T23:06:43.307

1

Looks like the easiest way would be this:

var grads = 
    (from r in tbl2 
     where r.ID == 99469 
     select r.GradID);
var results = 
    (from r in tbl1
     where !grads.Contains(r.ID)
     select new { r.ID, r.Grad })
    .FirstOrDefault();

Or for a little more brevity:

var grads = ...
var results = tbl1
    .Select(r => new { r.ID, r.Grad })
    .FirstOrDefault(r => !grads.Contains(r.ID));

edited Mar 26 '13 at 23:06

answered Mar 26 '13 at 22:52

p.s.w.g

146,324
30
291
331

This one fits my needs: var sidstegrad = (from d in db.GetTable() where d.Skif == SKIF orderby d.GradID descending select d.GradID).First(); var nextGrad = from d in db.GetTable() where Convert.ToInt32(d.iD) > sidstegrad select new { d.Grad, d.iD }; – Peter Hansen Mar 27 '13 at 08:12

score 1 · Answer 2 · answered Mar 26 '13 at 23:00

1

Something on the lines of:

 var query =
         (from c in db.tbl1
          where !(from d in db.tbl2
          select d.id)
               .Contains(c.id)
          select c).Take(1)

answered Mar 26 '13 at 23:00

devilfish17

337
3
10

Convert sql to LINQ TOP 1 NOT in

2 Answers2