Udemy

Sql NOT IN operator in LINQ query Entity Framework

Sunday, September 21, 2014 0 Comments A+ a-

Few days ago, i came across a scenario in which i needed to apply sql NOT IN operator in my project as LINQ Query, my project was in mvc and i used ADO.net Entity Data Model to interact with database.


Here is the sql query which i needed to convert to linq:


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 


At last after posting my question on a forum i got an answer from some one and that was 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}; 


The tmp is a temporary array taken from which i am checking that the processID coming in the current row not exists in that array then select row.

I hope it will help if you stuck or got same scenario and don't getting how to handle the situation -:).
Coursera - Hundreds of Specializations and courses in business, computer science, data science, and more