0

I have this sql query. I can convert most of the query to linq but I don't understand how to implement the "not in" portion in this particular query. Here is my query:

SELECT d.processid,d.name,t.test_name
FROM dc_tp_tprocess d
left outer join dc_tp_tprocedured pd on pd.processid=d.processid
left outer join dc_tp_test t on t.procedureid=pd.procedureid
where d.active='Y' and t.testid=1 
                   and d.processid not in (1,2,6,8,9,10)
order by t.testid,d.processid
Taryn
  • 242,637
  • 56
  • 362
  • 405
Ehsan Sajjad
  • 61,834
  • 16
  • 105
  • 160
  • possible duplicate of ["NOT IN" clause in LINQ to Entities](http://stackoverflow.com/questions/432954/not-in-clause-in-linq-to-entities) – Heinzi Jun 27 '13 at 05:37
  • my scenario is different you can see in my query – Ehsan Sajjad Jun 27 '13 at 05:40
  • 2
    Unfortunately, I don't see the difference. Maybe it would help if you extended your question to explain which of these approaches you tried and why they did not work for your case. – Heinzi Jun 27 '13 at 05:41
  • 1
    I've edited the question so that it's easy to read the question - please pay attention to the formatting when you ask a question in future; a single very long line is very hard to understand compared with several lines broken at appropriate points. – Jon Skeet Jun 27 '13 at 05:45
  • I agree that this question is different to that one - but it's very similar to many *other* questions on Stack Overflow. – Jon Skeet Jun 27 '13 at 05:47
  • possible duplicate of [convert update query to linq with not in statement](http://stackoverflow.com/questions/12003889/convert-update-query-to-linq-with-not-in-statement) – Jon Skeet Jun 27 '13 at 05:47
  • @JonSkeet: The accepted answer might not apply, but (a) the question is the same and (b) the second answer sounds like it should work. – Heinzi Jun 27 '13 at 05:49
  • @Heinzi: The question doesn't have enough information to be definitively the same or different. Here we have a query - we know that the set of things for the "not in" part is *not* a subquery on the database, it's a list which can be created up-front within C# itself. There are plenty of "not in" duplicates on SO - I just think you picked an unfortunate one. – Jon Skeet Jun 27 '13 at 05:52
  • i try the answers you have given. – Ehsan Sajjad Jun 27 '13 at 05:54
  • It look likes second will solve my problem. – Ehsan Sajjad Jun 27 '13 at 05:57

1 Answers1

1

just like this:

int[] tmp=new int[]{1,2,6,8,9,10};

var query=from d in dc_tp_tprocess
          join pd in dc_tp_tprocedured on d.processid equals pd.processid into leftGroup1
          from pd in leftGroup1.DefaultIfEmpty() 
          join t in dc_tp_test on pd.procedureid equals t.procedureid into leftGroup2
          from t in leftGroup2.DefaultIfEmpty()
          orderby t.testid,d.processid
          where d.active=="Y" && t.testid==1 && !tmp.Contains(d.processid)
          select new {d.processid,d.name,test_name=t==null?"":t.test_name};
Tim.Tang
  • 3,158
  • 1
  • 15
  • 18