Replicate the SQL IN keyword in LINQ to SQL

LINQ To SQL has made the life of .NET developers easier by replicating most of the familiar SQL keywords (SELECT,FROM,WHERE,JOIN..) within the scope of the language.It is easier to construct a LINQ to SQL expression for the developers who are used to in raw SQL syntax against different databases.One of the important SQL keyword is "IN" which is used frequently in the "WHERE" clause of SQL.

For example:

SELECT CustomerID,Name FROM Customers WHERE City IN ('Dhaka','Rajshahi','Chittagong','Khulna')

While constructing this query in LINQ to SQL, the usual solution may come to the mind like this:

C#

string listOfCity = "Dhaka,Rajshahi,Chittagong,Khulna";

MyDataContext context = new MyDataContext ();

var result = from cust in context.Customers
where cust.City in listOfCity
select cust.CustomerID,cust.Name;

return result.ToList();

Actually the above expression does not work and with a little
twist we can make it up and running.The correct code is:

List<string> listOfCity = new List<string>();

listOfCity.Add("Dhaka");
listOfCity.Add("Rajshahi");
listOfCity.Add("Chittagong");
listOfCity.Add("Khulna");

/* We can also use array of string as the following

string[] listOfCity = { "Dhaka","Rajshahi","Chittagong","Khulna" }; */

MyDataContext context = new MyDataContext ();

var result = from cust in context.Customers
where listOfCity.Contains(cust.City)
select cust.CustomerID,cust.Name ;

return result.ToList();

The trick is that we define a generic list of string or an array of string.Then we use the <List>.Contains(<Database column name>) syntax to replicate the SQL "IN" syntax in LINQ To SQL.