Sql NOT IN operator in LINQ Query Entity Framework
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:
At last after posting my question on a forum i got an answer from some one and that was this:
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 -:).
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 -:).
