How to use sql WHERE With LINQ Query

Introduction

In my current project I am using Linq To Sql ORM as my database layer for doing database operation. Now as I am moving further I got requirement to that I have to filter one table record form another table.

 For example I have to get list of employee which are either ProjectManger or TeamLead.

Sql query to for this is

select * 
from Employee
where deptid in (select deptid from Department)

To do same thing in Linq To Sql you need to use Contains() on one set of record function as you can see below

DataClassesDataContext db = new DataClassesDataContext();
List<int> lstDept = db.Department.Select(x => x.Dept_PKEY).ToList<int>();
 
var qry = from emp in db.Employees
where lstDept.Contains(item.Dept_PKEY)
select emp;

Above example shows how easily you can achieve WHERE IN functionality. Same you can achieve when you are coding using the set of collection object. Enjoy Linq.