How To do In and Like clause in Linq To SQL

In clause is one of the common querying feature we use in SQL. But there is no clear explanation of how to write a Linq query which allows you to filter the results based on certain items in a list. Well after digging in through some of the query operators I found that there are various operators that you can use to tell a Linq query to filter the items based on items in a list. Let's have a look at few of those examples.

I will start with using the contain operator and show you how Linq to SQL provider converts that into in clause in SQL server.

In the above code I am displaying all the customers in the city of London or Paris. Notice I am using the contains method on the cities array to find the right match. The contains clause gets converted into in clause as shown in the output.

Another possibility of in clause can be a like operator with bunch of or statements. For e.g where city like London or city like Paris. Although it's truly not an in statement but with bunch of or statements you can make it work like an in statement. Here is an example of doing that.

The results are identical except that I am using or and contains statement which Linq to SQL provider converts it into or and like statement as shown in the output window.

Another way in SQL where you can say show me all the customers who has at least one order that has a ship city of Bern is by making use of an exists clause. It is basically an in clause that evaluates to a sub query which selects only those customers who have orders with a ship city of Bern. Let's see how to write that in a Linq query.

In the above example I am using where clause with a lambda expression that evaluates to true if there are any orders for a customer that has a ship city of Bern. As you can see in the output window, it gets evaluated to an exists clause in SQL server.

You can also use StartsWith and EndsWith operator to force Linq to SQL to use like operator in comparing a value in a query. Here is an example of that.

Notice that Linq to SQL provider converted the StartsWith operator with like clause and value is appended with % at the start. Same thing happens with EndsWith except the value is appended with % at the end.

If build in operator does not give you the flexibility of comparing the values, you have the options of using SqlMethods which gives you more control in how you like value should look like. In the query below, I have made use of % before and after the value.